Intermediate · 5h 5m · 19 lessons
PostgreSQL
Store, query, and relate real data — from SELECT to indexes and EXPLAIN.
What you'll learn
- ✦Explain how PostgreSQL stores data in tables of typed columns, rows and keys, and map a real record onto them
- ✦Write SELECT queries that filter with WHERE, sort and page with ORDER BY and LIMIT, and summarise with GROUP BY and HAVING
- ✦Change data with INSERT, UPDATE and DELETE, and wrap related changes in a transaction that commits or rolls back as one
- ✦Link tables with primary and foreign keys, and combine them with INNER and LEFT JOINs
- ✦Enforce data rules with NOT NULL, UNIQUE, CHECK and foreign keys, and normalise a table so each fact lives in one place
- ✦Diagnose a slow query with EXPLAIN, add the index that fixes it, and weigh what each index costs
Part of these career paths
- Backend Developer path · course 8 of 14
- Full-Stack Developer path · course 9 of 14
Certification projects
Build 4 projects to earn your certificate
Hands-on work that proves you can apply what you learned — part of the certificate requirements.
Syllabus
Module 1 · 57m
Meet PostgreSQL Free
What you'll be able to do
- Can explain what a relational database is and turn a real list into a table's rows and columns
- Can create a table with the right type for each column, including exact money and moments in time
- Can connect with psql, list and describe tables, and run a SQL statement
Module 2 · 52m
Reading data Locked
What you'll be able to do
- Can write a WHERE that keeps exactly the rows you mean, using AND, OR, IN, BETWEEN, ILIKE and IS NULL
- Can sort with ORDER BY, take a top N with LIMIT and work out the OFFSET for any page
- Can summarise data with GROUP BY and aggregates, and decide whether a filter goes in WHERE or HAVING
Module 3 · 50m
Writing data safely Locked
What you'll be able to do
- Can insert, update and delete rows safely, checking every WHERE with a SELECT first
- Can wrap related changes in a transaction and choose COMMIT or ROLLBACK correctly
- Can explain what Read Committed and MVCC let concurrent users see, and stop a lost update
Module 4 · 57m
Relationships & joins Locked
What you'll be able to do
- Can give each table a primary key and put the foreign key on the right side of a one-to-many link
- Can predict the rows an INNER JOIN and a LEFT JOIN return, and list rows with no match
- Can pick NOT NULL, UNIQUE, CHECK or a foreign key to enforce a stated rule
- Can split a repetitive table so each fact is stored once and linked by keys
Module 5 · 48m
Making it fast Locked
What you'll be able to do
- Can add an index that matches a query's filter and sort, with the columns in the right order
- Can read an EXPLAIN plan, name each scan, and time a query safely with EXPLAIN ANALYZE
- Can weigh an index's read speed against its write and disk cost, and find indexes nobody uses
Module 6 · 41m
Beyond the basics Locked
What you'll be able to do
- Can store varying details in a jsonb column and filter on them without the text-comparison trap
- Can save a query as a view, and choose between a view and a materialized view by how fresh the data must be
- Can pass user input as query parameters and plan the next steps: an ORM, migrations and tested backups