TRUNCATE vs DELETE vs DROP in SQL - Practice Questions

Challenge - 5 Problems

❓ query_result

intermediate

Difference in row count after DELETE vs TRUNCATE

Consider a table Employees with 1000 rows. What will be the number of rows after running the following SQL commands separately?

1. DELETE FROM Employees;
2. TRUNCATE TABLE Employees;

Choose the correct pair of row counts after each command.

A After DELETE: 1000 rows, After TRUNCATE: 0 rows

B After DELETE: 0 rows, After TRUNCATE: 0 rows

C After DELETE: 0 rows, After TRUNCATE: 1000 rows

D After DELETE: 1000 rows, After TRUNCATE: 1000 rows

💡 Hint

Both DELETE and TRUNCATE remove all rows, but their behavior differs in other ways.

Both DELETE and TRUNCATE remove all rows from the table, so the row count becomes zero after either command.

🧠 Conceptual

intermediate

Transaction behavior of DELETE vs TRUNCATE

Which statement correctly describes the transaction behavior of DELETE and TRUNCATE commands?

A Both DELETE and TRUNCATE can be rolled back

B Neither DELETE nor TRUNCATE can be rolled back

C DELETE can be rolled back, TRUNCATE cannot be rolled back

D DELETE cannot be rolled back, TRUNCATE can be rolled back

💡 Hint

Think about whether these commands are logged in transactions.

Both DELETE and TRUNCATE are transactional in most modern databases and can be rolled back if used inside a transaction.

📝 Syntax

advanced

Syntax error in DROP command

Which of the following DROP commands will cause a syntax error?

A DROP TABLE Employees;

B DROP TABLE IF EXISTS Employees;

C DROP Employees;

D DROP DATABASE CompanyDB;

💡 Hint

Check the correct syntax for dropping tables.

The DROP command requires specifying the object type like TABLE or DATABASE. 'DROP Employees;' is missing the object type and causes a syntax error.

❓ optimization

advanced

Performance difference between DELETE and TRUNCATE

Which statement best explains why TRUNCATE is usually faster than DELETE when removing all rows from a large table?

A Both commands perform the same operations internally, so speed is identical

B TRUNCATE removes rows one by one, DELETE removes all rows at once

C DELETE deallocates data pages without logging, TRUNCATE logs each row deletion

D TRUNCATE deallocates data pages without logging individual row deletions, DELETE logs each row deletion

💡 Hint

Think about how logging affects performance.

TRUNCATE is faster because it deallocates entire data pages and logs minimal information, while DELETE logs each row deletion, making it slower.

🔧 Debug

expert

Effect of DROP on table and data

After running DROP TABLE Employees;, what will happen if you try to query SELECT * FROM Employees;?

A Raises an error because the table no longer exists

B Returns zero rows because table is empty

C Returns all rows from Employees table

D Returns metadata about the Employees table

💡 Hint

Think about what DROP does to a table.

DROP removes the table and its data completely. Querying it afterward causes an error because the table does not exist.