DELETE with WHERE condition in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What rows remain after DELETE with WHERE?

Given the table Employees with columns id, name, and department, and these rows:

1, 'Alice', 'Sales'
2, 'Bob', 'HR'
3, 'Charlie', 'Sales'
4, 'Diana', 'IT'

What rows remain after running:

DELETE FROM Employees WHERE department = 'Sales';

SQL

DELETE FROM Employees WHERE department = 'Sales';

A []

B [{id: 1, name: 'Alice', department: 'Sales'}, {id: 3, name: 'Charlie', department: 'Sales'}]

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

D [{id: 2, name: 'Bob', department: 'HR'}, {id: 4, name: 'Diana', department: 'IT'}]

💡 Hint

DELETE removes rows matching the WHERE condition.

The DELETE command removes rows where department is 'Sales'. So only Bob and Diana remain.

📝 Syntax

intermediate

Which DELETE statement is syntactically correct?

Choose the correct DELETE statement that removes rows where age is less than 30 from the Users table.

A DELETE FROM Users WHERE age < 30;

B DELETE Users WHERE age < 30;

C DELETE * FROM Users WHERE age < 30;

D DELETE FROM Users WHERE age >= 30;

💡 Hint

DELETE requires FROM keyword and correct comparison operators.

Option A uses correct syntax: DELETE FROM table WHERE condition. Others have syntax errors.

❓ optimization

advanced

How to optimize DELETE with large data and WHERE condition?

You want to delete millions of rows from Orders where status = 'cancelled'. Which approach is best to avoid long locks and improve performance?

A DROP TABLE Orders; CREATE TABLE Orders AS SELECT * FROM Orders WHERE status != 'cancelled';

B DELETE FROM Orders WHERE status = 'cancelled';

C DELETE FROM Orders WHERE status = 'cancelled' LIMIT 10000; (repeat until done)

D UPDATE Orders SET status = NULL WHERE status = 'cancelled';

💡 Hint

Deleting in smaller batches reduces lock time.

Option C deletes in batches, reducing lock contention and improving performance. Option C locks all rows at once. Option C is risky and complex. Option C does not delete rows.

🔧 Debug

advanced

Why does this DELETE statement fail with syntax error?

Given this SQL:

DELETE FROM Customers WHERE name = 'John' AND;

What is the cause of the syntax error?

SQL

DELETE FROM Customers WHERE name = 'John' AND;

A The DELETE statement requires a semicolon before WHERE.

B The WHERE clause ends with AND without a condition after it.

C The table name Customers is reserved and cannot be used.

D The string 'John' must be enclosed in double quotes.

💡 Hint

Check the WHERE clause syntax carefully.

The AND operator must be followed by another condition. Ending with AND causes syntax error.

🧠 Conceptual

expert

What happens if DELETE is run without WHERE?

Consider the table Products with 1000 rows. What is the effect of running:

DELETE FROM Products;

Choose the correct statement.

SQL

DELETE FROM Products;

A All 1000 rows are deleted, leaving an empty table.

B The command deletes no rows because WHERE is missing.

C The table structure is deleted along with data.

D Only the first row is deleted by default.

💡 Hint

Think about what DELETE does without a filter.

DELETE without WHERE removes all rows but keeps the table structure intact.