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

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

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

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

Hint

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

Practice

1. What does the WHERE clause do in a DELETE statement in MySQL?

easy

A. It limits which rows get deleted based on a condition.

B. It specifies the table to delete from.

C. It adds new rows to the table.

D. It changes the structure of the table.

Solution

1. Step 1: Understand the role of DELETE

   The DELETE statement removes rows from a table.

2. Step 2: Understand the role of WHERE

   The WHERE clause filters which rows are affected by DELETE.

3. Final Answer:

   It limits which rows get deleted based on a condition. -> Option A

4. Quick Check:

   WHERE clause = filter rows to delete [OK]

Hint: WHERE filters rows to delete, else all rows removed [OK]

Common Mistakes:

• Thinking WHERE adds rows
• Confusing WHERE with table name
• Assuming DELETE changes table structure

2. Which of the following is the correct syntax to delete rows where the column age is less than 18 from a table named users?

easy

A. DELETE users WHERE age < 18;

B. DELETE FROM users WHERE age < 18;

C. DELETE * FROM users WHERE age < 18;

D. DELETE FROM users IF age < 18;

Solution

1. Step 1: Check DELETE syntax

   The correct syntax starts with DELETE FROM followed by the table name.

2. Step 2: Verify WHERE clause usage

   WHERE clause uses condition like age < 18 to filter rows.

3. Final Answer:

   DELETE FROM users WHERE age < 18; -> Option B

4. Quick Check:

   Correct DELETE syntax = DELETE FROM users WHERE age < 18; [OK]

Hint: Use DELETE FROM table WHERE condition; [OK]

Common Mistakes:

• Omitting FROM keyword
• Using DELETE * which is invalid
• Replacing WHERE with IF

3. Given the table employees with rows:
{id:1, name:'Alice', dept:'HR'},
{id:2, name:'Bob', dept:'IT'},
{id:3, name:'Carol', dept:'HR'}
What will be the remaining rows after running:
DELETE FROM employees WHERE dept = 'HR';

medium

A. [{id:2, name:'Bob', dept:'IT'}]

B. [{id:1, name:'Alice', dept:'HR'}, {id:3, name:'Carol', dept:'HR'}]

C. [] (empty table)

D. [{id:1, name:'Alice', dept:'HR'}, {id:2, name:'Bob', dept:'IT'}, {id:3, name:'Carol', dept:'HR'}]

Solution

1. Step 1: Identify rows matching WHERE condition

   Rows with dept = 'HR' are Alice and Carol (id 1 and 3).

2. Step 2: Delete matching rows and keep others

   Only Bob (id 2) with dept 'IT' remains after deletion.

3. Final Answer:

   [{id:2, name:'Bob', dept:'IT'}] -> Option A

4. Quick Check:

   Deleted HR rows, kept IT row [OK]

Hint: Delete rows matching condition; others stay [OK]

Common Mistakes:

• Deleting all rows by missing WHERE
• Keeping deleted rows by mistake
• Confusing which rows match condition

4. What is wrong with this query?
DELETE FROM orders WHERE;

medium

A. DELETE requires SELECT keyword.

B. DELETE cannot have WHERE clause.

C. Missing condition after WHERE clause.

D. Table name is missing.

Solution

1. Step 1: Check syntax after WHERE

   WHERE clause must have a condition to filter rows.

2. Step 2: Identify missing condition error

   Query ends with WHERE but no condition is given, causing syntax error.

3. Final Answer:

   Missing condition after WHERE clause. -> Option C

4. Quick Check:

   WHERE needs condition [OK]

Hint: Always add condition after WHERE [OK]

Common Mistakes:

• Leaving WHERE empty
• Thinking WHERE is optional but incomplete
• Confusing DELETE syntax with SELECT

5. You have a table products with columns id, name, and stock. You want to delete all products that have zero stock but keep products with NULL stock values. Which query correctly deletes only products with stock exactly zero?

hard

A. DELETE FROM products WHERE stock <= 0;

B. DELETE FROM products WHERE stock IS NULL;

C. DELETE FROM products WHERE stock != NULL;

D. DELETE FROM products WHERE stock = 0;

Solution

1. Step 1: Understand NULL behavior in WHERE

   Comparisons with NULL using =, !=, <= return UNKNOWN (false); use IS NULL to check NULL.

2. Step 2: Identify condition for stock exactly zero

   stock = 0 matches only zero, excludes NULL and other values.

3. Step 3: Analyze other options

   stock <= 0 deletes zero and negatives (keeps NULL); stock IS NULL deletes NULL stocks; stock != NULL never matches (comparison with NULL always UNKNOWN).

4. Final Answer:

   DELETE FROM products WHERE stock = 0; -> Option D

5. Quick Check:

   Use = 0 to delete zero stock only [OK]

Hint: Use = 0 to delete zero, IS NULL for nulls [OK]

Common Mistakes:

• Using <= 0 deletes negatives too
• Using != NULL deletes nothing
• Confusing NULL with zero