CASE and UNION reporting

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.

A missing rating is not a zero-star review. Comparing NULL to a number produces an unknown result, so it does not select a WHEN branch. Without an explicit IS NULL branch, the row reaches ELSE.

For a review queue, use Awaiting review for NULL, Recommend for ratings at least 4.4, and Review notes for the remaining ratings. These labels describe this practice queue, not the official shelf-label question. Test both new arrivals and a known low-rated game.

Different columns give NULL different meanings: games.rating is not yet rated, sessions.score is absent for cooperative play, members.phone is not on file, and cafe_orders.member_id marks a walk-in. COALESCE can provide a display label, but replacing all of these with zero would erase their meanings.

COUNT(expression) counts non-NULL values. A CASE inside COUNT should return a value only for qualifying rows; an omitted ELSE returns NULL. ELSE 0 is still a non-NULL value and makes the count include the rows you meant to skip.

SUM has a different job. SUM(CASE WHEN condition THEN amount ELSE 0 END) adds the selected amounts and contributes zero for other rows. Ask whether a requested measure is a count of orders or an amount of money before choosing the aggregate.

For an operational prep queue, inspect sessions lasting at least 60 minutes and the total minutes they used. These are different units. A count plus its complementary count should equal the total rows; a sum of minutes should not be compared to a row count.

A report covering every event must start with events and preserve unmatched rows. LEFT JOIN registrations through event_id, then count registrations.id. COUNT(*) would count the placeholder row for an event with no registration.

An aggregate can be the expression a CASE evaluates. In this readiness check, an event is Needs outreach if it has no registrations and Has signups otherwise. Group at event level before applying that rule.

registered_date is the day someone signs up; event_date is the day the event takes place. Likewise, paid records whether a fee is settled. A sign-up count is not attendance, and multiplying signups by fee does not automatically measure received revenue.

A JOIN adds related columns to a row. UNION ALL appends the rows from another SELECT. Before combining sources, write the output columns in order and say what each means. Same column count is necessary, but same meaning and compatible values are just as important.

For a small product-and-event directory, agree on label, dollars and source. The event name must land under label and its fee under dollars. SQLite may accept a swapped text/number position, producing a report that runs with misleading cells.

Aliases for the combined output come from the first SELECT. Apply one ORDER BY at the end using those output names. A source literal tells readers where the row came from; a shared numeric ID by itself does not identify a record across different tables.

UNION removes rows identical across every selected column; UNION ALL retains every row. Duplicates are a property of the selected output, not proof that the source contains duplicate records. Two different sessions can have the same member and day.

Choose the unit of the report first. A list of distinct days needs deduplication; a list of recorded actions should preserve repeats. Add source and source_id when you need to trace each action back to its original table.

For an audit sample, inspect member 3 on 2026-09-12. Repeated sessions and orders are real source records. A source label separates orders from sessions, but alone it cannot distinguish two sessions on that same day. UNION ALL preserves both even when the display columns match.

INTERSECT answers which IDs appear in both sets. EXCEPT answers which IDs appear in the first set but not the second. Swapping EXCEPT operands reverses the question; it is not a formatting change.

Compare stable member IDs, then look up names. Different people can have the same name. Exclude NULL member_id from café orders before treating the result as a set of members, because walk-ins are not identified members.

Use WHERE id IN (set query) to retrieve display columns from members. The set operators remove repeated values, which is appropriate for a list of people. This does not establish how many orders they placed or whether they attended an event.

Start a multi-source report with an inclusion rule for each branch. For a member activity report, sessions and registrations have member IDs; café orders include both members and anonymous walk-ins. A customer report may include walk-ins, while a member loyalty report must exclude them.

Count each branch after its filters. With UNION ALL, the total must equal the sum of those branch counts. Equality is necessary but not sufficient: wrong columns, dates or business rules can still produce the expected count.

Keep the three time meanings explicit: session_date records play, order_date records a purchase, registered_date records a signup. event_date is the scheduled event day. Use this audit method when building the official ledger yourself; the point rules and required output stay in the Canvas handout.