DELETE with WHERE clause in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output after DELETE with a simple WHERE?

Given a table Employees with rows:

id | name    | department
1  | Alice   | Sales
2  | Bob     | HR
3  | Charlie | Sales
4  | Diana   | IT

What rows remain after running this query?

DELETE FROM Employees WHERE department = 'Sales';

MySQL

SELECT * FROM Employees;

A []

B [{"id":2,"name":"Bob","department":"HR"},{"id":4,"name":"Diana","department":"IT"}]

C [{"id":1,"name":"Alice","department":"Sales"},{"id":2,"name":"Bob","department":"HR"},{"id":3,"name":"Charlie","department":"Sales"},{"id":4,"name":"Diana","department":"IT"}]

D [{"id":1,"name":"Alice","department":"Sales"},{"id":3,"name":"Charlie","department":"Sales"}]

💡 Hint

DELETE removes rows matching the WHERE condition.

The DELETE query removes all employees in the Sales department, leaving only Bob and Diana.

🧠 Conceptual

intermediate

Which DELETE statement removes only one specific row?

Which DELETE query will remove only the employee with id = 3 from the Employees table?

A DELETE Employees WHERE id = 3;

B DELETE FROM Employees WHERE department = 'Sales';

C DELETE FROM Employees;

D DELETE FROM Employees WHERE id = 3;

💡 Hint

Use WHERE to target a specific row by id.

Option D deletes only the row where id equals 3. Option D deletes all Sales employees. Option D deletes all rows. Option D is invalid syntax.

📝 Syntax

advanced

Identify the syntax error in this DELETE query

What is wrong with this DELETE statement?

DELETE Employees WHERE id = 5;

A Table name should be in quotes

B WHERE clause cannot be used with DELETE

C Missing FROM keyword after DELETE

D DELETE cannot delete by id

💡 Hint

Check the correct DELETE syntax.

The correct syntax requires the FROM keyword: DELETE FROM Employees WHERE id = 5;

❓ optimization

advanced

Which DELETE query is more efficient for removing multiple rows?

You want to delete employees with ids 2, 4, and 6. Which query is best?

A DELETE FROM Employees WHERE id IN (2, 4, 6);

B DELETE FROM Employees WHERE id BETWEEN 2 AND 6;

C DELETE FROM Employees WHERE id = 2 OR id = 4 OR id = 6;

D DELETE FROM Employees WHERE id > 1 AND id < 7;

💡 Hint

Use concise syntax for multiple values.

Option A uses IN which is clearer and often optimized by the database engine. Option A is longer but works. Options B and D delete extra rows.

🔧 Debug

expert

Why does this DELETE query delete all rows unexpectedly?

Given this query:

DELETE FROM Employees WHERE department = 'Marketing' OR 1=1;

Why does it delete all rows?

A Because 1=1 is always true, so WHERE condition matches all rows

B Because DELETE ignores WHERE clause

C Because 'Marketing' is a reserved keyword

D Because OR is not allowed in WHERE clause

💡 Hint

Think about how OR conditions work in WHERE.

The condition '1=1' is always true, so the WHERE clause matches every row, causing all rows to be deleted.

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