DELETE with WHERE clause in MySQL - Mini Project: Build & Apply

Delete Records Using WHERE Clause in MySQL

📖 Scenario: You are managing a small online bookstore database. Sometimes, you need to remove books that are no longer available or outdated from the inventory.

🎯 Goal: Learn how to delete specific records from a MySQL table using the DELETE statement with a WHERE clause to target only certain rows.

📋 What You'll Learn

Create a table named books with columns id, title, and stock.

Insert specific book records into the books table.

Write a DELETE query that removes books with zero stock using a WHERE clause.

Verify the table structure remains intact after deletion.

💡 Why This Matters

🌍 Real World

Managing inventory databases often requires removing outdated or unavailable items safely without affecting other data.

💼 Career

Database administrators and backend developers frequently use DELETE with WHERE to maintain clean and accurate data.

1

Create the books table and insert records

Write SQL commands to create a table called books with columns id (integer primary key), title (text), and stock (integer). Then insert these exact records: (1, 'Learn SQL', 5), (2, 'Mastering Python', 0), (3, 'Web Development Basics', 3).

MySQL

-- 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 to add the rows.

2

Set up a condition to identify books with zero stock

Create a variable or comment that specifies the condition to find books where the stock is zero. This will help you target these books for deletion.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(255),
  stock INT
);

INSERT INTO books (id, title, stock) VALUES
(1, 'Learn SQL', 5),
(2, 'Mastering Python', 0),
(3, 'Web Development Basics', 3);

-- Define the condition to find books with zero stock
-- Your code here

Need a hint?

Use the WHERE clause with the condition stock = 0 to identify books with no stock.

3

Write the DELETE query with the WHERE clause

Write a DELETE statement that removes rows from the books table where the stock is zero using the condition WHERE stock = 0.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(255),
  stock INT
);

INSERT INTO books (id, title, stock) VALUES
(1, 'Learn SQL', 5),
(2, 'Mastering Python', 0),
(3, 'Web Development Basics', 3);

-- Condition to find books with zero stock
-- WHERE stock = 0

-- Write the DELETE query below
-- Your code here

Need a hint?

Use DELETE FROM books WHERE stock = 0; to remove only the books with zero stock.

4

Verify the table structure remains after deletion

Write a SQL query to select all columns from the books table to confirm that only the books with zero stock were deleted and the table still exists.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(255),
  stock INT
);

INSERT INTO books (id, title, stock) VALUES
(1, 'Learn SQL', 5),
(2, 'Mastering Python', 0),
(3, 'Web Development Basics', 3);

-- Condition to find books with zero stock
-- WHERE stock = 0

DELETE FROM books WHERE stock = 0;

-- Select all rows to verify deletion
-- Your code here

Need a hint?

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