SQL Quiz: Delete With Where
10 questions · exam conditions
0:00
Delete With WhereQuestion 1 of 10

The customers table contains inactive customers C1 and C2. The orders table has an order referencing C1 but none referencing C2. An immediate foreign key from orders.customer_id to customers.customer_id uses NO ACTION, and the database provides statement-level atomicity.

What happens when DELETE FROM customers WHERE status = 'inactive'; is executed?

Only C2 is deleted; C1 remains because it is referenced by an order
Both C1 and C2 are deleted; C1's referencing order row is left unreferenced
The statement fails due to a constraint violation; neither customer is deleted
Only C1 is deleted; C2 remains because it has no associated orders
← Back to quizzes

SQL Quiz

SQL Quiz: Delete With Where

Practice Delete With Where in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Delete With Where, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

The customers table contains inactive customers C1 and C2. The orders table has an order referencing C1 but none referencing C2. An immediate foreign key from orders.customer_id to customers.customer_id uses NO ACTION, and the database provides statement-level atomicity.

What happens when DELETE FROM customers WHERE status = 'inactive'; is executed?

  1. Only C2 is deleted; C1 remains because it is referenced by an order
  2. Both C1 and C2 are deleted; C1's referencing order row is left unreferenced
  3. The statement fails due to a constraint violation; neither customer is deleted (correct answer)
  4. Only C1 is deleted; C2 remains because it has no associated orders
Explanation: When working with foreign key constraints, the critical concept is when the constraint is checked and what happens if it's violated. NO ACTION means the database checks the constraint at the end of the statement — but crucially, if any violation exists at that point, the entire statement is rolled back, not just the offending row. Here's the logic: when you run DELETE FROM customers WHERE status = 'inactive', the database attempts to delete both C1 and C2. After processing the full statement, it checks referential integrity. C1 is still referenced by a row in orders, so a violation exists. Because the database enforces statement-level atomicity, it cannot partially commit — either all rows are deleted or none are. The constraint violation causes the entire statement to fail, leaving both C1 and C2 in the table. That makes C the correct answer. A is tempting because it sounds like "smart" behavior — delete only what's safe, skip what's not. But SQL doesn't work that way by default. A constraint violation doesn't surgically skip one row; it fails the whole statement. B would only be possible if the foreign key used ON DELETE SET NULL or ON DELETE CASCADE, which explicitly handle orphaned references — NO ACTION does neither. D is the opposite of A's logic and equally wrong; there's no mechanism that would delete only the referenced customer while keeping the unreferenced one. Study tip: Always pair the constraint type (NO ACTION, CASCADE, SET NULL) with the timing (IMMEDIATE vs. DEFERRED) — together they determine exactly when violations are caught and what gets rolled back.

Question 2

The inventory table contains I1 in category A with quantity 4, I2 in category A with quantity 6, I3 in category B with quantity 2, and I4 in category C with quantity 1. The policies table contains reorder levels 3 and 7 for category A, no rows for category B, and two rows with reorder level 1 for category C.

Which inventory rows are deleted by DELETE FROM inventory WHERE quantity < (SELECT AVG(reorder_level) FROM policies WHERE policies.category = inventory.category);?

  1. I1, I3, and I4, because all three have low quantities
  2. I1 and I3, because both quantities are below an applicable threshold
  3. I1 and I4, because category C's equality satisfies the comparison
  4. I1 only, because its quantity is below category A's average (correct answer)
Explanation: Whenever you see a correlated subquery in a DELETE or SELECT statement, your job is to evaluate the subquery independently for each row in the outer table — the result can differ per row, and some rows may produce NULL. For each inventory row, compute AVG(reorder_level) from policies where the categories match:
  • I1 (category A, qty 4): Policies has reorder levels 3 and 7 → AVG = 5. Is 4 < 5? Yes → deleted.
  • I2 (category A, qty 6): Same AVG = 5. Is 6 < 5? No → kept.
  • I3 (category B, qty 2): No rows in policies for category B → AVG of an empty set = NULL. Any comparison with NULL yields UNKNOWN, not TRUE → kept.
  • I4 (category C, qty 1): Policies has two rows both with reorder level 1 → AVG = 1. Is 1 < 1? No → kept.
So only I1 is deleted, confirming answer D. Answer A incorrectly includes I3 and I4 — it assumes low quantities automatically qualify without checking the subquery result. Answer B correctly excludes I4 but wrongly includes I3, ignoring that a NULL subquery result silently blocks deletion. Answer C includes I4 based on a misreading — "less than" is strict, and 1 < 1 is false even when the average equals the quantity. The key study tip: NULL propagation kills comparisons silently. When a correlated subquery returns no rows, aggregate functions like AVG return NULL, and quantity < NULL is UNKNOWN — the row is never deleted. Always trace through NULL cases explicitly on exam questions.

Question 3

The items table contains codes 'A_', 'A_7', 'AB_7', and 'A%7'. The database treats backslash as the escape character only when it is explicitly specified by an ESCAPE clause.

Which codes are deleted by DELETE FROM items WHERE code LIKE 'A_%' ESCAPE '\';?

  1. 'A_' and 'A_7', because the underscore is treated literally (correct answer)
  2. 'A_7' and 'AB_7', because the underscore matches one character
  3. 'A_', 'A_7', and 'AB_7', because the percent matches any suffix
  4. 'A_7' and 'A%7', because both contain a wildcard character
Explanation: When working with SQL's LIKE operator, the key skill is parsing the pattern character by character — especially when an ESCAPE clause is involved. Here, the pattern is 'A_%' with ESCAPE '\'. The backslash tells SQL to treat the very next character as a literal, not a wildcard. So _ means a literal underscore _, not "any single character." After that escaped underscore, the % remains a normal wildcard, matching zero or more of any characters. Breaking down the full pattern: it matches strings that start with A, followed by a literal _, followed by anything (including nothing). Let's test each code:
  • 'A_' → starts with A, has a literal _, then nothing — matches ✓
  • 'A_7' → starts with A, has a literal _, then 7 — matches ✓
  • 'AB_7' → starts with A, but next character is B, not _ — no match ✗
  • 'A%7' → starts with A, but next character is %, not _ — no match ✗
So answer A is correct: only 'A_' and 'A_7' are deleted. Answer B is wrong because it ignores the escape — treating _ as a wildcard would match 'AB_7', but the escape makes it literal. Answer C incorrectly includes 'AB_7' for the same reason. Answer D confuses the issue entirely — the pattern matches based on position and literal characters, not whether the stored value contains wildcard symbols. Study tip: Whenever you see ESCAPE in a LIKE query, immediately identify which characters are escaped and mentally relabel them as literals before evaluating the pattern.

Question 4

The events table contains E1 at 2026-03-31 23:59:59, E2 at 2026-04-01 00:00:00, E3 at 2026-04-30 18:30:00, and E4 at 2026-05-01 00:00:00.

Which events are deleted by DELETE FROM events WHERE event_time >= '2026-04-01 00:00:00' AND event_time < '2026-05-01 00:00:00';?

  1. E2 and E3 only, because the start is included and the end is excluded (correct answer)
  2. E1, E2, and E3, because all occur no later than April
  3. E2, E3, and E4, because both stated boundaries are included
  4. E3 only, because both midnight boundary values are excluded
Explanation: When filtering rows with comparison operators in SQL, the key is understanding inclusive vs. exclusive boundaries. The >= operator includes the boundary value itself, while < excludes it. This is the classic "half-open interval" pattern — everything from the start up to, but not including, the end. Applying that logic here: the condition event_time >= '2026-04-01 00:00:00' AND event_time < '2026-05-01 00:00:00' selects rows where the timestamp is at or after April 1st midnight, and strictly before May 1st midnight. E2 (2026-04-01 00:00:00) satisfies >= exactly, so it's included. E3 (2026-04-30 18:30:00) falls comfortably inside the range, so it's included. E4 (2026-05-01 00:00:00) hits the < boundary exactly and is therefore excluded. E1 (2026-03-31 23:59:59) falls before April 1st and is also excluded. That makes A correct — only E2 and E3 are deleted. Choice B incorrectly includes E1, ignoring that March 31st falls before the >= boundary. Choice C incorrectly includes E4, treating the upper bound < as if it were <= — a very common mistake when people assume "both ends are included." Choice D incorrectly excludes E2, misreading >= as strictly greater than >. Study tip: Always pause on >= vs > and <= vs < in WHERE clauses — one character changes everything. When you see date range filters, explicitly check each boundary value against the operator to avoid the "off-by-one-moment" trap.

Question 5

The products table contains product IDs 1, 2, and 3. The subquery SELECT product_id FROM discontinued_products returns two values: 2 and NULL.

What is the result of DELETE FROM products WHERE product_id NOT IN (SELECT product_id FROM discontinued_products);?

  1. Products 1 and 3 are deleted; product 2 remains
  2. Product 2 is deleted; products 1 and 3 remain
  3. No products are deleted by the statement (correct answer)
  4. All three products are deleted by the statement
Explanation: Whenever you see NOT IN with a subquery, your first instinct should be to check whether that subquery can return NULL — because this is one of SQL's most notorious traps. Here's why it matters: SQL evaluates NOT IN using three-valued logic. When you write product_id NOT IN (2, NULL), SQL internally checks product_id <> 2 AND product_id <> NULL. The problem is that any comparison with NULL produces UNKNOWN, not TRUE or FALSE. So for product 1: 1 <> 2 is TRUE, but 1 <> NULL is UNKNOWN — and TRUE AND UNKNOWN is UNKNOWN. Since the condition never evaluates to TRUE for any row, the WHERE clause matches nothing. No rows are deleted. That makes C the correct answer. Answer A is the trap most students fall into — it assumes NOT IN behaves like a simple exclusion filter, correctly ignoring the NULL and deleting products 1 and 3. This would be right if the subquery returned only (2), but the NULL poisons the entire result. Answer B describes the behavior of IN, not NOT IN — it deletes the product found in the subquery rather than those not found. Answer D suggests all rows are deleted, which would require every product_id to match the WHERE condition, which the NULL problem prevents. Your study tip: mentally flag any NOT IN subquery and ask "could this return NULL?" If yes, consider rewriting with NOT EXISTS or filtering out NULLs using WHERE product_id IS NOT NULL inside the subquery — both are NULL-safe alternatives.

Question 6

The tickets table contains: T1 ('closed', 'low'), T2 ('resolved', 'high'), T3 ('resolved', 'low'), and T4 ('open', 'high'). The values in parentheses are status and priority, respectively.

Which tickets are deleted by DELETE FROM tickets WHERE status = 'closed' OR status = 'resolved' AND priority = 'high';?

  1. T2 only, because the priority test applies to both statuses
  2. T1 and T2, because AND is evaluated before OR (correct answer)
  3. T1, T2, and T3, because both listed statuses qualify
  4. T2 and T4, because both tickets have high priority
Explanation: When a SQL WHERE clause mixes AND and OR, operator precedence determines how the conditions group — and this is one of the most common traps in SQL filtering. Just like in algebra, AND binds more tightly than OR, so it's evaluated first. That means WHERE status = 'closed' OR status = 'resolved' AND priority = 'high' is parsed as WHERE status = 'closed' OR (status = 'resolved' AND priority = 'high'). Walking through the table: T1 matches status = 'closed' ✓; T2 matches status = 'resolved' AND priority = 'high' ✓; T3 has status = 'resolved' but priority = 'low', so it fails the AND condition ✗; T4 is 'open', so neither branch matches ✗. That leaves T1 and T2 deleted — answer B is correct. Answer A is wrong because the priority filter does not apply to the 'closed' branch — T1 is deleted solely because its status is 'closed', regardless of priority. Answer C would only be correct if the query were written as WHERE (status = 'closed' OR status = 'resolved') AND priority = 'high', which adds explicit parentheses that change the logic entirely. Answer D has no basis in the query — priority = 'high' alone is never a standalone condition here, so T4 ('open', 'high') is never touched. The study tip to remember: whenever you see AND and OR in the same WHERE clause without parentheses, mentally add them yourself by wrapping each AND condition first. When in doubt, use explicit parentheses in real queries to avoid exactly this ambiguity.

Question 7

The accounts table contains these rows: account 101 has status 'active'; account 102 has status 'inactive'; account 103 has status NULL; and account 104 has status 'suspended'.

Which accounts are deleted by DELETE FROM accounts WHERE status <> 'active';?

  1. Accounts 102 and 104 only (correct answer)
  2. Accounts 102, 103, and 104
  3. Accounts 101 and 103 only
  4. Accounts 101, 102, and 104
Explanation: Whenever you see a DELETE or WHERE clause involving comparisons, your first instinct should be to think about how SQL handles NULL values — because NULL breaks the rules of normal logic. In SQL, NULL means "unknown," and any comparison involving NULL — including <>, =, >, etc. — does not return TRUE or FALSE. It returns NULL (unknown). Since WHERE clauses only delete rows where the condition evaluates to TRUE, any row with a NULL value in the compared column is quietly left alone. So when DELETE FROM accounts WHERE status <> 'active' runs, SQL evaluates each row: account 101 has 'active', so 'active' <> 'active' is FALSE — it's kept. Account 102 has 'inactive', so 'inactive' <> 'active' is TRUE — deleted. Account 104 has 'suspended', so 'suspended' <> 'active' is TRUE — deleted. Account 103 has NULL, so NULL <> 'active' evaluates to NULL, not TRUE — it is not deleted. That makes A the correct answer: only accounts 102 and 104 are removed. B is the classic trap here — it assumes NULL means "not active," so it should be deleted. That's the most common misconception. C incorrectly deletes account 101 (which matches the filter) and keeps 102 and 104. D would be correct if the intent were to delete everything except account 103, but that's not what the logic produces. The key study tip: NULL is not equal to anything, and it is not unequal to anything. To filter NULL values explicitly, you must use IS NULL or IS NOT NULL.

Question 8

The orders table contains order IDs 10, 20, and 30. The shipments table contains two rows for order 10 with status 'sent', one 'pending' row and one 'sent' row for order 20, and one 'pending' row for order 30.

How many order rows are deleted by DELETE FROM orders WHERE EXISTS (SELECT 1 FROM shipments WHERE shipments.order_id = orders.order_id AND shipments.status = 'sent');?

  1. One row, because only order 10 has multiple sent shipments
  2. Two rows, because orders 10 and 20 each have a sent shipment (correct answer)
  3. Three rows, because the subquery finds three sent shipment rows
  4. No rows, because the subquery returns shipment rows rather than order rows
Explanation: When you see EXISTS in a WHERE clause, remember that it's a true/false test per row in the outer table — it doesn't count or return shipment rows; it simply asks "does at least one matching row exist?" Here, the database evaluates each order in the orders table one at a time. For order 10, the subquery finds two 'sent' shipments — EXISTS sees at least one, returns TRUE, and the order is flagged for deletion. For order 20, the subquery finds one 'sent' shipment — EXISTS returns TRUE again. For order 30, there's only a 'pending' shipment — EXISTS returns FALSE, so that row is spared. The result: two rows deleted (orders 10 and 20), making B correct. Choice A is wrong because EXISTS doesn't care how many matching rows the subquery finds — it stops as soon as it finds one. The fact that order 10 has two sent shipments is irrelevant; it's treated identically to order 20, which has just one. Choice C reflects a common misconception: the subquery returns three 'sent' shipment rows across the whole table, but EXISTS doesn't count subquery rows — it operates order-by-order and collapses the result to TRUE/FALSE. Choice D is a logic error; EXISTS explicitly bridges the shipments and orders tables through the correlated condition shipments.order_id = orders.order_id, so it absolutely affects which order rows get deleted. A helpful rule of thumb: whenever you see EXISTS, mentally replace it with "does at least one matching row exist?" — never think of it as counting or returning the subquery's rows directly.

Question 9

The tasks table contains task IDs 1, 2, 3, and 4. For rows where action = 'archive', the task_audit subquery returns task IDs 2, 2, NULL, and 4.

Which tasks are deleted by DELETE FROM tasks WHERE task_id IN (SELECT task_id FROM task_audit WHERE action = 'archive');?

  1. Tasks 1, 2, 3, and 4, because the subquery is nonempty
  2. Task 2 twice and task 4 once, following the subquery rows
  3. No tasks, because the subquery includes a null task ID
  4. Tasks 2 and 4, with each qualifying task deleted once (correct answer)
Explanation: When you see DELETE ... WHERE task_id IN (subquery), think of IN as a set membership test — it checks whether each row's value belongs to a distinct set of values, not a list of repeated rows. The subquery returns (2, 2, NULL, 4). SQL's IN operator deduplies this internally: it evaluates whether each task_id in tasks matches any value in the result. Task 2 matches, task 4 matches, and tasks 1 and 3 do not. So tasks 2 and 4 are each deleted exactly once — making D correct. A is wrong because IN doesn't trigger a mass delete just because the subquery is nonempty. Each row in tasks is evaluated individually against the subquery values; only matching rows are affected. B reflects a misunderstanding of how IN works. The subquery returns rows to a set comparison, not a row-by-row join. Duplicates in the subquery result have no effect — SQL doesn't delete task 2 twice just because it appears twice in the subquery. C is a common and important trap. A NULL in the subquery does not block deletions — it simply never matches anything, because NULL IN (...) evaluates to UNKNOWN, not FALSE. The other non-null values still match normally, so tasks 2 and 4 are still deleted. As a study tip, remember: NULL values in a subquery used with IN are silently ignored for matching purposes — they don't cancel the entire operation. This behavior frequently appears on SQL exams to test whether you understand three-valued logic.

Question 10

The departments table contains department IDs D1, D2, and D3. The employees table has one employee assigned to D1 and one employee whose department_id is NULL.

Which departments are deleted by DELETE FROM departments WHERE NOT EXISTS (SELECT 1 FROM employees WHERE employees.department_id = departments.department_id);?

  1. D1 only, because it is the only department referenced by an employee
  2. D2 and D3 only, because neither has a matching employee (correct answer)
  3. D3 only, because the null department is associated with D2
  4. No departments, because the employees table is not empty
Explanation: When you see NOT EXISTS with a correlated subquery, your job is to evaluate the condition row by row for the outer table. For each department, SQL checks: "Does any employee row exist where employees.department_id = departments.department_id?" If no such row exists, NOT EXISTS returns true, and that department gets deleted. Walk through each department: D1 has one employee assigned to it, so the subquery finds a match — NOT EXISTS is false, and D1 is kept. D2 has no employee pointing to it, so the subquery returns nothing — NOT EXISTS is true, and D2 is deleted. Same logic applies to D3 — no employee references it, so it's deleted. That makes B the correct answer. Now for the distractors. A is backwards — D1 is the one department that survives because it has a matching employee. The logic of NOT EXISTS is the opposite of what A assumes. C introduces a false premise: the employee with a NULL department_id is not "associated" with D2 in any meaningful SQL sense. NULL = D2 evaluates to UNKNOWN, not true, so that null row never satisfies the subquery condition for any department. D reflects a common misconception that EXISTS/NOT EXISTS checks whether the entire table is empty — it doesn't. It checks for matching rows relative to each outer row individually. The key study tip: NULL values are a frequent trap with correlated subqueries. Remember that NULL = anything is always UNKNOWN, never true — so a NULL foreign key will never satisfy an equality join condition.