04-advanced-databases

04 — Advanced Databases

Exercises for the Advanced Databases course.

This is the longest and most valuable course in the series. By the end of it you will be comfortable with the PostgreSQL features that distinguish a junior SQL user from a senior one: advanced types, indexing, window functions, transactions, stored procedures, triggers, roles, backup, full‑text search, and row level security.

Every section is self‑contained. Each one lives in its own PostgreSQL schema (e.g. adv_windows, adv_tx) so you can work through them in any order without sections clobbering each other.

Sections

Section	What you'll practice
02 — Schemas	CREATE SCHEMA, search_path, schema-qualified names
03 — Advanced Data Types	JSONB, ARRAY, UUID, INET, ENUM, HSTORE, sequences
04 — Working with Dates	DATE, TIMESTAMPTZ, INTERVAL, AGE(), EXTRACT, DATE_TRUNC, time zones
05 — Constraints	CHECK, UNIQUE, NOT NULL, DEFAULT, named constraints
06 — Foreign Keys	Parent/child relationships, ON DELETE CASCADE / SET NULL / RESTRICT
07 — Manage Tables	ALTER TABLE: add/drop/rename columns, change types, rename tables
08 — Indexes	CREATE INDEX, unique, partial, multi-column, EXPLAIN with large dataset
09 — Analyse Queries	EXPLAIN, EXPLAIN ANALYZE, sequential vs index scan, reading plans
10 — Conditional Expressions	CASE, COALESCE, NULLIF
11 — Dealing with Conflicts	ON CONFLICT DO NOTHING, ON CONFLICT DO UPDATE (upsert)
12 — Sub-queries	Scalar, correlated, IN, EXISTS, ANY, ALL
13 — Common Table Expressions	WITH, chained CTEs, recursive CTEs
14 — Window Functions	ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM() OVER, PARTITION BY
15 — Views & Materialised Views	CREATE VIEW, CREATE MATERIALIZED VIEW, REFRESH
16 — Transactions	BEGIN / COMMIT / ROLLBACK, savepoints, isolation levels
17 — Functions, Procedures & Triggers	PL/pgSQL functions, CALL, triggers, audit tables
18 — Roles, Groups & Privileges	CREATE ROLE, GRANT, REVOKE, group roles
19 — Backup & Restore	pg_dump, pg_restore, psql -f, COPY to/from CSV
20 — Full-Text Search	to_tsvector, to_tsquery, ts_rank, GIN indexes
21 — Row-Level Security	ENABLE ROW LEVEL SECURITY, CREATE POLICY, per-tenant isolation

Sections 01 — Intro and 22 — Next Steps from the video course have no hands-on SQL, so there are no exercises for them here.

Prerequisites

• PostgreSQL 14 or newer installed locally and psql on your PATH
• A sql_exercise database created as per ../SETUP.md
• Comfortable with the basics: SELECT, INSERT, UPDATE, DELETE, JOIN, GROUP BY. If any of those feel shaky, work through courses 01–03 first.
• For section 18 — Roles you'll need to be connected as a Postgres superuser (usually postgres)
• For section 19 — Backup & Restore you'll need pg_dump and pg_restore on your PATH (they ship with PostgreSQL)

Suggested order

You can do sections in almost any order, but some build on each other:

1. 02 → 03 → 04 → 05 → 06 → 07 — the "types, tables and constraints" block. Work these in order, they're short and cumulative.
2. 08 → 09 — paired. Section 08 builds the large dataset, section 09 re-uses the same shape to read query plans.
3. 10 → 11 → 12 → 13 → 14 — the "query power tools" block. Window functions (14) is the highlight — take your time.
4. 15 — views, sits on top of everything you've learned about queries.
5. 16 — transactions. Some exercises ask you to open a second psql session in another terminal. Keep both open.
6. 17 → 18 — stored procedures/triggers, then roles. Both need to be run as a superuser.
7. 19 — backup/restore. Mostly shell commands, not SQL.
8. 20 → 21 — full-text search and row-level security. These feel almost like mini products on top of PostgreSQL.

How each section is laid out

NN-section-slug/
├── schema.sql     <- DROP SCHEMA ... CASCADE; CREATE SCHEMA; CREATE TABLEs
├── seed.sql       <- INSERT sample rows (absent when the section doesn't need data)
└── exercises.md   <- 10+ numbered exercises + 2–3 bonus challenges + cleanup

To load a section's schema and seed:

psql -U postgres -d sql_exercise -f 04-advanced-databases/08-indexes/schema.sql
psql -U postgres -d sql_exercise -f 04-advanced-databases/08-indexes/seed.sql

Then open the section's exercises.md and work through it inside psql. Every schema file starts with DROP SCHEMA IF EXISTS ... CASCADE; so you can re-run it any time you want a clean slate.

Solutions live in ../solutions/04-advanced-databases/. Don't peek until you've tried the exercise.