DROP and TRUNCATE behavior in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Difference in row count after DROP vs TRUNCATE

Consider a table employees with 100 rows. What will be the number of rows in the table after executing the following commands?

1. TRUNCATE TABLE employees;
2. DROP TABLE employees;

Choose the correct pair of row counts after each command.

A After TRUNCATE: 0 rows, After DROP: 0 rows

B After TRUNCATE: 100 rows, After DROP: 0 rows

C After TRUNCATE: 0 rows, After DROP: Table does not exist

D After TRUNCATE: Table does not exist, After DROP: 0 rows

💡 Hint

Think about what happens to the table structure after each command.

TRUNCATE removes all rows but keeps the table structure intact, so row count becomes 0. DROP removes the entire table, so it no longer exists.

🧠 Conceptual

intermediate

Transaction behavior of DROP vs TRUNCATE

Which statement correctly describes the transaction behavior of DROP TABLE and TRUNCATE TABLE in MySQL?

A Neither <code>DROP</code> nor <code>TRUNCATE</code> are transactional and cannot be rolled back.

B <code>TRUNCATE</code> is transactional and can be rolled back; <code>DROP</code> is not transactional and cannot be rolled back.

C Both <code>DROP</code> and <code>TRUNCATE</code> are transactional and can be rolled back.

D <code>DROP</code> is transactional and can be rolled back; <code>TRUNCATE</code> is not transactional and cannot be rolled back.

💡 Hint

Consider if these commands can be undone inside a transaction.

In MySQL, both DROP TABLE and TRUNCATE TABLE are DDL statements that cause an implicit commit before and after execution. Neither can be rolled back within a transaction.

📝 Syntax

advanced

Correct syntax to remove all rows but keep table

Which of the following SQL commands correctly removes all rows from a table orders but keeps the table structure intact?

A DROP TABLE orders;

B TRUNCATE TABLE orders;

C DELETE FROM orders;

D REMOVE ALL FROM orders;

💡 Hint

Think about which command clears data but does not delete the table.

TRUNCATE TABLE removes all rows quickly and keeps the table. DROP TABLE deletes the table. DELETE FROM is valid syntax but slower. DELETE TABLE and REMOVE ALL FROM are invalid syntax.

❓ optimization

advanced

Performance difference between DELETE and TRUNCATE

You want to remove all rows from a large table logs. Which command is generally faster and why?

A <code>TRUNCATE TABLE logs;</code> because it deallocates data pages without logging individual row deletions.

B <code>DELETE FROM logs;</code> because it logs each row deletion for rollback.

C <code>DROP TABLE logs;</code> because it removes the table and data instantly.

D <code>DELETE * FROM logs;</code> because it deletes all rows with a wildcard.

💡 Hint

Consider how each command handles logging and data removal.

TRUNCATE is faster because it deallocates data pages and does minimal logging. DELETE logs each row deletion, making it slower. DROP removes the table entirely, not just rows. DELETE * is invalid syntax.

🔧 Debug

expert

Error when truncating a table with foreign key constraints

You try to run TRUNCATE TABLE orders; but get an error about foreign key constraints. What is the cause?

A TRUNCATE requires the table to be empty before running.

B TRUNCATE cannot be used on tables with any indexes.

C The table <code>orders</code> does not exist.

D The table <code>orders</code> has foreign key constraints referencing it, so TRUNCATE is blocked.

💡 Hint

Think about how foreign keys affect data removal commands.

MySQL does not allow TRUNCATE on tables referenced by foreign keys because it bypasses row-level checks. You must drop or disable foreign keys or use DELETE instead.