Soft delete pattern concept in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Identify active records with soft delete

Given a table users with columns id, name, and deleted_at (which is NULL if the record is active), which query returns only active users?

SQL

SELECT * FROM users WHERE deleted_at IS NULL;

A SELECT * FROM users WHERE deleted_at = '2023-01-01';

B SELECT * FROM users WHERE deleted_at IS NOT NULL;

C SELECT * FROM users WHERE deleted_at IS NULL;

D SELECT * FROM users WHERE deleted_at = '';

💡 Hint

Think about how soft delete marks records as deleted by setting a timestamp.

Soft delete means marking a record as deleted by setting a timestamp in deleted_at. Active records have deleted_at as NULL.

🧠 Conceptual

intermediate

Purpose of soft delete timestamp

Why do we use a deleted_at timestamp column in soft delete instead of just deleting the record?

A To keep a record of when the item was deleted and allow recovery if needed.

B To encrypt the record data for security.

C To speed up queries by indexing the deletion time.

D To permanently remove the record from the database immediately.

💡 Hint

Think about why we might want to keep deleted data around.

Soft delete keeps data by marking it deleted with a timestamp, allowing recovery or audit later.

📝 Syntax

advanced

Correct SQL to soft delete a record

Which SQL statement correctly soft deletes a user with id = 5 by setting deleted_at to the current timestamp?

A INSERT INTO users (deleted_at) VALUES (CURRENT_TIMESTAMP) WHERE id = 5;

B DELETE FROM users WHERE id = 5;

C UPDATE users SET deleted_at = NULL WHERE id = 5;

D UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = 5;

💡 Hint

Soft delete means updating the timestamp, not deleting the row.

Soft delete updates the deleted_at column to the current time for the target record.

❓ optimization

advanced

Optimizing queries with soft delete

To improve performance of queries filtering active records (deleted_at IS NULL), which index is best to add?

A Create an index on the <code>id</code> column only.

B Create an index on the <code>deleted_at</code> column.

C Create a full-text index on the <code>name</code> column.

D Create no index; rely on sequential scans.

💡 Hint

Think about which column is used in the WHERE clause to filter active records.

Indexing deleted_at speeds up filtering active (non-deleted) records by that column.

🔧 Debug

expert

Why does this soft delete query fail to exclude deleted records?

Consider this query to select active users:
SELECT * FROM users WHERE deleted_at = NULL;
Why does it not return any rows even if some users are active?

A Because comparing with NULL using '=' always returns false; use IS NULL instead.

B Because the table has no rows at all.

C Because the query syntax is invalid and causes an error.

D Because the <code>deleted_at</code> column has no NULL values.

💡 Hint

Remember how SQL treats NULL comparisons.

In SQL, NULL is not equal to anything, even NULL. Use IS NULL to check for NULL values.

Practice

1. What is the main idea behind the soft delete pattern in databases?

easy

A. Marking data as deleted without actually removing it from the table

B. Permanently removing data from the database

C. Backing up data before deletion

D. Encrypting data before deletion

Solution

1. Step 1: Understand soft delete concept

   Soft delete means we do not remove data but mark it as deleted.

2. Step 2: Compare options

   Only Marking data as deleted without actually removing it from the table describes marking data instead of removing it.

3. Final Answer:

   Marking data as deleted without actually removing it from the table -> Option A

4. Quick Check:

   Soft delete = Mark data deleted, not remove [OK]

Hint: Soft delete means mark deleted, don't remove [OK]

Common Mistakes:

• Thinking soft delete removes data physically
• Confusing soft delete with backup
• Assuming encryption is part of soft delete

2. Which of the following SQL table columns is commonly used to implement a soft delete?

easy

A. deleted_at TIMESTAMP

B. is_deleted BOOLEAN

C. delete_flag VARCHAR(10)

D. remove_date DATE

Solution

1. Step 1: Identify common soft delete column

   The most common column is a boolean named is_deleted to mark true/false.

2. Step 2: Check other options

   Other columns can be used but is_deleted BOOLEAN is standard and simplest.

3. Final Answer:

   is_deleted BOOLEAN -> Option B

4. Quick Check:

   Soft delete uses boolean flag = is_deleted [OK]

Hint: Soft delete uses boolean flag like is_deleted [OK]

Common Mistakes:

• Using timestamp instead of boolean for soft delete
• Choosing vague column names
• Confusing soft delete with audit columns

3. Given the table users with a boolean column is_deleted, what will this query return?

SELECT * FROM users WHERE is_deleted = FALSE;

medium

A. Only users not marked as deleted

B. Only users marked as deleted

C. No users, syntax error

D. All users including deleted ones

Solution

1. Step 1: Understand the WHERE clause

   The query filters rows where is_deleted is FALSE, meaning not deleted.

2. Step 2: Determine returned rows

   Only users not marked deleted will be returned, excluding soft deleted rows.

3. Final Answer:

   Only users not marked as deleted -> Option A

4. Quick Check:

   WHERE is_deleted = FALSE returns active users [OK]

Hint: WHERE is_deleted = FALSE filters active rows [OK]

Common Mistakes:

• Thinking it returns deleted users
• Assuming syntax error due to boolean
• Confusing TRUE and FALSE values

4. You have this query to select active users:

SELECT * FROM users WHERE is_deleted = 0;

But it returns no rows even though some users are active. What is the likely problem?

medium

A. The table has no data

B. The is_deleted column is missing

C. The query syntax is invalid

D. The column is_deleted uses TRUE/FALSE, not 0/1

Solution

1. Step 1: Check data type of is_deleted

   If is_deleted is BOOLEAN, it expects TRUE/FALSE, not numeric 0/1.

2. Step 2: Understand why query returns no rows

   Using 0 may not match FALSE in some SQL dialects, causing no rows returned.

3. Final Answer:

   The column is_deleted uses TRUE/FALSE, not 0/1 -> Option D

4. Quick Check:

   Boolean columns need TRUE/FALSE, not 0/1 [OK]

Hint: Boolean needs TRUE/FALSE, not 0/1 [OK]

Common Mistakes:

• Assuming 0 equals FALSE in all SQL dialects
• Ignoring data type of column
• Thinking syntax error causes no rows

5. You want to implement soft delete on a products table. Which SQL statement correctly marks a product as deleted without removing it?

hard

A. INSERT INTO products (product_id, is_deleted) VALUES (10, TRUE);

B. DELETE FROM products WHERE product_id = 10;

C. UPDATE products SET is_deleted = TRUE WHERE product_id = 10;

D. SELECT * FROM products WHERE is_deleted = TRUE;

Solution

1. Step 1: Understand soft delete update

   Soft delete means updating a flag, not deleting the row.

2. Step 2: Analyze each option

   UPDATE products SET is_deleted = TRUE WHERE product_id = 10; updates is_deleted to TRUE for product 10, marking it deleted.

3. Final Answer:

   UPDATE products SET is_deleted = TRUE WHERE product_id = 10; -> Option C

4. Quick Check:

   Soft delete uses UPDATE to mark deleted [OK]

Hint: Use UPDATE to mark is_deleted TRUE [OK]

Common Mistakes:

• Using DELETE instead of UPDATE
• Trying to INSERT to mark deleted
• Using SELECT instead of modifying data