For teachers · class guide

Class guide: 50 minutes with Caso Abierto

A 50-minute plan with three levels. No solutions: no culprits, no answer values.

The plan

5 minIntro: what gets turned in. A student closes a case by submitting the SQL query that proves their answer, not by picking an option.

5 minGuided case 0: the teacher projects the editor, writes a SELECT in front of the class and shows how an answer gets submitted.

30 minCase by level: each student (or pair) works the case from their track, below.

10 minGroup discussion: "why your first query failed". Share the mistake, not the answer.

The three tracks

Introduction · T1·01

The File Nobody Asked For

SELECT WHERE date ranges ORDER BY

The trap (in general terms)

Narrowing a time window looks easy with BETWEEN, but BETWEEN includes both ends, and with dates and times that is rarely what you want. With >= and < you control exactly which edge is in. See this trap's page →

What the student turns in

The final query that narrows the case's time window and answers the three questions in the case file, plus one sentence explaining what changed from their first attempt.

Discussion questions

Which edge of the window is in and which is out? How did you decide?
How many rows did your first query return, and how many did the final one return?
If the window were a whole day, how would you write the filter so you don't lose the last part of the day?

Intermediate · T2·03

The Entry Nobody Wrote

anti-join NOT EXISTS LEFT JOIN … IS NULL the NOT IN with NULL trap

The trap (in general terms)

A NOT IN against a list containing a single NULL throws out the whole query without a warning: it doesn't fail, it just returns nothing, and that gives false confidence. See this trap's page →

What the student turns in

The query that finds the one execution with no ledger entry, plus one sentence explaining why their first NOT IN attempt returned zero rows (or all of them) instead of the missing one.

Discussion questions

Did your first query return zero rows or all of them? Why doesn't that result warn you something is wrong?
Why doesn't NOT EXISTS have the same problem as NOT IN?
Before trusting a NOT IN, how would you check whether the column allows NULL?

Advanced · T3·01

The Report That Multiplied Itself

cardinality fan-out aggregate before joining COUNT DISTINCT

The trap (in general terms)

Aggregating after a one-to-many JOIN duplicates every row on the "one" side once per matching row on the other side, and any SUM computed afterward comes out inflated. See this trap's page →

What the student turns in

The query that calculates the quarter's real commission without the duplication, plus one sentence explaining how they detected the original report was inflated.

Discussion questions

How many rows did you expect the JOIN to return before running it? How many did it actually return?
By what proportion was the original report's sum inflated?
How do you aggregate a table BEFORE joining it, instead of aggregating afterward?

Solutions for the teacher are sent separately, to whoever requests the license.

The first case runs free in your browser, no signup.

Play the first case free →