Lesson 19 · CASE and UNION reporting

Make the first matching rule the right rule

Task 1 of 8 · games · 0 results checked · 0 explanations saved

Opening your saved work…

Read the idea

Write the business rule in plain language before translating it. A searched CASE tests WHEN conditions in order and returns the first true branch. A more general condition placed first can swallow a more specific one; successful execution will not warn you.

For this practice, a game that needs at least 90 minutes requires a Long slot; at least 45 minutes needs a Standard slot; anything shorter needs a Short slot. Test 44, 45, 89 and 90 on paper. The boundary values matter as much as typical values.

Use simple CASE when comparing one expression to fixed values: CASE tier WHEN 'Gold' THEN 'Gold member' WHEN 'Silver' THEN 'Silver member' ELSE 'Other tier' END. Use searched CASE for ranges or multiple columns. These practice labels are separate from the official lab rules.

Worked example

SELECT title, play_minutes, CASE WHEN play_minutes >= 90 THEN 'Long slot' WHEN play_minutes >= 45 THEN 'Standard slot' ELSE 'Short slot' END AS slot FROM games WHERE id <= 6 ORDER BY id;

Catan satisfies both numeric tests. The first match assigns Long slot before the broader Standard slot condition can run.

The practice removes the sample filter and preserves one row per game. CASE supplies a column; it does not filter rows.

Your task

For all games, return id, title and slot using Long slot at 90+ minutes, Standard slot at 45+ minutes, otherwise Short slot. Order by id.

Predict or plan before trying

Give me a hint

Check the 90-minute branch before the 45-minute branch.

Compare with a worked answer

SELECT id, title, CASE WHEN play_minutes >= 90 THEN 'Long slot' WHEN play_minutes >= 45 THEN 'Standard slot' ELSE 'Short slot' END AS slot FROM games ORDER BY id;

Using an answer is useful practice. Record what you learned; it does not demonstrate independent mastery.

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Query results

Run your query to see the result here.

Explain your evidence

Which existing game proves that reversing the two WHEN clauses changes the answer? Explain why the row still looks plausible.

My explanation or revision I would like help with this task

Your instructor can review account-saved work and help requests. Saving does not submit to Canvas.