SQL Quiz: Case When For Categories
9 questions · exam conditions
0:00
Case When For CategoriesQuestion 1 of 9

An online store assigns each order exactly one review category. An order is Priority if its amount is at least 1000, Delayed if it is not Priority and its shipping delay exceeds 7 days, and Standard otherwise.

Which expression correctly creates the review_category column while preserving the required precedence?

CASE WHEN shipping_delay > 7 THEN 'Delayed' WHEN amount >= 1000 THEN 'Priority' ELSE 'Standard' END AS review_category
CASE WHEN amount >= 1000 THEN 'Priority' WHEN shipping_delay > 7 THEN 'Delayed' ELSE 'Standard' END AS review_category
CASE WHEN amount >= 1000 AND shipping_delay > 7 THEN 'Priority' WHEN shipping_delay > 7 THEN 'Delayed' ELSE 'Standard' END AS review_category
CASE WHEN amount >= 1000 THEN 'Priority' WHEN shipping_delay <= 7 THEN 'Standard' ELSE 'Delayed' END AS review_category
← Back to quizzes

SQL Quiz

SQL Quiz: Case When For Categories

Practice Case When For Categories 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 Case When For Categories, 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

An online store assigns each order exactly one review category. An order is Priority if its amount is at least 1000, Delayed if it is not Priority and its shipping delay exceeds 7 days, and Standard otherwise.

Which expression correctly creates the review_category column while preserving the required precedence?

  1. CASE WHEN shipping_delay > 7 THEN 'Delayed' WHEN amount >= 1000 THEN 'Priority' ELSE 'Standard' END AS review_category
  2. CASE WHEN amount >= 1000 THEN 'Priority' WHEN shipping_delay > 7 THEN 'Delayed' ELSE 'Standard' END AS review_category (correct answer)
  3. CASE WHEN amount >= 1000 AND shipping_delay > 7 THEN 'Priority' WHEN shipping_delay > 7 THEN 'Delayed' ELSE 'Standard' END AS review_category
  4. CASE WHEN amount >= 1000 THEN 'Priority' WHEN shipping_delay <= 7 THEN 'Standard' ELSE 'Delayed' END AS review_category
Explanation: When writing a CASE expression with business rules that have a priority hierarchy, the order of your WHEN clauses is everything — SQL evaluates them top to bottom and stops at the first match. This means the most restrictive or highest-priority condition must come first, or it risks being swallowed by a broader condition below it. The business rules state: Priority wins over everything (amount ≥ 1000), then Delayed applies only if not Priority (shipping_delay > 7), then Standard for the rest. B mirrors this exactly — it checks amount >= 1000 first, so any high-value order is immediately classified as Priority. Only orders that fail that check can fall into the Delayed bucket, naturally encoding the "not Priority" requirement without extra logic. A is wrong because it checks shipping_delay > 7 before amount >= 1000. An order with amount = 1500 and delay = 10 would be incorrectly labeled Delayed instead of Priority — the precedence rule is violated. C adds an unnecessary compound condition (amount >= 1000 AND shipping_delay > 7) for Priority. This means a high-value order with a short delay would skip Priority entirely and fall to Standard — incorrectly excluding orders that should be Priority. D swaps the logic for Standard and Delayed. After the Priority check, it assigns Standard to orders with shipping_delay <= 7, which is fine so far, but the ELSE clause catches everything remaining — including orders that are both high-value and delayed — potentially mislabeling edge cases. Study tip: When building a CASE expression from tiered rules, always write your conditions in the same order the rules define precedence — the most dominant category goes first.

Question 2

Employees are classified as Field Lead when they work in the Field department and are either a manager or have at least 10 years of service. All other employees are classified as Other. The columns are department, is_manager, and years_service.

Which expression implements the classification without applying the service rule to employees outside the Field department?

  1. CASE WHEN department = 'Field' AND is_manager = 1 OR years_service >= 10 THEN 'Field Lead' ELSE 'Other' END
  2. CASE WHEN department = 'Field' OR (is_manager = 1 AND years_service >= 10) THEN 'Field Lead' ELSE 'Other' END
  3. CASE WHEN (department = 'Field' AND is_manager = 1) AND years_service >= 10 THEN 'Field Lead' ELSE 'Other' END
  4. CASE WHEN department = 'Field' AND (is_manager = 1 OR years_service >= 10) THEN 'Field Lead' ELSE 'Other' END (correct answer)
Explanation: When combining multiple conditions in SQL, operator precedence works just like in algebra — AND binds more tightly than OR, so the order of your parentheses dramatically changes what your query actually checks. The business rule here has a specific structure: the employee must be in Field, and then within that group, must satisfy at least one of two sub-conditions (manager OR tenure). Translating layered "and then either/or" logic correctly is the core skill being tested. D is correct because department = 'Field' AND (is_manager = 1 OR years_service >= 10) mirrors the rule exactly. The outer AND ensures only Field employees qualify at all, and the parenthesized OR then checks either condition independently — manager status or years of service. Both sub-conditions are scoped entirely inside the Field requirement. A fails due to missing parentheses around the first two conditions. SQL evaluates it as (department = 'Field' AND is_manager = 1) OR (years_service >= 10), meaning any employee — in any department — with 10+ years gets classified as Field Lead. That violates the "Field department only" constraint. B has the same scoping problem in reverse: department = 'Field' OR (is_manager = 1 AND years_service >= 10) classifies all Field employees as Field Lead regardless of manager status or tenure, and also catches managers with tenure outside the Field department. C requires an employee to satisfy all three conditions simultaneously — Field department, manager, and 10+ years. This is too restrictive; the rule says manager or tenure, not both. As a rule of thumb: whenever you see "and either/or" in a business rule, immediately reach for AND (... OR ...) and let the parentheses enforce the grouping you intend.

Question 3

A report classifies invoice amounts as Small when the amount is less than 100, Medium when it is at least 100 but less than 500, Large when it is at least 500 but less than 1000, and Very Large when it is at least 1000. The amount is guaranteed to be non-null.

Which expression assigns every boundary value to the correct category without overlapping ranges?

  1. CASE WHEN amount <= 100 THEN 'Small' WHEN amount <= 500 THEN 'Medium' WHEN amount <= 1000 THEN 'Large' ELSE 'Very Large' END
  2. CASE WHEN amount < 100 THEN 'Small' WHEN amount BETWEEN 100 AND 500 THEN 'Medium' WHEN amount BETWEEN 500 AND 1000 THEN 'Large' ELSE 'Very Large' END
  3. CASE WHEN amount < 100 THEN 'Small' WHEN amount < 500 THEN 'Medium' WHEN amount < 1000 THEN 'Large' ELSE 'Very Large' END (correct answer)
  4. CASE WHEN amount < 100 THEN 'Small' WHEN amount > 100 AND amount < 500 THEN 'Medium' WHEN amount > 500 AND amount < 1000 THEN 'Large' ELSE 'Very Large' END
Explanation: When writing range-based CASE expressions, the key insight is that SQL evaluates each WHEN clause in order and stops at the first match. This means you can write "cascading" comparisons — each clause only needs to handle values that already failed every previous check. Option C works perfectly because of this cascade logic. If amount < 100 is false, you already know amount >= 100. The next check, amount < 500, therefore implicitly covers 100 ≤ amount < 500. Similarly, amount < 1000 covers 500 ≤ amount < 1000. Every boundary value (100, 500, 1000) lands in exactly the right bucket with no overlap and no gap. Option A uses <= instead of < at each boundary, so amount = 100 would be caught by the first WHEN and labeled Small — but the spec says 100 should be Medium. The same off-by-one error pushes 500 into Medium instead of Large, and 1000 into Large instead of Very Large. Option B uses BETWEEN, which is inclusive on both ends. This creates overlap: amount = 500 matches both BETWEEN 100 AND 500 (Medium) and BETWEEN 500 AND 1000 (Large). SQL takes the first match, putting 500 in Medium — incorrect per the spec. Option D explicitly excludes exact boundary values using strict inequality (> 100, > 500). An amount of exactly 100 or exactly 500 falls through those conditions and lands in ELSE 'Very Large' — badly wrong. Strategy tip: When ranges share boundary values, use cascading < comparisons in a CASE expression rather than BETWEEN or <=, and let SQL's top-down evaluation handle the implicit lower bounds for you.

Question 4

A delivery report uses nullable timestamp columns. A row must be labeled In Transit when delivered_at is NULL. A delivered row is Late when delivered_at is later than promised_at, and On Time otherwise. promised_at is never null.

Which expression creates the required delivery category?

  1. CASE WHEN delivered_at > promised_at THEN 'Late' WHEN delivered_at IS NULL THEN 'In Transit' ELSE 'On Time' END
  2. CASE WHEN delivered_at IS NULL THEN 'In Transit' WHEN delivered_at >= promised_at THEN 'Late' ELSE 'On Time' END
  3. CASE WHEN delivered_at IS NULL THEN 'In Transit' WHEN delivered_at > promised_at THEN 'Late' ELSE 'On Time' END (correct answer)
  4. CASE WHEN delivered_at IS NOT NULL THEN 'On Time' WHEN delivered_at > promised_at THEN 'Late' ELSE 'In Transit' END
Explanation: When writing a CASE expression, SQL evaluates each WHEN clause in order and stops at the first match. This means the sequence of your conditions is critical — especially when NULL values are involved, because any comparison with NULL (like NULL > promised_at) evaluates to UNKNOWN, not TRUE or FALSE, so that branch will never fire. The correct answer is C. It checks IS NULL first, correctly routing undelivered rows to 'In Transit' before any timestamp comparison runs. Then it checks delivered_at > promised_at (strictly greater than) to catch late deliveries, and falls through to 'On Time' for everything else — exactly matching the business rules described. A is wrong because it tests delivered_at > promised_at before checking for NULL. When delivered_at is NULL, that comparison returns UNKNOWN, so SQL skips to the next WHEN and correctly hits IS NULL — but by luck, not design. More critically, a row delivered exactly on the promised timestamp would fall to 'On Time', which is correct, but the ordering is fragile and signals a misunderstanding of NULL behavior. B uses >= instead of > for the late check, which incorrectly labels an on-time delivery (one where delivered_at = promised_at) as 'Late'. The business rule says late means later than, not equal to or later than. D immediately labels any non-NULL row as 'On Time', meaning late deliveries are never caught — the 'Late' branch can never be reached. Study tip: Always handle NULL checks first in a CASE expression, and read comparison operators carefully — > and >= are a classic exam trap.

Question 5

A company assigns a customer tier from completed orders only. The tier is Gold when completed-order spending is at least 5000, Silver when it is at least 2000, and Bronze otherwise. A query has already computed the alias completed_spend in a subquery, including a value of 0 for customers with no completed orders.

Which expression correctly derives the tier from completed_spend?

  1. CASE WHEN completed_spend >= 2000 THEN 'Silver' WHEN completed_spend >= 5000 THEN 'Gold' ELSE 'Bronze' END AS tier
  2. CASE WHEN completed_spend >= 5000 THEN 'Gold' WHEN completed_spend >= 2000 THEN 'Silver' ELSE 'Bronze' END AS tier (correct answer)
  3. CASE WHEN completed_spend > 5000 THEN 'Gold' WHEN completed_spend > 2000 THEN 'Silver' ELSE 'Bronze' END AS tier
  4. CASE WHEN completed_spend BETWEEN 2000 AND 5000 THEN 'Silver' WHEN completed_spend >= 5000 THEN 'Gold' ELSE 'Bronze' END AS tier
Explanation: When writing a CASE expression with overlapping numeric ranges, order matters enormously. SQL evaluates each WHEN clause from top to bottom and returns the result of the first condition that is true. This means you must place your most restrictive condition first. B is correct because it checks >= 5000 before >= 2000. A customer spending exactly $6,000 triggers >= 5000 immediately and gets 'Gold'. If that check were placed second, the >= 2000 condition would fire first and incorrectly return 'Silver'. A is the classic ordering trap. By checking >= 2000 first, every customer who qualifies for Gold also satisfies >= 2000, so they'd all be labeled 'Silver'. Gold would never be assigned. C uses strict greater-than (>) instead of greater-than-or-equal-to (>=). This means a customer spending exactly $5,000 fails the > 5000 check and falls into the > 2000 branch, receiving 'Silver' instead of 'Gold'. The boundary value is mishandled. D attempts to use BETWEEN 2000 AND 5000 for Silver, but BETWEEN is inclusive on both ends — it captures the value $5,000 in the Silver branch. Even though the Gold check appears afterward, SQL already matched $5,000 as Silver and stops evaluating. Gold would again never be awarded at the boundary. Study tip: With tiered CASE expressions, always write conditions from most restrictive to least restrictive (highest threshold first). If ranges overlap, wrong ordering silently produces bad data — no error, just incorrect results.

Question 6

A customer must be categorized as Unknown when preferred_flag is NULL, regardless of spending. Otherwise, the customer is Preferred when preferred_flag = 1, High Spend when annual spending is at least 1000, and Other in all remaining cases.

Which CASE expression implements all of these rules?

  1. CASE WHEN preferred_flag = NULL THEN 'Unknown' WHEN preferred_flag = 1 THEN 'Preferred' WHEN annual_spend >= 1000 THEN 'High Spend' ELSE 'Other' END
  2. CASE WHEN preferred_flag = 1 THEN 'Preferred' WHEN annual_spend >= 1000 THEN 'High Spend' WHEN preferred_flag IS NULL THEN 'Unknown' ELSE 'Other' END
  3. CASE WHEN preferred_flag IS NULL THEN 'Unknown' WHEN preferred_flag = 1 THEN 'Preferred' WHEN annual_spend >= 1000 THEN 'High Spend' ELSE 'Other' END (correct answer)
  4. CASE preferred_flag WHEN NULL THEN 'Unknown' WHEN 1 THEN 'Preferred' WHEN annual_spend >= 1000 THEN 'High Spend' ELSE 'Other' END
Explanation: When working with CASE expressions in SQL, two things matter critically: how you check for NULL and the order in which conditions are evaluated. SQL's CASE returns the result for the first matching WHEN clause, so ordering and syntax both shape the outcome. The correct answer is C because it handles both issues properly. It uses IS NULL to detect a null preferred_flag, and it places that check first — before any other condition — guaranteeing that null-flagged customers always land in Unknown, regardless of their annual_spend. The remaining conditions follow in logical order: flag equals 1, then high spending, then the catch-all ELSE. A is a classic NULL trap. Writing preferred_flag = NULL never evaluates to TRUE in SQL — comparisons with NULL using = always return UNKNOWN, not TRUE. This means no customer would ever be categorized as Unknown, silently breaking the business rule. B gets the NULL syntax right (IS NULL) but places the check after the preferred_flag = 1 and annual_spend >= 1000 conditions. Since a customer with a NULL flag could also have high spending, they might match the annual_spend >= 1000 branch first and be misclassified as High Spend instead of Unknown. D uses the simple CASE form (CASE preferred_flag WHEN ...), which implicitly uses equality (=) for comparisons. This means WHEN NULL still fails for the same reason as A, and WHEN annual_spend >= 1000 is a boolean expression that doesn't belong in this form at all. Study tip: Whenever a rule says "regardless of other conditions," that branch must come first in your CASE, and always use IS NULL — never = NULL.

Question 7

Inventory is categorized using on_hand and reorder_level. The category is No Target when reorder_level is 0, Critical when the ratio of on_hand to reorder_level is below 0.25, Low when the ratio is below 0.75, and Adequate otherwise. Both columns contain nonnegative integers.

Which expression handles zero targets, threshold boundaries, and fractional division correctly?

  1. CASE WHEN reorder_level = 0 THEN 'No Target' WHEN 1.0 * on_hand / reorder_level < 0.25 THEN 'Critical' WHEN 1.0 * on_hand / reorder_level < 0.75 THEN 'Low' ELSE 'Adequate' END (correct answer)
  2. CASE WHEN 1.0 * on_hand / reorder_level < 0.25 THEN 'Critical' WHEN 1.0 * on_hand / reorder_level < 0.75 THEN 'Low' WHEN reorder_level = 0 THEN 'No Target' ELSE 'Adequate' END
  3. CASE WHEN reorder_level = 0 THEN 'No Target' WHEN on_hand / reorder_level <= 0.25 THEN 'Critical' WHEN on_hand / reorder_level <= 0.75 THEN 'Low' ELSE 'Adequate' END
  4. CASE WHEN reorder_level = 0 THEN 'No Target' WHEN 1.0 * on_hand / reorder_level < 0.75 THEN 'Low' WHEN 1.0 * on_hand / reorder_level < 0.25 THEN 'Critical' ELSE 'Adequate' END
Explanation: When writing a CASE expression for tiered conditions like this, you need to think about three things simultaneously: order of evaluation, division by zero, and integer vs. floating-point division. Answer A handles all three correctly. It checks reorder_level = 0 first, so the division never executes when the denominator is zero. It then multiplies by 1.0 before dividing, forcing floating-point arithmetic so that, for example, on_hand = 1, reorder_level = 5 yields 0.2 rather than 0. Finally, it applies the thresholds in ascending order (< 0.25 before < 0.75), which is the only order that correctly isolates each tier. B is wrong because it attempts division before checking whether reorder_level = 0. Since CASE evaluates conditions top to bottom, a zero reorder_level reaches the division in the first WHEN clause, causing a divide-by-zero error before it ever reaches the 'No Target' check. C guards against division by zero correctly, but uses <= instead of < for both thresholds. This misclassifies boundary values — a ratio of exactly 0.25 should be Low (it is not below 0.25), but C labels it Critical. D places the < 0.75 check before < 0.25. Because every ratio below 0.25 is also below 0.75, all Critical items get caught by the first condition and labeled Low instead — the Critical branch can never be reached. Study tip: In multi-tier CASE expressions, always guard against invalid inputs first, then order thresholds from most restrictive to least — and never forget that integer division silently truncates in SQL.

Question 8

A ticket has priority = 'High', status = 'Closed', and escalated = 1. The query contains the following expression:

CASE WHEN status = 'Open' AND priority = 'High' OR escalated = 1 THEN 'Urgent' WHEN status = 'Closed' THEN 'Resolved' ELSE 'Normal' END

What category does this expression return, and why?

  1. Urgent, because AND is evaluated before OR, so escalated = 1 makes the first condition true (correct answer)
  2. Resolved, because status = 'Closed' prevents the first WHEN condition from being considered true
  3. Resolved, because the second WHEN is more specific and therefore overrides the first matching condition
  4. Normal, because the priority and status tests are inconsistent and the final ELSE is therefore used
Explanation: When you see a CASE expression combined with AND and OR, operator precedence is the concept being tested — and it's a classic SQL trap. In SQL (and most programming languages), AND binds more tightly than OR. This means the first WHEN condition is parsed as: (status = 'Open' AND priority = 'High') OR (escalated = 1) With our ticket — status = 'Closed', priority = 'High', escalated = 1 — the left side of the OR evaluates to false (status is not 'Open'), but the right side escalated = 1 evaluates to true. Because OR only needs one side to be true, the entire first WHEN condition is true, and SQL returns 'Urgent'. That makes A correct: AND's higher precedence groups the first two conditions together, leaving escalated = 1 as a standalone OR branch that fires independently. B is wrong because it assumes status = 'Closed' neutralizes the first WHEN. In a CASE expression, SQL evaluates each WHEN in order and stops at the first true condition — status = 'Closed' is never even reached here. C is wrong because SQL has no concept of "specificity" in CASE expressions. It doesn't find the "best" match; it finds the first match. D is wrong because there's no ambiguity that forces the ELSE — CASE expressions don't fail due to inconsistent conditions; they simply evaluate top-to-bottom. Study tip: Whenever you see AND and OR mixed in a condition, mentally add parentheses around each AND group first. That habit will catch precedence traps before they cost you.

Question 9

A source column risk_code can contain 'H', 'M', 'L', or NULL. A report must display High, Medium, or Low for the three codes and Not Assessed for NULL.

Which expression correctly creates the display category?

  1. CASE risk_code WHEN 'H' THEN 'High' WHEN 'M' THEN 'Medium' WHEN 'L' THEN 'Low' WHEN NULL THEN 'Not Assessed' END
  2. CASE WHEN risk_code IN ('H','M','L') THEN risk_code WHEN risk_code IS NULL THEN 'Not Assessed' ELSE 'Low' END
  3. CASE risk_code WHEN 'H' THEN 'High' WHEN 'M' THEN 'Medium' WHEN 'L' THEN 'Low' ELSE NULL END
  4. CASE WHEN risk_code = 'H' THEN 'High' WHEN risk_code = 'M' THEN 'Medium' WHEN risk_code = 'L' THEN 'Low' ELSE 'Not Assessed' END (correct answer)
Explanation: When working with CASE expressions in SQL, the critical distinction is between simple CASE (which uses = comparisons implicitly) and searched CASE (which uses explicit Boolean conditions). This question tests whether you understand how each form handles NULL. The searched CASE in D works correctly because each WHEN clause uses an explicit equality check (risk_code = 'H', etc.), and the final ELSE 'Not Assessed' catches anything that didn't match the previous conditions — including NULL. Since no equality check can ever match NULL, the ELSE branch naturally handles it, producing exactly the four-way mapping the report requires. A is the classic NULL trap. Simple CASE syntax (CASE risk_code WHEN NULL THEN ...) performs an implicit equality test internally, and in SQL, NULL = NULL evaluates to NULL (not TRUE). So WHEN NULL never matches, and rows with a null risk_code fall through to an implicit ELSE NULL, returning nothing rather than 'Not Assessed'. B returns the raw code letter (risk_code) for matches inside IN ('H','M','L') instead of translating them to full words — so a row with 'H' would display 'H', not 'High'. The logic is also unnecessarily convoluted. C uses simple CASE correctly for the three letter codes but explicitly returns NULL in the ELSE, meaning null inputs still produce no output rather than 'Not Assessed'. Study tip: Whenever you need to handle NULL in a CASE expression, prefer the searched form with IS NULL or rely on ELSE — never use WHEN NULL in a simple CASE, because equality-based comparisons with NULL always fail in SQL.