Advanced SQL Tutorial
Window functions, CTEs, reporting queries, transactions and safe data changes
Intermediate4 lessonsabout 30 min of reading
This course is for people who can already write SELECT, JOIN and GROUP BY queries and want the next layer: the features that separate everyday queries from the ones used in analytics, reporting and production back ends. If grouping and joins still feel shaky, take the SQL Fundamentals course first.
Examples run on SQLite, with real printed results, so you can reproduce every table yourself. Where a feature differs in PostgreSQL, MySQL or SQL Server, the lesson marks it. For transaction isolation, which SQLite cannot fully show, the lesson explains the behaviour carefully and says plainly when a result was not run.
The four lessons go from window functions (rankings, running totals, LAG and LEAD), to CTEs and recursive queries for hierarchies, to subqueries, set operations and CASE for reporting, and finally to transactions, isolation anomalies and the write patterns that keep data correct under concurrency: atomic updates, upserts and soft deletes.
Advanced SQL lessons
- 1SQL Window Functions: ROW_NUMBER, RANK, Running Totals, LAG and LEADLearn SQL window functions with runnable examples: ROW_NUMBER, RANK, DENSE_RANK, running totals, moving averages, LAG, LEAD and top-N per group.6 min read
- 2SQL CTEs and Recursive Queries: Walk Hierarchies with WITH RECURSIVEUse SQL CTEs to name steps in a query and recursive CTEs to walk trees and bills of materials, generate date series and guard against infinite loops.8 min read
- 3SQL Subqueries, UNION, INTERSECT, EXCEPT and CASE for ReportingWrite SQL subqueries, EXISTS, UNION, INTERSECT, EXCEPT and CASE expressions for reports, and avoid the NOT IN with NULL trap that returns zero rows.7 min read
- 4SQL Transactions, Isolation Levels and Safe Writes: Upserts, Soft DeletesUnderstand SQL transactions, isolation levels and anomalies like lost updates, and use upserts, atomic updates and soft deletes to change data safely.9 min read
Preparing for an interview? Practise SQL & Databases interview questions
Learning Advanced SQL to land a job? Browse open roles or check which skills are trending.