PG
PostgreSQL — Intermediate
Answer real questions with SQL: combine tables with joins, structure queries with subqueries and CTEs, rank and compare rows with window functions, make queries fast with indexes, keep data correct with transactions, and use views, functions and JSONB.
Lessons
- 1Sample Database & JoinsLoad the course database, then combine tables with INNER, LEFT and anti-joins.
- 2Database Design & NormalisationTurn a messy spreadsheet into well-structured tables: one fact in one place, linked by keys.
- 3Subqueries & CTEsQueries inside queries, EXISTS, WITH clauses and recursive CTEs.
- 4Window FunctionsRankings, running totals, per-group averages and comparisons with previous rows.
- 5Indexes & EXPLAINRead query plans and add indexes that turn slow scans into fast lookups.
- 6TransactionsBEGIN, COMMIT and ROLLBACK for all-or-nothing changes; savepoints and isolation.
- 7Upserts with INSERT … ON CONFLICTInsert new rows or update existing ones in one atomic statement, and import data safely.
- 8Views, Materialized Views & FunctionsSave queries as views, cache results, and write SQL and PL/pgSQL functions.
- 9JSON & JSONBStore flexible data in JSONB, query inside it, index it and build JSON responses.
- 10Arrays, Enums & DomainsRicher column types: enumerated values, reusable validated types, and arrays.