SQL & Relational Databases

Tables, keys, relationships, JOINs, aggregation, normalization, indexes, and transactions in PostgreSQL — learned by building the database of a small online shop.

Almost every application you will ever work on keeps its data in a relational database: the customers and orders of a shop, the users and posts of a social app, the accounts and transactions of a bank. Behind the framework and the API, there is a set of tables and a language, SQL, that asks questions of them. A developer who is comfortable with SQL can answer "which customers haven't ordered in 90 days?" in one query, while one who isn't writes a loop that is slow, long, and wrong.

This series teaches SQL and relational thinking from zero, using PostgreSQL, one of the most widely used open-source databases. You will learn how to design tables and constrain them so bad data cannot get in, how to connect tables with primary and foreign keys, and how to combine them with JOIN. You will summarize data with GROUP BY, break big questions into subqueries and CTEs, and learn what normalization really means. Then you will see why a query is slow, how an index fixes it, and how transactions keep your data correct when something fails halfway.

You won't just read about it. Across the series you build shopdb, the database of a small online shop with customers, products, categories, orders, and order items. Each chapter adds one idea to it: tables first, then data, queries, relationships, joins, reports, a cleaner design, a faster one, and finally a safe checkout that you call from Node.js. By the end you have a real schema, a script to rebuild it from scratch, and a set of queries you wrote yourself.

You need to be comfortable with the terminal from Command Line Basics. PostgreSQL runs in a container, so a first look at Docker helps, but the setup chapter gives you every command you need. The last chapter uses a little JavaScript, so JavaScript & TypeScript Fundamentals is useful there, and chapter 11 refers back to Big-O from Math for Programmers. Everything is free and runs on your own computer.

ORMs, NoSQL databases, database administration (backups, replication, tuning servers), and application-level frameworks are deliberately left out. Using a database from a backend framework is covered in the Backend track.

  1. 01Why Databases and the Relational ModelWhy files and spreadsheets stop working once data grows, how a relational database organizes data into tables, what SQL and PostgreSQL are, and the plan for shopdb, the small online shop we build in every chapter.
  2. 02Setting Up PostgreSQLRun PostgreSQL 17 in a Docker container, connect with psql, learn the meta-commands you will use every day, create the shopdb database and project folder, and keep your data between restarts.
  3. 03Tables, Data Types and ConstraintsWrite your first CREATE TABLE statements for customers and products, choose the right data types (and learn why money is never a float), and use NOT NULL, UNIQUE, CHECK, DEFAULT and primary keys so the database refuses bad data.
  4. 04Inserting, Updating, and Deleting DataFill shopdb with real rows: INSERT one or many rows, get generated ids back with RETURNING, change data safely with UPDATE and DELETE, understand TRUNCATE and upsert with ON CONFLICT, and write a seed.sql script you can re-run any time.
  5. 05Querying with SELECTAsk shopdb questions with SELECT: choose columns, filter with WHERE, combine conditions with AND/OR/NOT, match patterns with LIKE, handle NULL's three-valued logic, sort, paginate, remove duplicates, compute with functions and CASE, and learn the real order SQL evaluates a query.
  6. 06Keys and RelationshipsConnect tables the relational way: primary keys, surrogate vs natural keys, foreign keys and referential integrity, ON DELETE options, one-to-many and many-to-many relationships. Build categories, orders, and order_items in shopdb and migrate products.category to a real foreign key.
  7. 07JOINsCombine tables in PostgreSQL with INNER, LEFT, RIGHT, FULL, CROSS and self joins, chain three or four tables, find rows with no match using anti-joins, and avoid the classic mistakes: row multiplication and filters that silently turn a LEFT JOIN into an inner join.
  8. 08Aggregation and GroupingSummarize data in PostgreSQL with COUNT, SUM, AVG, MIN and MAX, group rows with GROUP BY, filter groups with HAVING, build revenue reports across joins, handle NULLs correctly, and use string_agg and FILTER.
  9. 09Subqueries, CTEs and Window FunctionsBreak big SQL questions into steps with subqueries, derived tables and WITH CTEs, save a report as a view, rank and total rows with window functions like ROW_NUMBER and SUM OVER, pick top-N per group, and combine results with UNION, INTERSECT and EXCEPT.
  10. 10Normalization and Schema DesignWhy one big table goes wrong, what 1NF, 2NF and 3NF mean in plain words, why order_items.unit_price is a deliberate copy, and a practical checklist and migration habit for designing tables you will not regret.
  11. 11Indexes and Query PerformanceSee why a query gets slow as tables grow, how a B-tree index turns a full scan into a few steps, how to read EXPLAIN ANALYZE, and the rules for composite, partial and foreign key indexes and the cases where an index is ignored.
  12. 12Transactions and Safe SQLMake multi-step changes all-or-nothing with BEGIN, COMMIT and ROLLBACK, build a safe checkout for shopdb, avoid race conditions with FOR UPDATE, stop SQL injection with parameterized queries in Node.js, and lock things down with roles and backups.