SQL and databases, from zero to interviews
A free SQL and DBMS course at three levels, Basic, Intermediate and Advanced: SELECT, joins, GROUP BY, subqueries, window functions, CTEs, indexes and transactions, plus the DBMS theory exams ask (ER diagrams, normalisation, ACID, serializability and recovery) and 50 SQL interview problems. Every query runs on real Postgres in the browser, on a phone too.
Basic
What a database is, then SQL from your first SELECT to joins and subqueries.
Module 1: What a database is
Why we keep data in a database, not a spreadsheet: tables, rows, keys, and what a DBMS does for you.
Module 2: Your first queries
Ask the database questions with SELECT: pick columns, filter rows with WHERE, handle NULL and sort the answer.
- SELECT: columns, expressions, aliases, DISTINCT
- WHERE: filtering rows
- NULL: the value that isn't there
- ORDER BY, LIMIT and pages
Module 3: Functions and expressions
Work with text, numbers and dates inside a query, and turn values into categories with CASE.
Module 4: Aggregates and GROUP BY
Summarise thousands of rows into a report: totals, averages and counts per group, filtered with HAVING.
- COUNT, SUM, AVG, MIN, MAX
- GROUP BY one and many columns
- HAVING vs WHERE, and the order a query runs in
- Conditional aggregation
Module 5: Joins
Combine tables: inner and outer joins, self joins, and finding the rows that have no match.
- INNER JOIN: matching rows across tables
- LEFT, RIGHT and FULL joins: keeping rows with no match
- SELF and CROSS joins
- Joins with GROUP BY, and the double-counting trap
Module 6: Subqueries and set operations
Put a query inside a query, test for existence, and combine result sets with UNION, INTERSECT and EXCEPT.
- Scalar and IN subqueries
- Correlated subqueries, EXISTS and the NOT IN trap
- Subqueries in FROM: derived tables
- UNION, INTERSECT and EXCEPT
Intermediate
Design tables and change data, model with ER diagrams, normalise, and master window functions and CTEs.
Module 7: Creating tables
Design tables that protect their own data: types, primary and foreign keys, UNIQUE, CHECK and ALTER TABLE.
- CREATE TABLE and data types
- Constraints: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT
- Foreign keys and ON DELETE
- ALTER, DROP, TRUNCATE, identity columns and sequences
Module 8: Changing data
Insert, update and delete rows safely, return what changed, and upsert with ON CONFLICT.
- INSERT: one row, many rows, from a SELECT, with RETURNING
- UPDATE and DELETE
- Upsert with ON CONFLICT, and MERGE
Module 9: ER modelling
Turn a real-world problem into an ER diagram and then into tables: entities, relationships and cardinality.
Module 10: Normalization
Spot redundancy and anomalies, find functional dependencies and keys, and normalise to 3NF and BCNF.
- Redundancy and the three anomalies
- Functional dependencies, closure and keys
- 1NF, 2NF and 3NF
- BCNF, lossless joins and when to denormalise
Module 11: Window functions
Rank, number and compare rows without collapsing them: ROW_NUMBER, RANK, LAG and running totals.
- OVER and PARTITION BY: windows vs GROUP BY
- ROW_NUMBER, RANK, DENSE_RANK and top-N per group
- LAG, LEAD and FIRST_VALUE: match-to-match change
- Running totals and moving averages: frames
Module 12: CTEs and recursive queries
Write long queries as readable steps with WITH, walk hierarchies with recursive CTEs, and build reports.
- WITH: multi-step queries you can read
- Recursive CTEs: org charts, paths and generated rows
- Reports: ROLLUP, STRING_AGG and pivots
Advanced
Transactions and isolation, indexes and query plans, views and triggers, and how a DBMS works inside.
Module 13: Transactions and concurrency
Keep money correct when things fail or run at once: ACID, isolation levels, MVCC, locks and deadlocks.
- Transactions and ACID
- Concurrency problems: lost updates and friends
- Isolation levels and MVCC
- Locks, FOR UPDATE and deadlocks
Module 14: Indexes and query plans
Make slow queries fast: how B+ tree indexes work, reading EXPLAIN, composite indexes and when an index can't help.
- Why indexes, and the B+ tree idea
- Reading EXPLAIN: scans and joins
- Composite and covering indexes
- When an index can't help
Module 15: Views, functions and security
Package logic in the database: views, PL/pgSQL functions, triggers, roles and GRANT, and stopping SQL injection.
- Views and materialised views
- Functions and procedures in PL/pgSQL
- Triggers: audit logs, updated_at and rules
- Roles, GRANT/REVOKE and SQL injection
Module 16: Inside the DBMS
The theory exams ask: relational algebra, storage and B+ trees, serializability and locking protocols, logging and recovery.
- Relational algebra and its SQL
- Storage: pages, B+ tree maths and hashing
- Serializability, two-phase locking and timestamps
- Logging and recovery: WAL, checkpoints, undo and redo
Module 17: Beyond one database
What changes at scale: replication, partitioning and sharding, CAP, and when to pick NoSQL over SQL.
Interview practice
Classic SQL interview problems, each graded on hidden data.
Interview pack: SQL interview problems
Fifty classic SQL interview questions, from the second-highest salary to login streaks, each graded on hidden data.