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

Need a 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

Need a 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

Need a 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

Need a hint?

Use DROP TABLE Books; to remove the entire table.