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.

Practice

1. What does the DELETE statement with a WHERE condition do in SQL?

easy

A. Removes only the rows that match the condition specified in WHERE.

B. Removes all rows from the table regardless of the condition.

C. Updates the rows that match the condition.

D. Creates a new table with rows matching the condition.

Solution

1. Step 1: Understand DELETE statement

   The DELETE statement removes rows from a table.

2. Step 2: Effect of WHERE condition

   When a WHERE condition is present, only rows matching that condition are deleted.

3. Final Answer:

   Removes only the rows that match the condition specified in WHERE. -> Option A

4. Quick Check:

   DELETE with WHERE removes matching rows only [OK]

Hint: DELETE with WHERE deletes matching rows only, not all [OK]

Common Mistakes:

• Thinking DELETE always removes all rows
• Confusing DELETE with UPDATE
• Assuming WHERE creates a new table

2. Which of the following is the correct syntax to delete rows where the column age is greater than 30 from a table named employees?

easy

A. REMOVE FROM employees WHERE age > 30;

B. DELETE employees WHERE age > 30;

C. DELETE FROM employees WHERE age > 30;

D. DELETE * FROM employees WHERE age > 30;

Solution

1. Step 1: Identify correct DELETE syntax

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

2. Step 2: Check WHERE clause usage

   The WHERE clause filters rows to delete; it must follow the table name.

3. Final Answer:

   DELETE FROM employees WHERE age > 30; -> Option C

4. Quick Check:

   DELETE FROM + table + WHERE condition [OK]

Hint: Use DELETE FROM table WHERE condition; to delete specific rows [OK]

Common Mistakes:

• Omitting FROM keyword
• Using REMOVE instead of DELETE
• Adding * after DELETE which is invalid

3. Given the table products with rows:
{id:1, name:'Pen', stock:10}, {id:2, name:'Pencil', stock:0}, {id:3, name:'Eraser', stock:5}
What will be the result of this query?

DELETE FROM products WHERE stock = 0;

medium

A. Deletes all products because stock = 0 is ignored.

B. Deletes the product with id 2 only.

C. Deletes no rows because stock = 0 is false for all.

D. Causes a syntax error.

Solution

1. Step 1: Identify rows matching condition

   Only the product with stock = 0 is the one with id 2 (Pencil).

2. Step 2: Effect of DELETE with WHERE

   DELETE removes only rows matching the condition, so only id 2 is deleted.

3. Final Answer:

   Deletes the product with id 2 only. -> Option B

4. Quick Check:

   DELETE WHERE stock=0 removes matching rows only [OK]

Hint: DELETE removes only rows matching WHERE condition [OK]

Common Mistakes:

• Assuming all rows are deleted
• Thinking condition is ignored
• Expecting syntax error for valid query

4. What is wrong with this SQL statement?

DELETE FROM orders WHERE;

medium

A. It updates rows instead of deleting.

B. It deletes all rows because WHERE is empty.

C. It creates a new table instead of deleting.

D. Missing condition after WHERE causes syntax error.

Solution

1. Step 1: Check syntax after WHERE

   WHERE clause must have a condition; empty WHERE is invalid syntax.

2. Step 2: Result of missing condition

   SQL parser throws syntax error because condition is missing.

3. Final Answer:

   Missing condition after WHERE causes syntax error. -> Option D

4. Quick Check:

   WHERE must have a condition, else syntax error [OK]

Hint: WHERE must always have a condition, never empty [OK]

Common Mistakes:

• Leaving WHERE empty thinking it deletes all
• Confusing DELETE with UPDATE
• Expecting query to run without error

5. You have a table users with columns id, name, and status. You want to delete all users whose status is either 'inactive' or 'banned'. Which query correctly deletes these users?

hard

A. DELETE FROM users WHERE status IN ('inactive', 'banned');

B. DELETE FROM users WHERE status = 'inactive' OR 'banned';

C. DELETE FROM users WHERE status = 'inactive' AND status = 'banned';

D. DELETE FROM users WHERE status = 'inactive', 'banned';

Solution

1. Step 1: Understand condition for multiple values

   To delete rows where status is 'inactive' or 'banned', use OR or IN clause.

2. Step 2: Compare options

   DELETE FROM users WHERE status = 'inactive' OR 'banned'; -- OR 'banned' (literal string) evaluates to true, deleting all rows.
   DELETE FROM users WHERE status IN ('inactive', 'banned'); -- correctly matches either value.
   DELETE FROM users WHERE status = 'inactive' AND status = 'banned'; -- AND condition is impossible, deletes none.
   DELETE FROM users WHERE status = 'inactive', 'banned'; -- invalid comma syntax.

3. Final Answer:

   DELETE FROM users WHERE status IN ('inactive', 'banned'); -> Option A

4. Quick Check:

   Use WHERE status IN (...) for multiple values [OK]

Hint: Use WHERE column IN (values) to delete multiple matches [OK]

Common Mistakes:

• Using AND instead of OR for multiple conditions
• Incorrect syntax with commas in WHERE
• Not using IN for multiple values