Part II — SQL Mastery

"You learn SQL the way you learn a spoken language: by saying things in it, over and over, until the grammar disappears and you are just expressing what you mean."

This is the heart of the book — twelve chapters that take you from your first SELECT to query techniques that most working developers have never mastered. It is, by design, the most thorough free SQL education available anywhere. Most books treat SQL as a syntax reference to skim; we treat it as a language to be spoken fluently, because that is the recurring theme that governs this entire part: SQL is a language, learned by writing it.

You will write hundreds of queries here, all against the Mercado database. The exercises are not an afterthought — they are the learning. Read a chapter with a psql window open, type every example, break it on purpose, and then do the 20–30 problems at the end. Fluency is the goal, and fluency only comes from reps.

What you will learn

  • Ch. 5 — SELECT, FROM, WHERE: retrieving and filtering rows; comparison and logical operators; BETWEEN, IN, LIKE, IS NULL; DISTINCT, ORDER BY, LIMIT. The muscle memory of basic queries.
  • Ch. 6 — JOINs: connecting tables — INNER, LEFT, RIGHT, FULL, CROSS, and self-joins. This is the single most important SQL skill, and the chapter is built around the book's JOIN progression anchor: from a two-table join to a six-table business question.
  • Ch. 7 — Aggregation: COUNT, SUM, AVG, MIN, MAX; GROUP BY and HAVING; ROLLUP, CUBE, and GROUPING SETS.
  • Ch. 8 — Built-in Functions: the string, numeric, date/time, and type-conversion functions you reach for in nearly every real query.
  • Ch. 9 — Subqueries: scalar, row, and table subqueries; correlated subqueries; EXISTS, IN, ANY, ALL; subquery vs. join.
  • Ch. 10 — Set Operations: UNION, INTERSECT, EXCEPT — combining result sets the way relational algebra intended.
  • Ch. 11 — CTEs & Recursive Queries: the WITH clause for readable, reusable SQL, and recursive CTEs for hierarchies (Mercado's category tree and org chart).
  • Ch. 12 — Window Functions: the most underused feature in SQL — ROW_NUMBER, RANK, LAG/LEAD, running totals and moving averages — analytics without collapsing rows.
  • Ch. 13 — Data Modification: INSERT, UPDATE, DELETE, TRUNCATE, RETURNING, and UPSERT (ON CONFLICT), with the safety habits that prevent production horror stories.
  • Ch. 14 — Data Definition: CREATE, ALTER, DROP; PostgreSQL's data types and constraints; sequences and schemas.
  • Ch. 15 — Views & Functions: views and materialized views, user-defined functions (PL/pgSQL), and triggers.
  • Ch. 16 — Advanced SQL: JSONB, full-text search, arrays, and the PostgreSQL superpowers that often eliminate the need for a second database.

Why this part matters

Beyond fluency, Part II quietly proves theme #4: PostgreSQL's full power — window functions, CTEs, JSONB, full-text search — lets one well-understood database do work that teams often (wrongly) split across three. And it sets up theme #5, performance is basic competence: by the end you will be writing queries complex enough that how you write them starts to matter, which is exactly what Part IV picks up.

For every learning path

📊 Analysts and 💻 developers should treat all twelve chapters as core; Chapters 6, 7, 9, 11, and 12 are the ones that separate the competent from the dangerous. 🔬 CS students will connect each construct back to the relational algebra of Chapter 4. 🏗️ DBAs can move quickly through 5–10 but should study 13–16 closely.

Open a psql session. From here on, you don't just read — you write.

Chapters in This Part