DELETE vs TRUNCATE vs DROP | Dataford Interview Questions

In SQL, difference between DELETE, TRUNCATE, and DROP?

Using the supplied reference data, write a PostgreSQL query that returns one comparison row for each command and summarizes its behavior.

Output

Return operation, can_remove_selected_rows, can_remove_all_rows, removes_table_definition, supports_where, and rollback_behavior.
Return one row each for DELETE, TRUNCATE, and DROP.
Order rows as DELETE, TRUNCATE, then DROP.

sql_command_reference

Column	Type	Description
reference_idPK	INT	Unique reference row identifier
operation	VARCHAR(10)	SQL command being described
can_remove_selected_rows	BOOLEAN	Whether the command can target selected rows
can_remove_all_rows	BOOLEAN	Whether the command removes all table rows
removes_table_definition	BOOLEAN	Whether the command removes the table definition
supports_where	BOOLEAN	Whether the command supports a WHERE clause
rollback_behavior	VARCHAR(30)	Transaction rollback behavior in PostgreSQL
reference_note	VARCHAR(140)	Supporting explanation for the reference row

Your question is DELETE vs TRUNCATE vs DROP. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

In SQL, difference between DELETE, TRUNCATE, and DROP?

Using the supplied reference data, write a PostgreSQL query that returns one comparison row for each command and summarizes its behavior.

Return operation, can_remove_selected_rows, can_remove_all_rows, removes_table_definition, supports_where, and rollback_behavior.
Return one row each for DELETE, TRUNCATE, and DROP.
Order rows as DELETE, TRUNCATE, then DROP.

sql_command_reference

Column	Type	Description
reference_idPK	INT	Unique reference row identifier
operation	VARCHAR(10)	SQL command being described
can_remove_selected_rows	BOOLEAN	Whether the command can target selected rows
can_remove_all_rows	BOOLEAN	Whether the command removes all table rows
removes_table_definition	BOOLEAN	Whether the command removes the table definition
supports_where	BOOLEAN	Whether the command supports a WHERE clause
rollback_behavior	VARCHAR(30)	Transaction rollback behavior in PostgreSQL
reference_note	VARCHAR(140)	Supporting explanation for the reference row

Your question is DELETE vs TRUNCATE vs DROP. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.