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.