CASE and explicit category boundaries

sqlrelational-datasqlitepostgresqlindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

CASE and explicit category boundaries

Concept and contract

Derive understandable categories without silently treating missing data as a measured value.

CASE derives one output value from ordered conditions. The worked query labels unknown salary separately, then labels known values at least 100 as high and all remaining known values as lower. These are teaching categories, not a market-pay recommendation or a currency-normalized comparison.

The order of conditions matters because the first matching branch wins. Checking missingness first preserves the distinction between role 3 and role 8. Each source identity remains present, so this classification changes output values rather than collapsing rows into groups.

Mechanism and worked example

A searched CASE uses WHEN predicates followed by THEN values and an optional ELSE fallback.

A searched CASE uses WHEN predicates followed by THEN values and an optional ELSE fallback. The expression ends with END and can receive an alias like any other selected expression. It can later be grouped or counted, but this first query only displays categories.

Trace the threshold: role 1 at exactly 100 belongs to high because the predicate is inclusive. Roles 4 and 6 also belong there. Role 3 is unknown, while role 8 is lower because zero is a known value under the stated rule.

Complete worked example

sql

SELECT id, CASE WHEN salary IS NULL THEN 'unknown'
 WHEN salary >= 100 THEN 'high' ELSE 'lower' END AS band
FROM roles ORDER BY id;

Reasoning: Missingness is a separate band; 100 is included in high.

Counterexamples and common mistakes

The counterexample coalesces unknown salary to zero before testing a threshold.

The counterexample coalesces unknown salary to zero before testing a threshold. It therefore assigns role 3 to lower and loses the unknown category. Another frequent mistake uses greater than instead of greater than or equal, shifting the exact boundary case without anyone noticing.

Write boundary and missing-value examples next to the category definition. Avoid overlapping conditions whose order accidentally encodes a hidden priority. If ranges are intentionally overlapping, explain why the earlier branch takes precedence rather than relying on readers to infer it.

Counterexample: a plausible but wrong query

sql

SELECT id, CASE WHEN COALESCE(salary,0) >= 100
 THEN 'high' ELSE 'lower' END AS band FROM roles ORDER BY id;

Reasoning: Replacing an unknown with zero removes an important category.

Independent practice: small query

Create a two-category label for active status: active when active equals one, archived otherwise.

Create a two-category label for active status: active when active equals one, archived otherwise. Return identity and the derived label for every role. This independent task uses a field with an existing CHECK constraint, so no extra unknown-status branch is required by this fixture.

Predict archived for identities 5 and 7 and active for the other six. Keep an explicit ELSE branch. The first step is a small CASE expression; extending it to uncertain external statuses is optional and would require a different input contract.

Step 1: Write the status condition. Step 2: Provide active and archived labels with END.

Practice starter: solve before reading the solution

sql

SELECT id, CASE WHEN /* active condition */ THEN 'active'
 ELSE 'archived' END AS state FROM roles ORDER BY id;

Reasoning: Classify a constrained binary state without changing source rows.

Hint 1: The active field is constrained to zero or one.
Hint 2: Use ELSE for the other valid state.

Solution, tests and trade-offs

The solution preserves all eight identities and labels the two archived records explicitly.

The solution preserves all eight identities and labels the two archived records explicitly. A result-count check alone cannot establish that the right rows received the right labels, so compare the identity-to-label mapping. No UPDATE is needed to compute a report category.

If such a category becomes a durable business rule, centralize or document it so reports do not drift. A derived display label should not replace the authoritative source state without a migration and compatibility plan. This exercise remains a read-only projection.

Practice solution and acceptance checks

sql

SELECT id, CASE WHEN active = 1 THEN 'active'
 ELSE 'archived' END AS state FROM roles ORDER BY id;

Reasoning: Only roles 5 and 7 are labelled archived.

Interview reasoning and next step

In an interview, describe CASE as an expression with ordered matching, not a procedural loop.

In an interview, describe CASE as an expression with ordered matching, not a procedural loop. Explain branch order, boundary inclusion, fallback and NULL handling using concrete records. Those decisions matter more than memorizing a long expression with no business contract.

Conditional aggregation later reuses CASE inside SUM or COUNT. First establish that the row-level label is correct, then aggregate it. This separation makes incorrect denominators and missing-category handling easier to detect in a practical report.

Optional stretch — after the core task works: Write a conditional count for active and archived records and reconcile it to eight.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.

Derive understandable categories without silently treating missing data as a measured value.