SQL & PostgreSQL

A blog series

SQL and PostgreSQL From Zero

SQL and PostgreSQL from your first SELECT to production habits: joins, aggregates, window functions, schema design, indexes and EXPLAIN, transactions and locks, JSONB and safe migrations. Every query was run on a real PostgreSQL server and the real output is shown.

Started 
2026
Lessons 
8
Reading 
~3 h

Contents

Every lesson has the same steps: what it is, how it works, a real example, common mistakes, then interview questions with answers you can say out loud.

  1. Lesson 00Tables, SELECT and WHEREWhat a relational database is, running PostgreSQL, a small shop schema, SELECT, WHERE, NULL logic, and INSERT, UPDATE and DELETE with RETURNING.18 min
  2. Lesson 01Joins: Combining TablesPrimary and foreign keys, inner, left, right, full and cross joins with drawn result tables, self joins and the NULL trap in left joins.18 min
  3. Lesson 02Aggregates, GROUP BY and HAVINGCOUNT, SUM and AVG, GROUP BY rules, HAVING versus WHERE, FILTER, date_trunc reports and the order a SELECT is evaluated.20 min
  4. Lesson 03Subqueries, CTEs and Window FunctionsScalar, IN and EXISTS subqueries, WITH queries, a recursive category tree, ranking, LAG and LEAD, running totals and top-N per group.22 min
  5. Lesson 04Designing a Schema: Keys, Normalisation and ConstraintsIdentity versus UUID keys, normal forms with a flat-to-normalised redesign, ON DELETE, enums, money, timestamptz and soft deletes.22 min
  6. Lesson 05Indexes and EXPLAINHow a B-tree helps, composite, covering and partial indexes, and reading real EXPLAIN ANALYZE plans on a million rows.24 min
  7. Lesson 06Transactions, Isolation Levels and LocksAtomicity, read committed versus repeatable read versus serializable with two real sessions, row locks, SKIP LOCKED, deadlocks and retries.22 min
  8. Lesson 07PostgreSQL Features: JSONB, Full-Text Search, Arrays and Safe MigrationsJSONB with GIN indexes, full-text search, arrays, upserts, and expand-and-contract migrations that do not lock a busy table.24 min

Glossary

19 terms used in this series. Inside any lesson, hover or tap a dotted term to see its meaning without leaving the page. Browse all terms →