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

The created_at column stores timestamps, including times throughout each day. A maintenance statement must update every order created during January 2026, but no order created before or after that month.

Which WHERE clause most reliably identifies the required rows?

WHERE created_at BETWEEN TIMESTAMP '2026-01-01 00:00:00' AND TIMESTAMP '2026-01-31 00:00:00'
WHERE created_at >= TIMESTAMP '2026-01-01 00:00:00' AND created_at < TIMESTAMP '2026-02-01 00:00:00'
WHERE created_at > TIMESTAMP '2026-01-01 00:00:00' AND created_at <= TIMESTAMP '2026-02-01 00:00:00'
WHERE created_at >= TIMESTAMP '2026-01-01 00:00:00' AND created_at <= TIMESTAMP '2026-02-01 00:00:00'
← Back to quizzes

SQL Quiz

SQL Quiz: Update With Where

Practice Update 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 Update 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 created_at column stores timestamps, including times throughout each day. A maintenance statement must update every order created during January 2026, but no order created before or after that month.

Which WHERE clause most reliably identifies the required rows?

  1. WHERE created_at BETWEEN TIMESTAMP '2026-01-01 00:00:00' AND TIMESTAMP '2026-01-31 00:00:00'
  2. WHERE created_at >= TIMESTAMP '2026-01-01 00:00:00' AND created_at < TIMESTAMP '2026-02-01 00:00:00' (correct answer)
  3. WHERE created_at > TIMESTAMP '2026-01-01 00:00:00' AND created_at <= TIMESTAMP '2026-02-01 00:00:00'
  4. WHERE created_at >= TIMESTAMP '2026-01-01 00:00:00' AND created_at <= TIMESTAMP '2026-02-01 00:00:00'
Explanation: When filtering timestamp columns by a calendar month, the core challenge is boundary precision: timestamps aren't just dates — they include times down to fractions of a second, so your range boundaries must account for every possible moment within the month. The most reliable pattern for month-range filtering is the half-open interval: use >= on the start and < on the day after the last day. Answer B does exactly this — it captures every timestamp from 2026-01-01 00:00:00.000... up to (but not including) 2026-02-01 00:00:00. This cleanly includes January 31st at 23:59:59.999 without accidentally pulling in any February data. Answer A fails because BETWEEN is inclusive on both ends, and the upper bound is 2026-01-31 00:00:00 — this cuts off everything on January 31st after midnight, meaning most of that day's orders are excluded. A major data loss trap. Answer C uses > instead of >= on the lower bound, which silently excludes any order created at exactly 2026-01-01 00:00:00. It also uses <= on 2026-02-01 00:00:00, which would include orders created at precisely that timestamp — pulling in the very start of February. Answer D uses >= correctly on the left, but <= on 2026-02-01 00:00:00 again includes that exact February boundary moment, making it subtly wrong for strict month isolation. Study tip: Always prefer the half-open interval pattern (>= start AND < next_period_start) for date/time ranges — it's boundary-safe, handles any timestamp precision, and works consistently across all SQL databases.

Question 2

The accounts table contains account 1 with a balance of 40, account 2 with 50, account 3 with NULL, and account 4 with -5.

Which accounts are modified by this statement?

UPDATE accounts SET review_required = 1 WHERE balance + 10 >= 50;

  1. Accounts 1 and 2 only (correct answer)
  2. Accounts 1, 2, and 3 only
  3. Accounts 2 and 3 only
  4. Accounts 2 and 4 only
Explanation: When evaluating a WHERE clause in SQL, you need to mentally substitute each row's values into the condition and check whether it evaluates to TRUE. Only rows where the condition is TRUE get updated — rows where it's FALSE or NULL are left alone. Let's walk through each account with the condition balance + 10 >= 50:
  • Account 1 (balance = 40): 40+10=50≥5040 + 10 = 50 \geq 50 → TRUE ✓
  • Account 2 (balance = 50): 50+10=60≥5050 + 10 = 60 \geq 50 → TRUE ✓
  • Account 3 (balance = NULL): NULL+10=NULL\text{NULL} + 10 = \text{NULL}, and NULL >= 50 evaluates to NULL, not TRUE → skipped
  • Account 4 (balance = -5): −5+10=5≥50-5 + 10 = 5 \geq 50 → FALSE → skipped
So only accounts 1 and 2 are modified, making A the correct answer. B is wrong because it includes account 3. Students sometimes assume NULL behaves like zero, which would make NULL + 10 = 10, still less than 50 — but more importantly, any arithmetic on NULL produces NULL, and a NULL condition is never treated as TRUE in a WHERE clause. C is wrong on two counts: it excludes account 1 (which clearly satisfies the condition) and incorrectly includes account 3 for the NULL reason above. D is wrong because account 4 yields 5, which is far less than 50, and again incorrectly omits account 1. The key study tip: NULL is contagious — any expression involving NULL produces NULL, and SQL never updates rows where the WHERE condition is NULL.

Question 3

Product codes include 'AB_10', 'ABX10', 'AB_20', and 'XY_10'. The underscore in codes such as 'AB_10' is a literal character, not a wildcard.

Which statement updates exactly the products whose codes begin with the literal characters AB_?

  1. UPDATE products SET flagged = 1 WHERE product_code LIKE 'AB_%';
  2. UPDATE products SET flagged = 1 WHERE product_code LIKE 'AB!_%' ESCAPE '!'; (correct answer)
  3. UPDATE products SET flagged = 1 WHERE product_code LIKE '%AB!_%' ESCAPE '!';
  4. UPDATE products SET flagged = 1 WHERE product_code LIKE 'AB[_]%';
Explanation: Whenever you see a SQL LIKE pattern that needs to match a literal underscore (_) or percent sign (%), remember that these characters are wildcards by default — _ matches any single character, and % matches any sequence. To treat them as literals, you must escape them using an ESCAPE clause. Option B — LIKE 'AB!_%' ESCAPE '!' — is correct. The ESCAPE '!' declaration tells SQL that any wildcard character immediately preceded by ! should be treated as a literal. So !_ becomes a literal underscore, and the trailing % remains a wildcard matching anything after it. This pattern matches codes that start with the exact characters AB_, correctly flagging 'AB_10' and 'AB_20' while excluding 'ABX10' and 'XY_10'. Option A fails because 'AB_%' uses _ as an unescaped wildcard. It matches any character in that position, so 'ABX10' would also be flagged — that's the classic trap this question is testing. Option C uses the correct escape syntax but wraps the pattern in '%AB!_%', adding a leading % wildcard. This would match codes that contain AB_ anywhere, not just at the start — too broad. Option D uses bracket syntax ([_]) to escape the underscore, which works in SQL Server's T-SQL dialect but is not standard SQL. On an exam testing standard SQL behavior, you should not rely on vendor-specific extensions unless explicitly stated. Your takeaway: always pair escaped wildcards with an explicit ESCAPE clause using the standard syntax, and watch out for unescaped _ characters hiding in LIKE patterns.

Question 4

Before the statement runs, player 1 has 95 points, player 2 has 100 points, and player 3 has 105 points.

What are the players' point totals after this single statement completes?

UPDATE players SET points = points + 10 WHERE points < 100;

  1. Player 1: 115; player 2: 100; player 3: 105
  2. Player 1: 105; player 2: 110; player 3: 105
  3. Player 1: 105; player 2: 100; player 3: 105 (correct answer)
  4. Player 1: 105; player 2: 110; player 3: 115
Explanation: When you see an UPDATE statement with a WHERE clause, your first job is to identify which rows get modified before worrying about the new values. The WHERE clause acts as a filter — only rows satisfying the condition are touched; all others stay exactly as they are. Here, the condition is points < 100. Before the update, player 1 has 95 points, player 2 has 100 points, and player 3 has 105 points. Only player 1 satisfies points < 100 (since 95 < 100 is true, but 100 < 100 and 105 < 100 are both false). So only player 1 gets the +10 applied: 95 + 10 = 105. Players 2 and 3 are untouched at 100 and 105, respectively. That makes C the correct answer. Choice A is wrong because it gives player 1 a total of 115, as if +10 were applied twice — a common mistake when students misread the logic or imagine the update running in a loop. Choice B incorrectly updates player 2, whose 100 points do not satisfy the strict less-than condition (<, not <=). Choice D is the most tempting distractor — it updates both player 2 and player 3, which would only be correct if the condition were points <= 105 or similar. A quick study tip: pay close attention to strict (<, >) versus inclusive (<=, >=) comparisons in WHERE clauses — exams frequently place a boundary value (like 100 here) right at the threshold to test whether you catch the distinction.

Question 5

The inventory table has rows whose category_code values are 'A', 'B', 'C', and NULL. The excluded_categories table contains two values: 'B' and NULL.

What is the result of the following statement under standard SQL null semantics?

UPDATE inventory SET available = 0 WHERE category_code NOT IN (SELECT category_code FROM excluded_categories);

  1. Only the rows with category codes 'A' and 'C' are updated
  2. Only the row with category code 'B' is updated
  3. The rows with 'A', 'C', and NULL are updated
  4. No inventory rows are updated (correct answer)
Explanation: Whenever you see NOT IN with a subquery, your first instinct should be to check whether that subquery can return NULL. This is one of SQL's most notorious traps. Here's the core rule: under standard SQL null semantics, NOT IN uses three-valued logic. When SQL evaluates value NOT IN (list), it checks value <> each_element for every element. If any comparison yields UNKNOWN (which happens whenever NULL is involved), the entire NOT IN expression evaluates to UNKNOWN — never TRUE. A WHERE clause only processes rows where the condition is TRUE, so rows returning UNKNOWN are silently excluded from the update. In this problem, excluded_categories contains 'B' and NULL. For every row in inventory, SQL must check category_code <> NULL, which always yields UNKNOWN. Because of this, no row can ever satisfy the NOT IN condition, and D is correct — no rows are updated at all. Choice A is the intuitive answer — you might expect 'A' and 'C' to be updated since they're not literally 'B' — but this ignores the NULL in the subquery poisoning all comparisons. Choice C makes a similar mistake, additionally assuming NULL rows would match themselves, which they don't under null semantics. Choice B flips the logic entirely, confusing NOT IN with IN. The practical takeaway: a single NULL in a NOT IN subquery kills all results. On any SQL exam or in real development, always filter out NULLs from subqueries used with NOT IN, or rewrite using NOT EXISTS instead, which handles NULLs more predictably.

Question 6

A customer must be marked inactive only when that customer has no order whose status is 'open'. Customers with no orders at all must also be marked inactive.

Which statement performs the required update?

  1. UPDATE customers SET status = 'inactive' WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id AND orders.status = 'open'); (correct answer)
  2. UPDATE customers SET status = 'inactive' WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.status = 'open');
  3. UPDATE customers SET status = 'inactive' WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id AND orders.status = 'open');
  4. UPDATE customers SET status = 'inactive' WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id AND orders.status <> 'open');
Explanation: When you see a question involving correlated subqueries in SQL, ask yourself two things: Is the subquery linked to the outer query row by row? and Does the condition filter for the right rows? The goal here is to mark a customer inactive if they have zero open orders — including customers with no orders at all. The tool for this is NOT EXISTS, which returns true when the subquery produces no rows. The critical detail is that the subquery must be correlated — tied to the current customer being evaluated — otherwise every customer gets evaluated against the same global result. Answer A is correct because it uses NOT EXISTS with a correlated subquery that checks for open orders belonging specifically to that customer (orders.customer_id = customers.customer_id AND orders.status = 'open'). If no such row exists for a given customer — whether because they have no orders or no open orders — that customer gets marked inactive. This precisely matches the requirement. Answer B looks similar but is fatally flawed: its subquery has no join condition linking back to customers.customer_id. This means the subquery checks whether any open order exists in the entire table. If even one open order exists anywhere, NOT EXISTS returns false for every customer, and nobody gets updated. Answer C uses EXISTS instead of NOT EXISTS, so it marks customers with open orders as inactive — the exact opposite of the requirement. Answer D uses status <> 'open', targeting customers who have orders that are not open, rather than customers who lack open orders entirely — a subtle but critical logical error. Study tip: Always verify that your correlated subquery references the outer table's key. Missing that join condition turns a row-by-row filter into an unintended global check.

Question 7

The employees table contains employee 1 with a salary of 40, employee 2 with 60, and employee 3 with NULL. The pay_benchmarks table contains target salaries of 50, 70, and NULL.

Which employees are modified by this statement?

UPDATE employees SET salary = salary + 10 WHERE salary < (SELECT AVG(target_salary) FROM pay_benchmarks);

  1. Employees 1 and 3 only
  2. Employees 1 and 2 only
  3. Employee 1 only (correct answer)
  4. All three employees
Explanation: This question tests two critical SQL behaviors: how AVG() handles NULL values, and how comparison operators interact with NULL. Start by evaluating the subquery. AVG(target_salary) runs against pay_benchmarks, which contains 50, 70, and NULL. SQL's AVG() ignores NULL values entirely, so it computes (50+70)/2=60(50 + 70) / 2 = 60. The WHERE clause becomes salary < 60. Now check each employee against salary < 60. Employee 1 has salary 40 — that's less than 60, so they're updated. Employee 2 has salary 60 — that fails < 60 (not strictly less), so they're skipped. Employee 3 has NULL salary — and here's the crucial part: any comparison involving NULL yields UNKNOWN, not TRUE or FALSE. Since NULL < 60 evaluates to UNKNOWN, the WHERE clause doesn't match, and Employee 3 is not updated. Only Employee 1 is modified, making C correct. Answer A is wrong because it includes Employee 3, which requires ignoring how NULL comparisons work — NULL never satisfies a < condition. Answer B is wrong because it includes Employee 2, whose salary of 60 does not satisfy the strict < 60 condition. Answer D is wrong for both reasons combined. Study tip: Memorize these two NULL rules as a pair — aggregate functions like AVG(), SUM(), and COUNT(col) silently skip NULLs in their calculations, but comparison operators (<, >, =) against NULL always return UNKNOWN, effectively excluding that row from WHERE clause matches.

Question 8

The discounts table contains four rows: discount 1 has percent_off equal to NULL, discount 2 has 0, discount 3 has 5, and discount 4 has -5.

Which rows are modified by this statement?

UPDATE discounts SET reviewed = 1 WHERE percent_off <> 0;

  1. Discounts 1, 3, and 4 only
  2. Discounts 3 and 4 only (correct answer)
  3. Discounts 2, 3, and 4 only
  4. Discount 3 only
Explanation: When working with SQL WHERE clauses, the most critical concept to master is how NULL values behave in comparisons — and it's almost always a trap on exams. In SQL, NULL represents an unknown value. Any comparison involving NULL — whether =, <>, >, or < — evaluates to NULL (not TRUE, not FALSE). Since the WHERE clause only updates rows where the condition evaluates to TRUE, rows with NULL are silently excluded. So when the statement runs WHERE percent_off <> 0, discount 1 (NULL) produces an unknown result and is skipped. Discount 2 has percent_off = 0, which makes 0 <> 0 FALSE — also skipped. Discount 3 has 5, so 5 <> 0 is TRUE — updated. Discount 4 has -5, so -5 <> 0 is TRUE — updated. Only discounts 3 and 4 are modified, confirming B is correct. Choice A is wrong because it includes discount 1 (NULL). A common misconception is that NULL "isn't equal to 0," so it should pass a <> 0 check — but that logic doesn't apply. NULL comparisons always yield unknown, never TRUE. Choice C is wrong because it includes discount 2, whose value is exactly 0, which explicitly fails the <> 0 condition. Choice D is wrong because it excludes discount 4 (-5), which clearly satisfies <> 0. As a study tip, remember this rule: NULL is never equal to anything, and never unequal to anything — always use IS NULL or IS NOT NULL to test for it explicitly.

Question 9

Customers 1, 2, and 3 each initially have reward_level equal to 1. The orders table contains two paid orders for customer 1, one paid order for customer 2, and no paid orders for customer 3.

What are the reward levels after this statement completes?

UPDATE customers SET reward_level = reward_level + 1 WHERE customer_id IN (SELECT customer_id FROM orders WHERE payment_status = 'paid');

  1. Customer 1: 3; customer 2: 2; customer 3: 1
  2. Customer 1: 3; customer 2: 2; customer 3: 2
  3. Customer 1: 2; customer 2: 2; customer 3: 2
  4. Customer 1: 2; customer 2: 2; customer 3: 1 (correct answer)
Explanation: When you see an UPDATE with a subquery in the WHERE clause, your job is to first figure out which rows get updated, then figure out how many times each row is updated — which is always exactly once per UPDATE statement. The subquery SELECT customer_id FROM orders WHERE payment_status = 'paid' returns a list of customer IDs with paid orders. Customer 1 has two paid orders, so their ID appears twice in that result — but IN doesn't care about duplicates. It simply checks whether a value exists in the list. Customer 1's ID is in the list, so their row is updated once. Customer 2 has one paid order, so they're updated once. Customer 3 has no paid orders, so their ID never appears, and they're skipped entirely. Starting from reward_level = 1, customer 1 becomes 2, customer 2 becomes 2, and customer 3 stays at 1. That's answer D. Answer A is wrong because it sets customer 1 to 3, implying the update ran once per matching row in the orders table — but SQL updates each customer row only once regardless of how many order rows matched. Answer B compounds this error by also incorrectly updating customer 3, who has no paid orders at all. Answer C correctly leaves customer 3 at 1 but still mistakenly sets customer 1 to only 2 while simultaneously getting it right, making it a half-trap — it would be correct if customer 1 had only one paid order. The key takeaway: IN deduplicates results automatically. An UPDATE always touches each matching row exactly once, no matter how many subquery rows reference it.

Question 10

The employees rows are: employee 1—Sales, active; employee 2—Sales, inactive; employee 3—Support, active; employee 4—Support, inactive; employee 5—Engineering, active. The active column contains either 1 or 0.

Which employees are modified by this statement?

UPDATE employees SET bonus = 500 WHERE department = 'Sales' OR department = 'Support' AND active = 1;

  1. Employees 1, 2, and 3 only (correct answer)
  2. Employees 1 and 3 only
  3. Employees 1, 2, 3, and 4 only
  4. Employees 1, 3, and 5 only
Explanation: When you see a SQL statement mixing OR and AND in a WHERE clause, your first instinct should be to think about operator precedence. In SQL, AND always binds more tightly than OR — meaning it evaluates first, just like multiplication before addition in arithmetic. Failing to account for this is one of the most common SQL traps. So this clause: WHERE department = 'Sales' OR department = 'Support' AND active = 1 is actually evaluated as: WHERE department = 'Sales' OR (department = 'Support' AND active = 1) That means the update applies to: any employee in Sales (regardless of active status) OR any active Support employee. Checking each row — employee 1 is Sales/active ✓, employee 2 is Sales/inactive ✓ (Sales alone qualifies), employee 3 is Support/active ✓, employee 4 is Support/inactive ✗ (fails the AND condition), employee 5 is Engineering ✗. That gives us employees 1, 2, and 3, confirming A is correct. Choice B is wrong because it assumes AND applies across both departments, effectively reading the clause as (department = 'Sales' OR department = 'Support') AND active = 1, which would exclude inactive employee 2. Choice C incorrectly includes employee 4, as if active = 1 doesn't apply to Support at all. Choice D mistakenly includes employee 5 (Engineering) while misreading the logic entirely. Study tip: Whenever you see OR and AND together without parentheses, mentally insert parentheses around the AND condition first — that's always how SQL evaluates it.