Why DELETE needs caution in SQL - See It in Action

Why DELETE Needs Caution in SQL

📖 Scenario: You are managing a small library database. You want to remove some books that are no longer available. But deleting data can cause problems if done carelessly.

🎯 Goal: Build a safe SQL DELETE operation that removes only specific books without affecting other data.

📋 What You'll Learn

Create a table called books with columns id, title, and available

Insert 3 specific book records into books

Add a condition variable to select only unavailable books

Write a DELETE query that deletes only books marked as unavailable

💡 Why This Matters

🌍 Real World

Deleting data is common in databases but must be done carefully to avoid losing important information.

💼 Career

Database administrators and developers must write safe DELETE queries to maintain data integrity and prevent accidental data loss.

1

Create the books table and insert data

Write SQL to create a table called books with columns id (integer), title (text), and available (boolean). Then insert these three rows exactly: (1, 'Moby Dick', true), (2, 'Hamlet', false), (3, '1984', true).

SQL

-- Create the books table and insert 3 rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Add a condition variable for unavailable books

Create a variable called unavailable_condition that holds the SQL condition available = FALSE as a string.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  available BOOLEAN
);

INSERT INTO books (id, title, available) VALUES
  (1, 'Moby Dick', TRUE),
  (2, 'Hamlet', FALSE),
  (3, '1984', TRUE);

-- Define the condition for unavailable books
-- Your code here

Need a hint?

Think of the condition you want to use in the DELETE query's WHERE clause.

3

Write the DELETE query with condition

Write a SQL DELETE statement that removes rows from books where available = FALSE.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  available BOOLEAN
);

INSERT INTO books (id, title, available) VALUES
  (1, 'Moby Dick', TRUE),
  (2, 'Hamlet', FALSE),
  (3, '1984', TRUE);

-- Delete only unavailable books
DELETE FROM books WHERE available = FALSE;
-- Your code here

Need a hint?

Use DELETE FROM books WHERE available = FALSE; to remove only unavailable books.

4

Add a safety check with a transaction

Wrap the DELETE statement inside a transaction using BEGIN TRANSACTION; before and COMMIT; after the DELETE statement.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  available BOOLEAN
);

INSERT INTO books (id, title, available) VALUES
  (1, 'Moby Dick', TRUE),
  (2, 'Hamlet', FALSE),
  (3, '1984', TRUE);

-- Wrap DELETE in a transaction
BEGIN TRANSACTION;
DELETE FROM books WHERE available = FALSE;
COMMIT;
-- Your code here

Need a hint?

Use BEGIN TRANSACTION; before and COMMIT; after the DELETE to ensure safety.