the academy is open. a real database runs in your browser and I grade every query you write. get it wrong and I will tell you exactly why, in detail, because someone has to. free on @itchio. no signup. tsunderesql.itch.io/tsundere… it's not like I want you to enroll or anything.

a habit from nora's desk: before you trust a count, run COUNT(*), COUNT(id) and COUNT(DISTINCT id) side by side. the first gap is ids that are NULL, the second is ids that repeat. tsunderesql is an anime visual novel where you write real sql. wishlist it on steam: store.steampowered.com/app/5…

sql is written SELECT first but read FROM first: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. that's the logical order, not a promise about how the engine executes it, and it's why HAVING can test COUNT(*) and WHERE can't. tsunderesql is an anime visual novel where you write real sql. wishlist it on steam: store.steampowered.com/app/5…

C. WHERE runs before the groups exist, so it can't see COUNT(*). filter the groups with HAVING COUNT(*) >= 2 after GROUP BY. postgres, sqlite and duckdb all refuse the original. you got it? fine. this time. #SQL

next fest is over. went in with 612 wishlists, came out with 1,016. the week before it was about 9 a day. during the fest: 79 on the first full day, a dip to 33 midweek, then 93 on the last full day, which i didn't expect at all. anyone else see the last-day bump?

three requests for the same song. SELECT DISTINCT song hands back one row. that's your encore, don't push it. last day of steam next fest. tsunderesql demo: store.steampowered.com/app/5…

yesterday: D, 35 and 35. with ORDER BY and no frame, the window runs through every row tied on the current day, so both day-2 rows get 10 + 20 + 5. today, last one this week: what's wrong with this query? find it before i do. answer tomorrow #SQL

query haiku 8/8 · mei you counted my laps: nine. COUNT DISTINCT runner: one. still me. still ahead. COUNT(*) counts rows. COUNT(DISTINCT runner) counts different non-NULL runners. last day of steam next fest. tsunderesql demo: store.steampowered.com/app/5…

SELECT 7 / 2 returns 3 in sqlite and postgres: integer in, integer out. write 7.0 / 2 to get 3.5. duckdb is the odd one, its / already gives 3.5. tsunderesql is an anime visual novel where you write real sql. it's in steam next fest, oct 19-26: play the demo or wishlist: store.steampowered.com/app/5…

two players tie on points, ORDER BY points DESC. RANK() gives both of them 1 and the next one 3. DENSE_RANK() gives 1, 1, 2. ROW_NUMBER() gives 1 and 2, and without a tiebreaker column nothing says who gets which. mei says it's her. tsunderesql is an anime visual novel where you write real sql. it's in steam next fest, oct 19-26: play the demo or wishlist: store.steampowered.com/app/5…

same table, two counts. COUNT(*) says 5, COUNT(email) says 3. counting a column skips its NULLs, so the two students with no email drop out of the second number. tsunderesql is in steam next fest, oct 19-26. demo: store.steampowered.com/app/5…

yesterday: A, 6 in postgres and sqlite. 7 / 2 on two integers is 3, then 3 * 2. duckdb's / gives 3.5, so duckdb says 7.0. today: a running total with a tie in it. i already know what you'll pick. answer tomorrow #SQL

query haiku 7/8 · nora NOT IN, with one NULL in the set, returns nothing. one unknown. all lost. if the subquery behind NOT IN returns a NULL, no row passes: each check is false or unknown. tsunderesql is in steam next fest, oct 19-26. demo: store.steampowered.com/app/5…

CASE WHEN points = 40 THEN 'game point' END has no ELSE, so every row that isn't 40 gets NULL. if you wanted a value there, write the ELSE. tsunderesql is an anime visual novel where you write real sql. it's in steam next fest, oct 19-26: play the demo or wishlist: store.steampowered.com/app/5…

ORDER BY points DESC LIMIT 1 gives you the top scorer. LIMIT 1 on its own gives you a row, and nothing says which one. tsunderesql is in steam next fest, oct 19-26. demo: store.steampowered.com/app/5…

yesterday: B, 5, 3, 2. COUNT(*) counts rows, COUNT(email) skips the NULLs, and DISTINCT folds the two duplicate emails into one. today: one line of arithmetic. it's not a trick. mostly. answer tomorrow #SQL

query haiku 6/8 · aimi you wrote it twice. sweet. plain UNION would toss the spare. UNION ALL keeps both. UNION drops duplicate rows. UNION ALL keeps them. tsunderesql is in steam next fest, oct 19-26. demo: store.steampowered.com/app/5…

LEFT JOIN orders, then WHERE orders.status = 'paid'. students with no order have NULL there, the filter drops them, and you get what an inner join would give. if you meant to keep them, put the filter in ON. tsunderesql is an anime visual novel where you write real sql. it's in steam next fest, oct 19-26: play the demo or wishlist: store.steampowered.com/app/5…

where do NULLs go in ORDER BY? sorting ascending, sqlite puts them first and postgres puts them last. reina doesn't leave her seating plan to chance: ORDER BY rank NULLS LAST. tsunderesql is an anime visual novel where you write real sql. it's in steam next fest, oct 19-26: play the demo or wishlist on steam