DELETE with WHERE condition in SQL - 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 specified condition. Without a condition, it deletes all rows.

beginner

Why is the WHERE clause important in a DELETE statement?

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

beginner

Write a DELETE statement to remove all users older than 30 from a table named Users.

DELETE FROM Users WHERE age > 30;

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 but still existing.

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 this SQL do? DELETE FROM Products WHERE price < 10;

A Deletes all products with price less than 10

B Deletes all products with price greater than 10

C Deletes all products

D Deletes the Products table

The WHERE clause limits deletion to rows where price is less than 10.

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

A Deletes all rows in the table

B Deletes no rows

C Deletes only the first row

D Deletes the table structure

Without WHERE, DELETE removes every row from the table.

Which keyword is used to specify which rows to delete?

A ORDER BY

B FROM

C SELECT

D WHERE

WHERE defines the condition to select rows for deletion.

How do you delete rows where the column 'status' equals 'inactive'?

A DELETE * FROM table WHERE status = 'inactive';

B DELETE status FROM table;

C DELETE FROM table WHERE status = 'inactive';

D REMOVE FROM table WHERE status = 'inactive';

Correct syntax uses DELETE FROM with WHERE condition.

Can you delete rows based on multiple conditions?

A No, only one condition is allowed

B Yes, using AND or OR in WHERE clause

C Yes, but only with OR

D Yes, but only with AND

Multiple conditions can be combined with AND or OR in WHERE.

Explain how the DELETE statement works with a WHERE condition in SQL.

Think about deleting specific items from a list by setting rules.

Describe the risks of running a DELETE statement without a WHERE clause and how to avoid them.

Consider what happens if you clear your entire contact list by mistake.

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