Write SQL like a professional: windows, recursion, transactions, triggers, indexes and JSON.
- 28 lessons
- 7 units
- 5 h 34 m in all
- £0, for good
what’s inside.
For people who build things on their own and want code that others trust and can change.
SQL Level 3 picks up where Level 2 left off. In 28 lessons you break big questions into steps, rank and track data with window functions, walk trees with recursion, change data safely with transactions, let triggers and indexes do the work, and read JSON. It ends with a level check: shipping a set of analytics queries that hold up on five different market stalls.
every lesson goes the same way.
- meet it
- guess first
- your turn
- fix it
- nailed it
unit 1
Queries in steps
Break big questions into named steps and combine whole results.
- Name a step with WITH11 minGive part of a query a name, then use it like a table.
- Steps that build on steps12 minChain several named steps into one clear pipeline.
- Stack results with UNION11 minPut one result under another, with or without repeats.
- Compare two lists11 minFind what two results share and what one has that the other lacks.
unit 2
Window functions
Rank, number and compare rows without losing any of them.
- A window keeps every row11 minPut a total beside every row instead of squashing the rows.
- Number the rows12 minGive every row a position that never wobbles.
- Ties and podiums11 minLet tied rows share a place, with or without a gap after.
- A window for each group12 minRestart the numbers and totals for every group.
unit 3
Windows over time
Track totals, changes and trends as the days go by.
unit 4
Recursion and views
Make rows from nothing, walk family trees and save queries for later.
- Counting with recursion12 minMake a query repeat itself until a condition stops it.
- Filling the gaps13 minMake a full calendar so missing days show up as zero.
- Walking a tree13 minFollow bosses and teams through every level at once.
- Save a query as a view11 minGive a query a name that everyone can read like a table.
unit 5
Change data safely
Group changes so they all happen or none do, and merge new data cleanly.
- All or nothing12 minMake several changes count as one, so nothing gets lost halfway.
- Changing your mind10 minThrow away a transaction's changes when something looks wrong.
- Checkpoints inside a transaction12 minUndo one part of a transaction and keep the rest.
- Insert or update12 minAdd new rows and update existing ones in a single statement.
unit 6
Let the database do the work
Get receipts for changes, react to them automatically and find rows fast.
- See what changed10 minGet the changed rows straight back from an insert, update or delete.
- Triggers that react13 minMake the database keep its own records whenever data changes.
- Triggers that guard13 minRefuse bad changes with an error message you write yourself.
- Indexes and query plans12 minHelp the database find rows fast, and check that it does.
unit 7
Speed, JSON and shipping
Shape indexes to fit your queries, read JSON data and ship a report you trust.
- Indexes that fit the query13 minBuild indexes on several columns, in the order your queries need.
- Reading JSON12 minPull values out of JSON text and skip the broken rows.
- Unpacking and building JSON13 minTurn JSON lists into rows, and rows back into JSON.
- Shipping a report14 minMake queries that keep working on tomorrow's messy data.