Soft delete pattern concept in SQL - Mini Project: Build & Apply

Implementing Soft Delete Pattern in SQL

📖 Scenario: You are managing a customer database for a small online store. Instead of permanently deleting customer records, you want to mark them as inactive so you can keep their data for future reference.

🎯 Goal: Build a simple SQL table with a soft delete pattern by adding an is_active column. Then write queries to mark customers as inactive and to select only active customers.

📋 What You'll Learn

Create a table called customers with columns id, name, and is_active

Set is_active to TRUE by default for new customers

Write an SQL query to mark a customer as inactive using UPDATE

Write an SQL query to select only active customers using WHERE is_active = TRUE

💡 Why This Matters

🌍 Real World

Soft delete is used in many applications to avoid losing data permanently and to allow easy recovery of deleted records.

💼 Career

Understanding soft delete patterns is important for database administrators and backend developers to manage data safely and comply with data retention policies.

1

Create the customers table

Write an SQL statement to create a table called customers with three columns: id as an integer primary key, name as text, and is_active as a boolean that defaults to TRUE.

SQL

-- Your code here to create the customers table

Need a hint?

Use CREATE TABLE with the specified columns and set is_active default to TRUE.

2

Insert sample customers

Write SQL INSERT statements to add two customers to the customers table: one with id 1 and name 'Alice', and another with id 2 and name 'Bob'. Do not specify is_active so it uses the default.

SQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    is_active BOOLEAN DEFAULT TRUE
);
-- Your code here to insert two customers

Need a hint?

Use INSERT INTO customers (id, name) VALUES (...) twice with the exact values.

3

Mark a customer as inactive

Write an SQL UPDATE statement to set is_active to FALSE for the customer with id 2.

SQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    is_active BOOLEAN DEFAULT TRUE
);

INSERT INTO customers (id, name) VALUES (1, 'Alice');
INSERT INTO customers (id, name) VALUES (2, 'Bob');
-- Your code here to mark Bob as inactive

Need a hint?

Use UPDATE customers SET is_active = FALSE WHERE id = 2 to mark Bob inactive.

4

Select only active customers

Write an SQL SELECT statement to get all columns from customers where is_active is TRUE.

SQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    is_active BOOLEAN DEFAULT TRUE
);

INSERT INTO customers (id, name) VALUES (1, 'Alice');
INSERT INTO customers (id, name) VALUES (2, 'Bob');

UPDATE customers SET is_active = FALSE WHERE id = 2;
-- Your code here to select active customers

Need a hint?

Use SELECT * FROM customers WHERE is_active = TRUE to get only active customers.