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.