DELETE with WHERE clause in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the DELETE statement do in SQL?

The DELETE statement removes rows from a table based on a condition or all rows if no condition is specified.

beginner

Why is the WHERE clause important in a DELETE statement?

The WHERE clause limits which rows get deleted. Without it, all rows in the table will be removed.

beginner

Write a DELETE statement to remove all users with age less than 18 from a table named 'users'.

DELETE FROM users WHERE age < 18;

intermediate

What happens if you run DELETE FROM employees without a WHERE clause?

All rows in the 'employees' table will be deleted, leaving the table empty.

intermediate

Can you use multiple conditions in the WHERE clause of a DELETE statement? How?

Yes, you can combine conditions using AND, OR. For example: DELETE FROM orders WHERE status = 'cancelled' AND order_date < '2023-01-01';

What does the WHERE clause do in a DELETE statement?

A Specifies the columns to delete

B Specifies the table to delete

C Specifies which rows to delete

D Specifies the database to delete

The WHERE clause filters rows to delete based on a condition.

What happens if you omit the WHERE clause in a DELETE statement?

A Only one row is deleted

B All rows in the table are deleted

C No rows are deleted

D The table structure is deleted

Without WHERE, DELETE removes all rows from the table.

Which of the following is a correct DELETE statement to remove users named 'John'?

A DELETE FROM users WHERE name = 'John';

B REMOVE FROM users WHERE name = 'John';

C DELETE users WHERE name = 'John';

D DELETE * FROM users WHERE name = 'John';

The correct syntax is DELETE FROM table WHERE condition.

How can you delete rows where age is greater than 30 and status is 'inactive'?

A DELETE FROM table WHERE age > 30 OR status = 'inactive';

B DELETE FROM table WHERE age > 30 status = 'inactive';

C DELETE FROM table WHERE age > 30, status = 'inactive';

D DELETE FROM table WHERE age > 30 AND status = 'inactive';

Use AND to combine multiple conditions that must all be true.

Which SQL keyword is used to delete rows from a table?

A DELETE

B REMOVE

C DROP

D ERASE

DELETE is the SQL keyword to remove rows from a table.

Explain how to safely delete specific rows from a table using the DELETE statement.

Think about how to target only the rows you want to remove.

Describe what happens if you run a DELETE statement without a WHERE clause and why it can be risky.

Consider the effect on the whole table.

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