sql Interview questions - RamNayakTech/knowledge-hub GitHub Wiki

• DDL Vs DML - DDL always talks about table structures, constraints (Primary, foreign, unique, default, check), indexes (primary key). Whereas DML is about dealing with data withing the table - insert, update, delete
• Delete Vs Truncate - Delete operation supports commit and revoke operations so that you can undo the changes which is not possible with Truncate.

delete from table_name where 1=1;

Above query deletes all records of the table as condition in the where clause evaluates true all the time. If you want to delete specific rows/records, use select statement to see those records with appropriate condition. Same condition can be reused in the delete statement to delete the records.

• Drop - This would delete data and removes the table. This operation is irreversible.
• Constraints

Below default constraint sets value of the table column to 10 if value is not passed as part of insert statement.

column dataype() default 10

For gender column if you want to limit the values to be inserted to 'M' and 'F' for standardization, this comes handy.

check('M', 'F')

You can force unique values in column using Unique constraint.

column_name data_type(size) unique