TRUNCATE vs DELETE vs DROP in SQL - Hands-On Comparison

Understanding TRUNCATE vs DELETE vs DROP in SQL

📖 Scenario: You are managing a small library database. You want to learn how to remove data or tables safely and efficiently.

🎯 Goal: Build SQL commands step-by-step to practice removing data and tables using TRUNCATE, DELETE, and DROP.

📋 What You'll Learn

Create a table called Books with columns BookID (integer) and Title (text).

Insert three specific books into the Books table.

Use DELETE to remove one book by BookID.

Use TRUNCATE to remove all remaining books.

Use DROP to delete the entire Books table.

💡 Why This Matters

🌍 Real World

Database administrators and developers often need to remove data or tables safely and efficiently. Knowing when to use DELETE, TRUNCATE, or DROP helps maintain data integrity and performance.

💼 Career

Understanding these commands is essential for roles like database administrator, backend developer, and data analyst to manage and clean databases properly.

1

Create the Books table and insert data

Write SQL commands to create a table called Books with columns BookID as integer and Title as text. Then insert these three books exactly: (1, 'The Hobbit'), (2, '1984'), and (3, 'Pride and Prejudice').

SQL

-- Create the Books table and insert three books
-- Your code here

Hint

Use CREATE TABLE to make the table, then INSERT INTO to add each book.

2

Delete one book by BookID

Write an SQL command to delete the book with BookID equal to 2 from the Books table using the DELETE statement.

SQL

CREATE TABLE Books (BookID INT, Title TEXT);
INSERT INTO Books (BookID, Title) VALUES (1, 'The Hobbit');
INSERT INTO Books (BookID, Title) VALUES (2, '1984');
INSERT INTO Books (BookID, Title) VALUES (3, 'Pride and Prejudice');
-- Delete the book with BookID 2
-- Your code here

Hint

Use DELETE FROM Books WHERE BookID = 2; to remove the specific book.

3

Remove all remaining books using TRUNCATE

Write an SQL command to remove all remaining rows from the Books table using the TRUNCATE statement.

SQL

CREATE TABLE Books (BookID INT, Title TEXT);
INSERT INTO Books (BookID, Title) VALUES (1, 'The Hobbit');
INSERT INTO Books (BookID, Title) VALUES (2, '1984');
INSERT INTO Books (BookID, Title) VALUES (3, 'Pride and Prejudice');
DELETE FROM Books WHERE BookID = 2;
-- Remove all remaining books using TRUNCATE
-- Your code here

Hint

Use TRUNCATE TABLE Books; to quickly remove all rows.

4

Drop the Books table

Write an SQL command to completely remove the Books table from the database using the DROP statement.

SQL

CREATE TABLE Books (BookID INT, Title TEXT);
INSERT INTO Books (BookID, Title) VALUES (1, 'The Hobbit');
INSERT INTO Books (BookID, Title) VALUES (2, '1984');
INSERT INTO Books (BookID, Title) VALUES (3, 'Pride and Prejudice');
DELETE FROM Books WHERE BookID = 2;
TRUNCATE TABLE Books;
-- Drop the Books table
-- Your code here

Hint

Use DROP TABLE Books; to remove the entire table.

Practice

1. Which SQL command removes all rows from a table quickly but keeps the table structure intact?

easy

A. TRUNCATE

B. DELETE

C. DROP

D. ALTER

Solution

1. Step 1: Understand the effect of TRUNCATE

   TRUNCATE removes all rows quickly without logging each row deletion, but keeps the table structure.

2. Step 2: Compare with other commands

   DELETE removes rows but logs each deletion, DROP deletes the entire table, and ALTER changes table structure.

3. Final Answer:

   TRUNCATE -> Option A

4. Quick Check:

   Fast row removal keeping table = TRUNCATE [OK]

Hint: TRUNCATE clears all rows fast but keeps table [OK]

Common Mistakes:

• Confusing DROP with TRUNCATE
• Thinking DELETE is faster than TRUNCATE
• Assuming ALTER removes data

2. Which of the following is the correct syntax to delete all rows from a table named Employees?

easy

A. TRUNCATE TABLE Employees;

B. DELETE * FROM Employees;

C. DELETE FROM Employees;

D. DROP TABLE Employees;

Solution

1. Step 1: Recall DELETE syntax

   The correct syntax to delete rows is DELETE FROM table_name; without the asterisk.

2. Step 2: Check other options

   DELETE * FROM is invalid syntax, TRUNCATE and DROP are different commands.

3. Final Answer:

   DELETE FROM Employees; -> Option C

4. Quick Check:

   Correct DELETE syntax = DELETE FROM [OK]

Hint: DELETE syntax has no * after DELETE keyword [OK]

Common Mistakes:

• Using DELETE * instead of DELETE FROM
• Confusing TRUNCATE with DELETE syntax
• Using DROP to remove rows only

3. Given a table Products with 1000 rows, what will be the result after running TRUNCATE TABLE Products;?

medium

A. The table is deleted completely, including its structure.

B. All rows are removed quickly, but the table remains for new data.

C. Only rows matching a condition are deleted.

D. An error occurs because TRUNCATE requires a WHERE clause.

Solution

1. Step 1: Understand TRUNCATE effect on data

   TRUNCATE TABLE removes all rows quickly without logging each deletion but keeps the table structure intact.

2. Step 2: Check other options

   The table is deleted completely, including its structure. describes DROP, Only rows matching a condition are deleted. describes DELETE with condition, An error occurs because TRUNCATE requires a WHERE clause. is incorrect because TRUNCATE does not allow WHERE but does not error for missing WHERE.

3. Final Answer:

   All rows are removed quickly, but the table remains for new data. -> Option B

4. Quick Check:

   TRUNCATE clears rows, keeps table = All rows are removed quickly, but the table remains for new data. [OK]

Hint: TRUNCATE clears all rows fast, table stays [OK]

Common Mistakes:

• Thinking TRUNCATE deletes the table
• Expecting TRUNCATE to support WHERE clause
• Confusing TRUNCATE with DELETE

4. You run the command DELETE FROM Customers WHERE CustomerID = 10; but get an error. Which of these is the most likely cause?

medium

A. The CustomerID column is misspelled or missing.

B. You used DELETE without specifying a table.

C. You cannot use WHERE with DELETE.

D. The table Customers does not exist.

Solution

1. Step 1: Check command syntax

   The syntax DELETE FROM Customers WHERE CustomerID = 10; is correct if the table and column exist.

2. Step 2: Identify error cause

   If an error occurs, likely the column CustomerID is misspelled or missing, causing the error.

3. Final Answer:

   The CustomerID column is misspelled or missing. -> Option A

4. Quick Check:

   Column error causes DELETE failure = The CustomerID column is misspelled or missing. [OK]

Hint: Check column names carefully if DELETE with WHERE fails [OK]

Common Mistakes:

• Assuming DELETE cannot use WHERE
• Ignoring table existence errors
• Confusing syntax errors with data errors

5. You want to permanently remove a table named Orders and all its data from the database. Which command should you use and why?

hard

A. DELETE FROM Orders; because it removes all rows and keeps the table.

B. TRUNCATE TABLE Orders; because it quickly deletes all rows but keeps the table.

C. ALTER TABLE Orders DROP; because it removes the table structure.

D. DROP TABLE Orders; because it deletes the table and all its data permanently.

Solution

1. Step 1: Understand the goal

   The goal is to remove the table and all its data permanently from the database.

2. Step 2: Compare commands

   DELETE and TRUNCATE remove data but keep the table. ALTER TABLE DROP is invalid syntax. DROP TABLE removes the table and data permanently.

3. Final Answer:

   DROP TABLE Orders; because it deletes the table and all its data permanently. -> Option D

4. Quick Check:

   Remove table and data permanently = DROP TABLE [OK]

Hint: DROP removes table and data permanently [OK]

Common Mistakes:

• Using DELETE or TRUNCATE to remove table
• Trying ALTER TABLE DROP which is invalid
• Confusing DROP with DELETE