PostgreSQL
Learn SQL and PostgreSQL by building a school results database — tables, queries, joins, window functions, indexes, transactions, JSONB, security and operations.
Learn databases from scratch with PostgreSQL: install it, create tables, add and change data, ask questions with SELECT, connect tables with keys, and summarise data with aggregates. Lessons build one school database step by step — run them in order.
You will be able to
- Install PostgreSQL and work in psql
- Create tables with the right data types and constraints
- Insert, update, delete and query data with SQL
- Link tables with primary and foreign keys and summarise data with GROUP BY
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.
You will be able to
- Combine tables with INNER, LEFT and anti-joins
- Write readable queries with CTEs and window functions
- Read EXPLAIN plans and add the right indexes
- Use transactions, views, PL/pgSQL functions and JSONB
Run PostgreSQL like a professional: tune queries with the right index types, handle concurrency and locking safely, automate with triggers, add full-text search, partition large tables, lock data down with roles and row-level security, and operate databases in production. Uses the school database from the Intermediate track.
You will be able to
- Diagnose slow queries and choose partial, expression, covering and BRIN indexes
- Prevent race conditions with row locks and build a SKIP LOCKED job queue
- Use triggers, full-text search and partitioning
- Secure data with roles and row-level security; back up, monitor and maintain a database