DELETE vs TRUNCATE behavior in SQL - Hands-On Comparison

Understanding DELETE vs TRUNCATE Behavior in SQL

📖 Scenario: You are managing a small company's employee database. Sometimes you need to remove all records from a table quickly, and other times you want to delete records one by one or selectively.

🎯 Goal: Build SQL commands to create a table, insert data, and then practice removing data using both DELETE and TRUNCATE commands to see how they behave differently.

📋 What You'll Learn

Create a table named employees with columns id (integer) and name (text).

Insert exactly three employees with ids 1, 2, and 3 and names 'Alice', 'Bob', and 'Charlie'.

Write a DELETE statement to remove the employee with id 2.

Write a TRUNCATE statement to remove all remaining employees quickly.

💡 Why This Matters

🌍 Real World

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

💼 Career

Understanding DELETE vs TRUNCATE is essential for roles like database developer, data analyst, and backend engineer to manage data cleanup and maintenance tasks.

1

Create the employees table and insert data

Write SQL commands to create a table called employees with columns id as integer and name as text. Then insert three rows with these exact values: (1, 'Alice'), (2, 'Bob'), and (3, 'Charlie').

SQL

-- Create the employees table and insert three employees
-- Your code here

Need a hint?

Use CREATE TABLE to make the table, then use three INSERT INTO statements for each employee.

2

Add a DELETE statement to remove one employee

Add a SQL DELETE statement that removes the employee with id equal to 2 from the employees table.

SQL

CREATE TABLE employees (id INTEGER, name TEXT);
INSERT INTO employees (id, name) VALUES (1, 'Alice');
INSERT INTO employees (id, name) VALUES (2, 'Bob');
INSERT INTO employees (id, name) VALUES (3, 'Charlie');
-- Add a DELETE statement to remove employee with id 2
-- Your code here

Need a hint?

Use DELETE FROM employees WHERE id = 2; to remove the employee with id 2.

3

Add a TRUNCATE statement to remove all remaining employees

Add a SQL TRUNCATE statement that removes all remaining rows from the employees table quickly.

SQL

CREATE TABLE employees (id INTEGER, name TEXT);
INSERT INTO employees (id, name) VALUES (1, 'Alice');
INSERT INTO employees (id, name) VALUES (2, 'Bob');
INSERT INTO employees (id, name) VALUES (3, 'Charlie');
DELETE FROM employees WHERE id = 2;
-- Add a TRUNCATE statement to remove all remaining employees
-- Your code here

Need a hint?

Use TRUNCATE TABLE employees; to quickly remove all rows.

4

Add a comment explaining the difference between DELETE and TRUNCATE

Add a SQL comment explaining that DELETE removes rows one by one and can be selective, while TRUNCATE removes all rows quickly and resets the table.

SQL

CREATE TABLE employees (id INTEGER, name TEXT);
INSERT INTO employees (id, name) VALUES (1, 'Alice');
INSERT INTO employees (id, name) VALUES (2, 'Bob');
INSERT INTO employees (id, name) VALUES (3, 'Charlie');
DELETE FROM employees WHERE id = 2;
TRUNCATE TABLE employees;
-- Add a comment explaining the difference between DELETE and TRUNCATE
-- Your code here

Need a hint?

Use SQL comment syntax -- to add your explanation.