Lumenary CS · Programming languages
SQL
The language of relational databases, from a first SELECT to joins, recursive queries, window functions, query plans, transactions and a schema of your own design.
SQL is the language for defining, changing and asking questions of data held in relational databases. It grew out of IBM research in the early 1970s (the first version, called SEQUEL, was described in 1974), became an ANSI and ISO standard in the 1980s, and is still revised every few years. Nearly every application that keeps records talks to a database in SQL: websites and phone apps, banks and hospitals, scientific archives, and the analytics warehouses where companies study their own data. Its signature idea is that you describe what result you want and let the database work out how to compute it.
This course is for people who can already program a little: if you know what a variable, a loop and a function are (the Start level of Lumenary CS or equivalent experience), you are ready. Lumenary CS has a general Databases lesson on why databases exist and how they work inside; this course goes much deeper into the language itself. It teaches standard SQL and runs every example on SQLite, and it points out where PostgreSQL, MySQL and SQLite differ, so that what you learn carries over to whichever database you meet at work.
To follow along, install the sqlite3 command-line shell from sqlite.org/download.html (it is already present on most macOS and Linux systems), or try queries in the official SQLite Fiddle, which runs SQLite in your browser with nothing to install. For a server database, install PostgreSQL from postgresql.org/download. Every program marked as checked was run by Lumenary's test suite with the SQLite 3.37 shell, and the output shown is the output it printed. The lessons were drafted with AI assistance and are awaiting review by a human computer scientist.
- Chloe · The Designer
“Why is the language built this way?” - Margaret · The Profiler
“What does it print, and what does it cost?” - Gideon · The Language Lawyer
“What exactly does the language guarantee?”
Foundations · 3 lessons
Foundations
Where SQL came from and the relational model behind it, then tables, types and constraints, changing data safely, and filtering and sorting rows, including the three-valued logic that NULL brings.
- 1First steps: relations and your first queriesWhere SQL came from, the relational model it rests on, the sqlite3 shell, and your first CREATE, INSERT and SELECT. 55 min
- 2Tables, types and constraintsDefining tables that refuse bad data, and changing rows safely with INSERT, UPDATE, DELETE and upsert. 60 min
- 3Filtering, sorting and NULLPrecise conditions with WHERE, values computed with CASE and functions, sorting and paging, and the three-valued logic that NULL brings to all of it. 60 min
Practice · 4 lessons
Practice
The everyday power tools: aggregation with GROUP BY and HAVING, joins of every kind, subqueries, common table expressions (recursive ones too) and views, and window functions for rankings, running totals and comparisons between rows.
- 4Aggregation and groupingTurning many rows into a few numbers with aggregate functions, GROUP BY and HAVING, and the NULL rules and dialect quirks that come with them. 55 min
- 5Joins of every kindInner, outer, cross and self joins, semi- and anti-joins, set operations, and the row-multiplying mistake that every SQL programmer makes once. 65 min
- 6Subqueries, CTEs and viewsQueries inside queries, named steps with WITH, recursion over hierarchies and graphs, and views that give a query a permanent name. 65 min
- 7Window functionsRankings, running totals, moving averages and comparisons with the previous row, computed without collapsing a single row. 65 min
Professional · 3 lessons
Professional
How databases make queries fast and keep data correct under concurrency, then normalisation, JSON columns and a complete schema designed and queried from scratch.
- 8Indexes and query plansHow B-tree indexes turn scans into searches, how to design composite, covering, expression and partial indexes, and how to read the plan a database chooses. 60 min
- 9Transactions and isolationAll-or-nothing changes, savepoints, the anomalies that concurrent users cause, the isolation levels that prevent them, and the patterns professionals use to stay correct. 65 min
- 10Project: a tool library from schema to reportsNormalise a messy spreadsheet into a schema with real constraints, decide where JSON belongs, and answer a lending library's questions with everything this course has taught. 80 min
Further reading and resources
- SQLiteThe official website of the database engine used for every example in this course: downloads, news and the full documentation.
- SQLite documentationStart with "SQL As Understood By SQLite" and the page on the command-line shell.
- SQL As Understood By SQLiteSQLite's language reference, with a syntax diagram for every statement.
- SQLite FiddleThe official online playground: SQLite compiled to WebAssembly, running in your browser.
- PostgreSQL documentation · The PostgreSQL Global Development GroupThe manual of the most widely used open-source server database; its SQL chapters are among the clearest references for the standard language.
- MySQL Reference Manual · OracleThe documentation of MySQL, the other very widely used open-source server database.
- ISO/IEC 9075: Database languages SQLThe international standard that defines SQL. It is sold by ISO and its national members; the database manuals above say how each product follows it.