SQL (PostgreSQL, MySQL, SQLite)
SQL for PostgreSQL, MySQL and SQLite, from a first SELECT to joins, window functions, transactions, indexes and schema design, with every query run.
113 lessons: 15 free, 31 free with Pro answers, 48 with a free core and 19 with a free intro.
Examples run with sql.js 1.14.2.
Who it is for
Students, analysts, back-end developers and anyone preparing for the SQL round of a technical interview.
What you will be able to do
- Write SELECT queries that filter, sort, join and summarise data in PostgreSQL, MySQL and SQLite
- Summarise data with aggregates, GROUP BY, HAVING and window functions
- Combine results with subqueries, common table expressions and set operations
- Create tables with keys and constraints, and change data safely inside transactions
- Read query plans and choose indexes that make slow queries fast
- Design normalised schemas and decide when to denormalise
- Recognise how SQL dialects differ and write portable SQL
- Run SQL from application code with parameters instead of string building
Revise and practise
- Practice: every exercise and quiz (112 exercises, 113 quizzes)
Modules and lessons
What the labels mean
- Free
- the whole lesson and every answer in it
- Free lesson, Pro answers
- the whole lesson is free; the model answers to its interview questions open with a pass
- Free core, Pro depth
- the core of the lesson is free; its in-depth parts open with a pass
- Free intro, Pro
- the first part is free; the rest opens with a pass
- Pro
- opens with a Pro, Premium or Ultimate pass
Minutes are reading time: “8 of 25 min free” means 8 minutes of the lesson are free.
What a pass opens in this track
133 in-depth parts and 260 interview answers. The same pass opens every other track and every Pro tool.
- Module 1
Start here: databases and your first query
What SQL and relational databases are, running queries in the browser and on your own computer, the practice databases, and SELECT with columns, expressions and aliases.
- What SQL is and how relational databases work Free: 15 minutes
- Run SQL in your browser and on your computer Free: 15 minutes
- Meet the practice database and its tables Free: 20 minutes
- SELECT and FROM: columns, expressions, aliases Free: 20 minutes
- Module 2
Filtering and sorting rows
WHERE and comparisons, LIKE patterns, NULL and three-valued logic, ORDER BY with LIMIT and OFFSET, DISTINCT and CASE.
- WHERE, comparison and logical operators Free: 20 minutes
- LIKE and pattern matching Free: 15 minutes
- NULL and three-valued logic Free: 25 minutes
- ORDER BY, LIMIT and OFFSET Free: 20 minutes
- DISTINCT and CASE expressions Free: 20 minutes
- Module 3
Data types and built-in functions
Types and casting, numbers and arithmetic, text functions, dates and times, and time zones in PostgreSQL, MySQL and SQLite.
- Data types and type conversion Free lesson, Pro answers: 25 minutes
- Numbers, arithmetic and rounding Free lesson, Pro answers: 25 minutes
- Text functions and Unicode Free lesson, Pro answers: 25 minutes
- Dates, times and date arithmetic Free lesson, Pro answers: 30 minutes
- Time zones, UTC and timestamps Free lesson, Pro answers: 25 minutes
- Module 4
Aggregates and GROUP BY
COUNT, SUM, AVG, MIN and MAX, GROUP BY and HAVING, conditional and string aggregation, subtotals with ROLLUP, and the order in which a query is evaluated.
- SQL aggregates: COUNT, SUM, AVG, MIN and MAX Free: 20 minutes
- GROUP BY in SQL: one row per group Free: 25 minutes
- HAVING in SQL: filtering groups Free: 15 minutes
- Conditional aggregation with CASE and FILTER Free: 25 minutes
- String aggregation: GROUP_CONCAT and STRING_AGG Free: 15 minutes
- Subtotals with ROLLUP, CUBE and GROUPING SETS Free lesson, Pro answers: 25 minutes
- SQL order of execution: how a query is evaluated Free: 15 minutes
- Module 5
Joining tables
Keys and relationships, inner and outer joins, self-joins, cross joins, multi-table joins and fan-out, range joins and LATERAL.
- Primary keys, foreign keys and relationships Free lesson, Pro answers: 20 minutes
- INNER JOIN: combining rows from two tables Free lesson, Pro answers: 25 minutes
- LEFT, RIGHT and FULL OUTER JOIN Free lesson, Pro answers: 30 minutes
- Self-joins: comparing rows of one table Free lesson, Pro answers: 20 minutes
- CROSS JOIN and combinations Free lesson, Pro answers: 20 minutes
- Joining many tables without double counting Free lesson, Pro answers: 25 minutes
- Range joins, as-of joins and LATERAL Free lesson, Pro answers: 30 minutes
- Module 6
Subqueries and set operations
UNION, INTERSECT and EXCEPT, scalar and derived subqueries, IN and EXISTS with anti-joins, and correlated subqueries.
- UNION, INTERSECT and EXCEPT in SQL Free lesson, Pro answers: 20 minutes
- Subqueries in SELECT, WHERE and FROM Free core, Pro depth: 8 of 25 minutes free
- IN, EXISTS, ANY/ALL and anti-joins Free core, Pro depth: 11 of 30 minutes free
- Correlated subqueries in SQL Free core, Pro depth: 8 of 25 minutes free
- Module 7
Common table expressions and recursive queries
WITH queries, recursive common table expressions, hierarchies and trees, and graph queries in SQL.
- WITH and common table expressions (CTEs) Free lesson, Pro answers: 25 minutes
- Recursive CTEs, series and calendar tables Free core, Pro depth: 10 of 35 minutes free
- Querying hierarchies: org charts and trees Free core, Pro depth: 13 of 35 minutes free
- Graph problems in SQL: routes and reachability Free core, Pro depth: 13 of 35 minutes free
- Module 8
Window functions
Window function basics, ranking, LAG and LEAD, window frames, top-N per group, running totals and growth, and gaps and islands.
- Window functions: OVER and PARTITION BY Free lesson, Pro answers: 25 minutes
- ROW_NUMBER, RANK, DENSE_RANK and NTILE Free core, Pro depth: 11 of 30 minutes free
- LAG, LEAD, FIRST_VALUE and NTH_VALUE Free intro, Pro: 7 of 30 minutes free
- Window frames: ROWS, RANGE and GROUPS Free intro, Pro: 6 of 30 minutes free
- Running totals, shares and growth rates Free intro, Pro: 8 of 30 minutes free
- Gaps and islands, streaks and sessions Free intro, Pro: 8 of 35 minutes free
- Module 9
Creating tables and constraints
CREATE TABLE and types, primary keys and identity, NOT NULL, UNIQUE and CHECK, foreign keys, ALTER TABLE and migrations, generated columns and exclusion constraints.
- CREATE TABLE and choosing column types Free lesson, Pro answers: 30 minutes
- NOT NULL, UNIQUE, CHECK and DEFAULT Free core, Pro depth: 9 of 25 minutes free
- Foreign keys and ON DELETE actions Free core, Pro depth: 9 of 25 minutes free
- ALTER TABLE and safe schema migrations Free core, Pro depth: 11 of 30 minutes free
- Generated columns Free core, Pro depth: 8 of 20 minutes free
- Preventing overlaps: ranges and exclusion Free core, Pro depth: 11 of 30 minutes free
- Module 10
Changing data: INSERT, UPDATE, DELETE and UPSERT
INSERT with RETURNING, UPDATE and DELETE, upsert and MERGE, and loading and exporting data in bulk.
- INSERT, defaults and getting generated keys Free lesson, Pro answers: 20 minutes
- UPDATE and DELETE: changing rows safely Free core, Pro depth: 7 of 20 minutes free
- UPSERT and MERGE: insert or update in one step Free core, Pro depth: 8 of 20 minutes free
- Bulk loading and exporting CSV Free core, Pro depth: 7 of 20 minutes free
- Module 11
Transactions, isolation and locking
Transactions and ACID, isolation levels and their anomalies, MVCC, locking reads and lost updates, and deadlocks with retries.
- Transactions, ACID and savepoints Free lesson, Pro answers: 25 minutes
- Isolation levels and read anomalies Free core, Pro depth: 12 of 30 minutes free
- MVCC: how readers and writers coexist Free core, Pro depth: 10 of 25 minutes free
- Lost updates, locking reads and job queues Free core, Pro depth: 11 of 35 minutes free
- Deadlocks, serialization failures and retries Free core, Pro depth: 9 of 30 minutes free
- Module 12
Indexes, query plans and performance
How indexes work, reading query plans, composite, covering, partial and expression indexes, sargable queries, join algorithms, keyset pagination and a tuning workflow.
- How indexes work: B-trees in practice Free lesson, Pro answers: 25 minutes
- Reading EXPLAIN plans in three engines Free core, Pro depth: 11 of 35 minutes free
- Composite indexes and column order Free intro, Pro: 7 of 30 minutes free
- Covering, partial and expression indexes Free intro, Pro: 7 of 30 minutes free
- Writing index-friendly (sargable) conditions Free intro, Pro: 7 of 30 minutes free
- Join algorithms and optimizer statistics Free intro, Pro: 7 of 35 minutes free
- Fast pagination with keyset (seek) paging Free intro, Pro: 7 of 30 minutes free
- A query-tuning workflow Free intro, Pro: 9 of 35 minutes free
- Module 13
Designing database schemas
From requirements to an ER model, normalisation and denormalisation, relationships and trees, money, time, ids and enums, history and soft deletes, and a design review.
- From requirements to an ER model Free lesson, Pro answers: 30 minutes
- Normalisation: 1NF to BCNF in practice Free core, Pro depth: 12 of 35 minutes free
- Denormalisation and derived data Free core, Pro depth: 10 of 30 minutes free
- Many-to-many, one-to-one and polymorphic links Free core, Pro depth: 10 of 30 minutes free
- Storing trees: adjacency, paths and closure tables Free core, Pro depth: 10 of 30 minutes free
- Module 14
Views, functions, procedures and triggers
Views, materialized views, functions and procedures, and triggers.
- Views and updatable views Free lesson, Pro answers: 25 minutes
- Materialized views and summary tables Free core, Pro depth: 10 of 30 minutes free
- Stored functions and procedures Free core, Pro depth: 13 of 35 minutes free
- Triggers: automatic actions on change Free core, Pro depth: 13 of 35 minutes free
- Module 15
JSON in SQL
Storing JSON, querying it, turning JSON into rows and building JSON, and updating and indexing JSON.
- Storing JSON: json, jsonb and JSON columns Free lesson, Pro answers: 25 minutes
- Querying JSON values and paths Free core, Pro depth: 11 of 30 minutes free
- Turning JSON into rows and rows into JSON Free core, Pro depth: 10 of 30 minutes free
- Updating and indexing JSON Free core, Pro depth: 9 of 30 minutes free
- Module 16
Text: collations, regular expressions and full-text search
Collations and case-insensitive matching, regular expressions, and full-text search.
- Collations, case and accents Free lesson, Pro answers: 30 minutes
- Module 17
Security: injection, privileges and data protection
SQL injection and query parameters, roles and privileges, row-level security for multi-tenant data, and backups and data protection.
- SQL injection and query parameters Free lesson, Pro answers: 30 minutes
- Users, roles and least privilege Free core, Pro depth: 6 of 20 minutes free
- Row-level security and multi-tenant data Free core, Pro depth: 6 of 20 minutes free
- Backups, restores and protecting personal data Free core, Pro depth: 6 of 20 minutes free
- Module 18
Using SQL from application code
SQL from Python, JavaScript and Java, ORMs and query builders, testing SQL and data quality, and reviewing SQL that AI tools write.
- SQL from Python: sqlite3 and psycopg Free lesson, Pro answers: 30 minutes
- SQL from Node.js and Java Free core, Pro depth: 10 of 30 minutes free
- ORMs, query builders and migration tools Free core, Pro depth: 10 of 30 minutes free
- Testing SQL queries and data quality Free core, Pro depth: 10 of 30 minutes free
- Reviewing SQL written by AI assistants Free core, Pro depth: 9 of 25 minutes free
- Module 19
SQL for data analysis
Pivoting and unpivoting, time-series bucketing, cohorts, retention and funnels, percentiles and distributions, and star schemas.
- Pivot and unpivot in SQL: rows to columns and back Free lesson, Pro answers: 30 minutes
- Time buckets in SQL: weeks, months, fiscal years Free core, Pro depth: 9 of 30 minutes free
- Cohort analysis in SQL: retention and funnels Free intro, Pro: 9 of 35 minutes free
- Median in SQL: percentiles, histograms, spread Free intro, Pro: 7 of 30 minutes free
- Star schemas: facts, dimensions and SCD type 2 Free intro, Pro: 8 of 35 minutes free
- Module 20
Dialects and portability
How PostgreSQL, MySQL and SQLite differ, the SQL standard and portable SQL, moving between engines, and other SQL dialects.
- PostgreSQL vs MySQL vs SQLite: differences Free lesson, Pro answers: 30 minutes
- The SQL standard and portable SQL Free core, Pro depth: 9 of 24 minutes free
- Migrating between SQL engines safely Free core, Pro depth: 12 of 30 minutes free
- Other dialects: SQL Server, Oracle, DuckDB Free core, Pro depth: 8 of 22 minutes free
- Module 21
SQL Server and Oracle side by side
First queries in SQL Server and Oracle, and how they differ in paging, identity, MERGE, dates, strings, NULLs, collations, temporary tables, hierarchies and PIVOT.
- SQL Server and Oracle: first queries side by side Free lesson, Pro answers: 25 minutes
- Row limits: TOP, OFFSET...FETCH and ROWNUM Free core, Pro depth: 10 of 25 minutes free
- Dates and times: SQL Server vs Oracle Free core, Pro depth: 13 of 35 minutes free
- String functions: SQL Server vs Oracle Free core, Pro depth: 12 of 30 minutes free
- NULLs and empty strings: Oracle versus SQL Server Free core, Pro depth: 9 of 25 minutes free
- Collations and case in SQL Server and Oracle Free core, Pro depth: 11 of 30 minutes free
- Hierarchies: recursive CTEs and Oracle CONNECT BY Free core, Pro depth: 11 of 30 minutes free
- PIVOT, APPLY and windows in SQL Server and Oracle Free core, Pro depth: 11 of 30 minutes free
- Module 22
SQL interview preparation
A method for SQL interview questions, the classic problem types from ranking to intervals, theory and output prediction, and timed practice.
- How to approach SQL interview questions Free lesson, Pro answers: 25 minutes
- Org chart drills: self-joins and recursion Free intro, Pro: 7 of 30 minutes free
- Interview drills: retention, growth, rolling Free intro, Pro: 8 of 35 minutes free
- Interview drills: 'all', 'none' and division Free intro, Pro: 6 of 30 minutes free
- Interview drills: intervals and as-of lookups Free intro, Pro: 8 of 35 minutes free
- Interview drills: reports, pivots, percentages Free intro, Pro: 7 of 30 minutes free
- Interview drills: Oracle SQL syntax Free intro, Pro: 8 of 35 minutes free
- SQL theory questions and output prediction Free lesson, Pro answers: 30 minutes
Related tools
Official documentation
- PostgreSQL 18 Documentation (The PostgreSQL Global Development Group)
- SQL As Understood By SQLite (SQLite)
- MySQL 8.4 Reference Manual (Oracle Corporation)
- sql.js documentation (sql.js)