DELETE with WHERE condition in SQL - Mini Project: Build & Apply

Delete Records with WHERE Condition in SQL

📖 Scenario: You manage a small library database that keeps track of books and their availability. Sometimes, books are removed from the collection when they are outdated or damaged.

🎯 Goal: Learn how to delete specific records from a database table using the DELETE statement with a WHERE condition to remove only certain rows.

📋 What You'll Learn

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

Insert specific book records into the books table

Write a DELETE query that removes only the books with status set to 'unavailable'

Verify that only the correct records are deleted

💡 Why This Matters

🌍 Real World

Deleting specific records is common when cleaning up outdated or incorrect data in databases, such as removing unavailable books from a library system.

💼 Career

Database administrators and developers often need to write DELETE queries with conditions to maintain accurate and relevant data in production databases.

1

Create the books table and insert records

Write SQL code to create a table called books with columns id (integer), title (text), and status (text). Then insert these exact records: (1, 'Python Basics', 'available'), (2, 'SQL Fundamentals', 'unavailable'), (3, 'Data Science 101', 'available'), (4, 'Old Database Guide', 'unavailable').

SQL

-- Create the books table and insert the records below
-- Your code here

Need a hint?

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

2

Set up a condition to identify unavailable books

Create a variable or comment that specifies the condition to find books where the status is 'unavailable'. Use the exact text status = 'unavailable' in your code as a comment or part of the query.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  status TEXT
);

INSERT INTO books (id, title, status) VALUES
  (1, 'Python Basics', 'available'),
  (2, 'SQL Fundamentals', 'unavailable'),
  (3, 'Data Science 101', 'available'),
  (4, 'Old Database Guide', 'unavailable');

-- Specify the condition to find unavailable books
-- Your code here

Need a hint?

Use a comment or a variable to hold the condition text exactly as status = 'unavailable'.

3

Write the DELETE query with WHERE condition

Write a SQL DELETE statement that removes rows from the books table where the status is 'unavailable'. Use the exact syntax DELETE FROM books WHERE status = 'unavailable';.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  status TEXT
);

INSERT INTO books (id, title, status) VALUES
  (1, 'Python Basics', 'available'),
  (2, 'SQL Fundamentals', 'unavailable'),
  (3, 'Data Science 101', 'available'),
  (4, 'Old Database Guide', 'unavailable');

-- Condition to find unavailable books
-- status = 'unavailable'

-- Write DELETE query below
-- Your code here

Need a hint?

Use DELETE FROM books WHERE status = 'unavailable'; to remove only the unavailable books.

4

Verify the remaining records

Add a SQL SELECT statement to show all remaining rows in the books table after deletion. Use the exact query SELECT * FROM books;.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  status TEXT
);

INSERT INTO books (id, title, status) VALUES
  (1, 'Python Basics', 'available'),
  (2, 'SQL Fundamentals', 'unavailable'),
  (3, 'Data Science 101', 'available'),
  (4, 'Old Database Guide', 'unavailable');

-- Condition to find unavailable books
-- status = 'unavailable'

DELETE FROM books WHERE status = 'unavailable';

-- Show remaining books
-- Your code here

Need a hint?

Use SELECT * FROM books; to see all books left after deletion.