Questions: CASE WHEN: Conditional Expressions in SQL

Question 1

A developer writes: CASE WHEN score >= 70 THEN 'C' WHEN score >= 80 THEN 'B' WHEN score >= 90 THEN 'A' ELSE 'F' END. A student with score 95 receives which grade?

• A. 'A' — the highest matching condition wins
• B. 'C' — the first true condition (95 >= 70) is returned and evaluation stops
• C. 'F' — no condition matches because evaluation happens in reverse order
• D. NULL — multiple conditions are simultaneously true, producing a conflict

Answer: (B)

CASE evaluates conditions top-to-bottom and returns the result of the FIRST true condition. Since 95 >= 70 is true, it returns 'C' immediately — it never evaluates the later conditions. This is the classic ordering mistake: to work correctly, conditions must go from most specific (>= 90) to least specific (>= 70), not the other way around.

Question 2

A query calculates `price * CASE WHEN discount_eligible THEN 0.9 END` with no ELSE clause. For rows where discount_eligible is false, this expression evaluates to:

• A. price * 1.0 — CASE defaults to 1 when no ELSE is provided
• B. price * 0 — CASE returns 0 when no condition matches
• C. NULL — CASE with no matching branch returns NULL, and NULL in arithmetic produces NULL
• D. An error — SQL raises an exception when CASE has no matching branch

Answer: (C)

When no WHEN condition matches and no ELSE clause is present, CASE silently returns NULL. Any arithmetic involving NULL produces NULL — so `price * NULL = NULL`, not price. This is a common bug: the developer intended non-eligible rows to keep their full price, but gets NULL instead. Adding ELSE 1.0 is the fix. SQL never raises an error for a CASE with no match — the NULL behavior is silent and easy to miss.

Question 3

A CASE expression evaluates most its WHEN conditions for nearly every row, even after finding the first true condition.

• T. True
• F. False

Answer: False

CASE uses short-circuit evaluation: it stops at the first true WHEN condition and returns that result without evaluating subsequent conditions. This is why condition order matters — and also means later conditions can assume earlier ones were false. For example, in a grade-assigning CASE ordered correctly (>= 90, >= 80, >= 70), the '>= 80' branch implicitly handles scores in 80-89, because any score >= 90 was already caught by the first branch.

Question 4

CASE WHEN can primarily appear in the SELECT list of a query, not inside aggregate functions or ORDER BY clauses.

• T. True
• F. False

Answer: False

CASE is a general expression that can appear anywhere an expression is valid in SQL: in SELECT, ORDER BY, GROUP BY, HAVING, and inside aggregate functions. A particularly powerful pattern is using CASE inside aggregates — e.g., SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) — to produce conditional totals from a single table scan. CASE can also appear in UPDATE SET clauses to conditionally modify values.

Question 5

You need a single query against an orders table that returns three counts in one row: how many orders are 'pending', how many are 'paid', and how many are 'cancelled'. How would you use CASE WHEN to accomplish this?

Model answer: SELECT SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count, SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count, SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count FROM orders

This technique — sometimes called conditional aggregation — uses CASE inside SUM (or COUNT) to count rows that meet each condition. Each CASE returns 1 when the condition is true and 0 otherwise; SUM then accumulates the count. This performs a single pass over the table rather than three separate queries, and is the standard SQL pattern for pivoting categorical data into columns.