SQL & Relational Thinking

Why relational, and why SQL before any ORM

Module 02 kept your places in a Python list. That list dies when the process dies. This module is the other half — the part that remembers — and you are going to talk to it directly, with no library in between.

What

A relational database stores data as tables: named columns, one row per thing, with rules the database itself enforces about what a valid row looks like.

Where

The Postgres container you started in Module 00. You reach it with docker compose exec db psql -U cityguide -d cityguide.

When

Any time data must outlive the process, be read by more than one thing, or be searched by something other than "loop over everything".

How

SQL — a language where you describe the result you want and the database works out how to produce it.

The wrong mental model to kill first. Most people arrive believing a database is "where the objects go" — a hard drive for your Python classes. It is not. It is a query engine with a planner, a cost model, and indexes, and the tables are the part you happen to see. An ORM (Module 05) will make it look like object storage again, which is exactly why you learn this first. The engineer who cannot read the SQL their ORM generated is the engineer who cannot explain why one endpoint takes 40 milliseconds and the next takes 9 seconds.

SQL is declarative, and that is the whole trick. In Python you write how: open the list, loop, compare, append. In SQL you write what: "the names of cities in Portugal, biggest first". You never say scan-this-then-sort-that. A component called the planner reads your query, looks at what it knows about the size and shape of your tables, and picks a strategy. Two consequences follow, and both matter for the rest of your career:

• The same query can be fast today and slow in six months — nothing changed in your code, the data grew and the planner changed its mind. This is normal, not a bug, and it is why the last section of this module exists.
• You cannot debug SQL by reading it alone. The text says what you asked for; it does not say what the database did. EXPLAIN ANALYZE is the only honest answer, and beginners are almost never shown it.
• Constraints are enforced by the database, not by your app. Your validation code protects rows that arrive through your code. A constraint protects rows that arrive through anything — a migration, a colleague's script, you at 2am in psql.
• Set thinking beats row thinking. Beginners write a loop that runs one query per item. SQL wants one query that handles the whole set. That single habit is worth more than any syntax you will learn today.

Your practice schema — type this in now

This is not the City Guide schema. Module 04 designs that from scratch, driven by the questions the API has to answer, and you would rob yourself of that by copying an answer here. This is a throwaway sandbox: cities, visits to those cities, and themes attached to cities. Open a SQL prompt:

$ docker compose exec db psql -U cityguide -d cityguide
psql (16.x)
Type "help" for help.

cityguide=#

That cityguide=# is the psql prompt. Now type (or paste) the four tables. Statements end with a semicolon — without one, psql waits and your prompt changes to cityguide-#, which is the single most common "why is nothing happening" moment of your first hour:

CREATE TABLE cities (
    id          serial  PRIMARY KEY,
    name        text    NOT NULL,
    country     text    NOT NULL,
    population  integer CHECK (population >= 0),
    founded     integer,
    UNIQUE (name, country)
);

CREATE TABLE themes (
    id    serial PRIMARY KEY,
    label text   NOT NULL UNIQUE
);

CREATE TABLE visits (
    id         serial  PRIMARY KEY,
    city_id    integer NOT NULL REFERENCES cities(id) ON DELETE CASCADE,
    visitor    text    NOT NULL,
    visited_on date    NOT NULL,
    rating     integer CHECK (rating BETWEEN 1 AND 5),
    note       text
);

CREATE TABLE city_themes (
    city_id  integer NOT NULL REFERENCES cities(id) ON DELETE CASCADE,
    theme_id integer NOT NULL REFERENCES themes(id) ON DELETE RESTRICT,
    PRIMARY KEY (city_id, theme_id)
);

Each successful statement prints CREATE TABLE back at you. Now the data. Notice deliberately: Reykjavik has no founded year, two visits have no rating, and several have no note. Those NULLs are not sloppiness — they are the material for the trap in the next section.

INSERT INTO cities (name, country, population, founded) VALUES
  ('Lisbon',     'Portugal',   545796, 1256),
  ('Porto',      'Portugal',   231962, 1123),
  ('Kyoto',      'Japan',     1463723,  794),
  ('Osaka',      'Japan',     2691185, 1583),
  ('Tbilisi',    'Georgia',   1118035,  455),
  ('Valparaiso', 'Chile',      296655, 1536),
  ('Hobart',     'Australia',  247086, 1804),
  ('Reykjavik',  'Iceland',    139875, NULL);
-- INSERT 0 8

INSERT INTO visits (city_id, visitor, visited_on, rating, note) VALUES
  (1, 'ana',  '2024-03-11', 5,    'tram 28 at dawn'),
  (1, 'ben',  '2024-05-02', 4,    NULL),
  (1, 'cara', '2024-09-19', 5,    'best pastel de nata'),
  (2, 'ana',  '2024-03-15', 4,    NULL),
  (2, 'dev',  '2025-01-08', 3,    'rained the whole time'),
  (3, 'ben',  '2023-11-21', 5,    'maple season'),
  (3, 'cara', '2024-04-02', 5,    NULL),
  (3, 'dev',  '2025-02-14', 4,    'crowded'),
  (3, 'ana',  '2025-03-30', NULL, 'too short to rate'),
  (4, 'ben',  '2023-11-25', 3,    NULL),
  (4, 'ana',  '2025-04-01', 4,    'ate too much'),
  (5, 'cara', '2024-06-12', 5,    'sulphur baths'),
  (5, 'dev',  '2024-06-30', NULL, NULL),
  (6, 'ben',  '2024-08-05', 2,    'hills, endless hills'),
  (6, 'ana',  '2025-05-17', 4,    NULL);
-- INSERT 0 15

INSERT INTO themes (label) VALUES ('coastal'), ('historic'), ('food'), ('nightlife');
-- INSERT 0 4

INSERT INTO city_themes (city_id, theme_id) VALUES
  (1,1),(1,2),(1,3), (2,2),(2,3), (3,2),(3,3),
  (4,3),(4,4), (5,2),(5,4), (6,1),(6,2), (7,1);
-- INSERT 0 14

INSERT 0 8 means "8 rows inserted"; the zero is a legacy field you can ignore forever. Hobart (id 7) has themes but no visits, and Reykjavik (id 8) has neither — both facts are load-bearing later.

Six psql meta-commands carry you through this whole module. They start with a backslash and take no semicolon:

\dt              -- list tables in this database
\d visits        -- describe one table: columns, types, keys, constraints
\di              -- list indexes
\x               -- toggle expanded output (one column per line) for wide rows
\i notes/module-03.sql   -- run a .sql file
\q               -- quit back to your shell

SELECT, WHERE, ORDER BY, LIMIT the shape of every query

What

The four clauses that make up the overwhelming majority of SQL you will ever write or read.

Where

Behind every list endpoint, every search box, every admin screen in every product you have used.

When

Reading data. Everything else in this module is a refinement of this one shape.

How

Pick columns, filter rows, order them, cut the list short — in that written order, though not in that execution order.

SELECT name, country, population
FROM cities
WHERE country = 'Portugal'
ORDER BY population DESC;

  name  | country  | population
--------+----------+------------
 Lisbon | Portugal |     545796
 Porto  | Portugal |     231962
(2 rows)

Never write SELECT * in application code. It is fine at the psql prompt while you explore. In an endpoint it means "send me every column, including ones added next year, including the 40 KB text blob nobody asked for" — and it silently breaks the moment someone renames a column. Name the columns you want.

Filtering. Five operators cover nearly everything:

WHERE country = 'Japan'                    -- equal
WHERE country <> 'Japan'                    -- not equal (!= also works)
WHERE country IN ('Japan', 'Chile')        -- one of a list
WHERE name LIKE 'P%'                       -- starts with capital P; % = any characters
WHERE name ILIKE 'p%'                      -- same, case-insensitive (Postgres extension)
WHERE population BETWEEN 200000 AND 600000 -- inclusive on both ends
WHERE founded IS NULL                      -- has no value at all

SELECT name FROM cities WHERE country IN ('Japan', 'Chile') ORDER BY name;

    name
------------
 Kyoto
 Osaka
 Valparaiso
(3 rows)

NULL is not a value, and this will bite you

NULL means "unknown". It is not zero, not an empty string, not false. And SQL does not use two-valued true/false logic — it uses three-valued logic: true, false, and unknown. Any comparison with NULL produces unknown, and WHERE keeps only rows where the condition is true. Unknown is discarded exactly like false.

Run both of these. They look like they ask the same question:

SELECT count(*) FROM visits WHERE rating = NULL;   -- 0   <- silently wrong
SELECT count(*) FROM visits WHERE rating IS NULL;  -- 2   <- correct

Ordering, and where NULLs land

SELECT name, founded FROM cities ORDER BY founded DESC NULLS LAST LIMIT 4;

    name    | founded
------------+---------
 Hobart     |    1804
 Osaka      |    1583
 Valparaiso |    1536
 Lisbon     |    1256
(4 rows)

Drop NULLS LAST and Reykjavik jumps to the top of the list, because Postgres treats NULL as larger than everything and DESC defaults to NULLS FIRST. A "most recently updated first" list that opens with a wall of blank rows is this default, every time.

LIMIT and OFFSET are pagination — the naive kind. LIMIT 10 OFFSET 20 gives you page 3. Two warnings you should carry forward: without an ORDER BY the database is under no obligation to return rows in a stable order, so a row can appear on page 1 and page 2; and OFFSET 100000 makes the database produce and throw away 100,000 rows before giving you anything. Module 06 replaces this with keyset pagination when your list endpoint gets real.

Keys and constraints rules the database enforces

What

Declared rules about what a valid row is — uniqueness, required values, allowed ranges, and which rows may point at which other rows.

Where

In the CREATE TABLE you already typed. Read it back with \d visits.

When

At design time, before data exists. Adding a constraint to a table that already violates it is a bad afternoon.

How

Five of them cover almost everything: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, REFERENCES.

Why not validate in Python instead? Because your Python is one of several doors into that table. There is also the migration you will write in Module 05, the one-off fix script someone runs on a Friday, a future second service, and you at the psql prompt. A constraint sits at the table and applies to all of them. Application validation gives a nice error message; a constraint gives a guarantee. You want both, and if you can only have one, keep the guarantee.

serial in the schema is shorthand: it makes an integer column whose default comes from a sequence, so the database picks 1, 2, 3… for you. Read the real shape back:

cityguide=# \d visits
                              Table "public.visits"
   Column   |  Type   | Collation | Nullable |              Default
------------+---------+-----------+----------+------------------------------------
 id         | integer |           | not null | nextval('visits_id_seq'::regclass)
 city_id    | integer |           | not null |
 visitor    | text    |           | not null |
 visited_on | date    |           | not null |
 rating     | integer |           |          |
 note       | text    |           |          |
Indexes:
    "visits_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "visits_rating_check" CHECK (rating >= 1 AND rating <= 5)
Foreign-key constraints:
    "visits_city_id_fkey" FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE CASCADE

Note what Postgres did for free: the primary key created an index. That is the first index in this module and you did not ask for it — a uniqueness rule can only be enforced cheaply if there is a sorted structure to check against, so PRIMARY KEY and UNIQUE always build one. Keep that in mind for the last section.

Break all three on purpose. Reading the error text now means recognising it instantly when it surfaces inside a 60-line traceback in Module 06:

INSERT INTO visits (city_id, visitor, visited_on, rating)
VALUES (999, 'zoe', '2025-06-01', 4);
ERROR:  insert or update on table "visits" violates foreign key constraint "visits_city_id_fkey"
DETAIL:  Key (city_id)=(999) is not present in table "cities".

INSERT INTO visits (city_id, visitor, visited_on, rating)
VALUES (1, 'zoe', '2025-06-01', 9);
ERROR:  new row for relation "visits" violates check constraint "visits_rating_check"
DETAIL:  Failing row contains (16, 1, zoe, 2025-06-01, 9, null).

INSERT INTO cities (name, country, population) VALUES ('Lisbon', 'Portugal', 1);
ERROR:  duplicate key value violates unique constraint "cities_name_country_key"
DETAIL:  Key (name, country)=(Lisbon, Portugal) already exists.

Every one of those messages names the constraint. That name is your search term and your fix — visits_city_id_fkey tells you the table, the column, and the kind of rule in one string.

ON DELETE: what happens to the children

A foreign key has to answer one more question: if the row being pointed at is deleted, what happens to the rows pointing at it? Your schema uses both answers deliberately.

• ON DELETE CASCADE deletes the children too. visits.city_id uses this: a visit to a city that no longer exists is meaningless, so deleting Lisbon should take its three visits with it. Use it when the child cannot exist independently — and respect it, because it is silent and recursive. Deleting one row can remove thousands.
• ON DELETE RESTRICT refuses the delete. city_themes.theme_id uses this: you may not delete the "coastal" theme while cities are still tagged with it. The database throws an error and you must clean up first. Use it when the deletion is probably a mistake.
• ON DELETE SET NULL keeps the child and blanks the link — useful for "author deleted, keep the post". It requires the column to be nullable, which reintroduces every NULL problem from the previous section, so choose it on purpose.
• The default, if you write nothing, is NO ACTION — effectively RESTRICT. Silence is not permission; it is refusal.

JOINs the one people fear

What

Combining rows from two tables into one result, matched on a condition you supply.

Where

Any query that needs facts from more than one table — which, once your schema is normalised, is most of them.

When

Instead of "query table A, loop over the results, query table B for each" — the pattern that turns one request into 200 round trips.

How

Two kinds carry 95% of real work: INNER JOIN and LEFT JOIN. Learn those two properly and skim the rest.

INNER JOIN keeps only matched pairs. If a city has no visits, it is not in the answer. If a visit pointed at a missing city it would not be either — though your foreign key makes that impossible, which is one more thing constraints buy you.

SELECT c.name, v.visitor, v.rating
FROM cities c
INNER JOIN visits v ON v.city_id = c.id
WHERE v.rating = 5
ORDER BY c.name, v.visitor;

The single-letter aliases c and v are conventional and worth adopting immediately — without them, name is ambiguous the moment two tables both have one, and Postgres will tell you so.

LEFT JOIN keeps every row from the left table whether or not it matched, filling the right-hand columns with NULL when it did not. That is how you answer "which cities has nobody visited?" — a question an INNER JOIN structurally cannot answer, because the rows you are looking for are the ones it deletes:

SELECT c.name
FROM cities c
LEFT JOIN visits v ON v.city_id = c.id
WHERE v.id IS NULL
ORDER BY c.name;

   name
-----------
 Hobart
 Reykjavik
(2 rows)

Read that WHERE v.id IS NULL carefully: visits.id is NOT NULL, so a NULL there cannot have come from a real visit row. It can only mean "the LEFT JOIN invented this blank right-hand side because nothing matched". That idiom is called an anti-join and you will use it constantly.

-- (a) WHERE: unmatched cities get NULL columns, then WHERE throws them away.
--     rating = NULL is unknown, not true. 5 rows. Hobart and Reykjavik gone.
SELECT c.name, v.visitor FROM cities c
LEFT JOIN visits v ON v.city_id = c.id
WHERE v.rating = 5;

-- (b) ON: the condition decides what counts as a match. Unmatched left rows
--     are still kept. 10 rows - the 5 above, plus all 5 cities with no
--     5-star visit, each once, with visitor NULL.
SELECT c.name, v.visitor FROM cities c
LEFT JOIN visits v ON v.city_id = c.id AND v.rating = 5
ORDER BY c.name;

The other three, in one sentence each. RIGHT JOIN is a LEFT JOIN with the tables the other way round; write LEFT and reorder your tables instead, so every join in your codebase reads the same direction. FULL OUTER JOIN keeps unmatched rows from both sides — genuinely useful roughly once a year, when reconciling two systems. CROSS JOIN pairs every left row with every right row and is almost always a mistake you made by forgetting an ON, which is how a 1,000-row table and a 1,000-row table produce a million rows and a very quiet server.

Many-to-many, via a join table

A city has many themes; a theme belongs to many cities. Neither table can hold a column for the other — you would need a list in a cell, which relational tables do not do. The answer is a third table whose only job is to hold pairs: city_themes, with a two-column primary key so the same pair cannot be recorded twice.

SELECT c.name, t.label
FROM cities c
JOIN city_themes ct ON ct.city_id = c.id
JOIN themes t       ON t.id = ct.theme_id
WHERE t.label = 'coastal'
ORDER BY c.name;

    name    |  label
------------+---------
 Hobart     | coastal
 Lisbon     | coastal
 Valparaiso | coastal
(3 rows)

Two joins to cross one relationship: cities to the join table, join table to themes. JOIN with no word in front means INNER JOIN — that is the default, and it is why Reykjavik, which has no themes at all, never appears. Remember this shape: Module 04 needs exactly it for place↔tag, and Module 05's ORM will hide it behind an attribute you can write in one line, which is precisely why you are typing it out by hand today.

GROUP BY and aggregates rows to summaries

What

Collapsing many rows into one row per group, with a function like count or avg summarising each group.

Where

Every dashboard, every "3 reviews, 4.2 average" badge, every report anyone ever asks you for.

When

Whenever the answer is a number per category rather than a list of rows.

How

GROUP BY names the categories; the aggregate functions describe each one; HAVING filters the groups afterwards.

count(*)        -- how many rows in this group
count(rating)   -- how many rows have a NON-NULL rating
sum(rating)     -- total
avg(rating)     -- mean, ignoring NULLs
min(visited_on) / max(visited_on)   -- earliest / latest

count(*) and count(column) are different questions. The first counts rows. The second counts rows where that column has a value — aggregates skip NULLs, always, without warning:

SELECT count(*) AS total, count(rating) AS rated, round(avg(rating), 2) AS avg_rating
FROM visits;

 total | rated | avg_rating
-------+-------+------------
    15 |    13 |       4.08
(1 row)

Fifteen visits, thirteen ratings, and the average is over thirteen — not fifteen with the blanks counted as zero. That is almost always what you want, but it is never what a beginner assumes, and "our average rating is suspiciously high" bug reports live here.

SELECT c.name,
       count(v.id)              AS visits,
       round(avg(v.rating), 2)  AS avg_rating
FROM cities c
LEFT JOIN visits v ON v.city_id = c.id
GROUP BY c.name
HAVING count(v.id) >= 2
ORDER BY count(v.id) DESC, c.name;

    name    | visits | avg_rating
------------+--------+------------
 Kyoto      |      4 |       4.67
 Lisbon     |      3 |       4.67
 Osaka      |      2 |       3.50
 Porto      |      2 |       3.50
 Tbilisi    |      2 |       5.00
 Valparaiso |      2 |       3.00
(6 rows)

Kyoto shows 4 visits but averages 4.67 — three ratings of 5, 5, 4, and one NULL that avg ignored. Tbilisi shows 2 visits and a clean 5.00 from its single rated one. Note also count(v.id), not count(*): with a LEFT JOIN, an unmatched city still produces one row, so count(*) would report Hobart as having 1 visit. Counting a column from the right table gives the honest 0.

FROM        pull the rows
WHERE       throw away ROWS          <- before grouping, sees individual rows
GROUP BY    pile the survivors into groups
HAVING      throw away GROUPS        <- after grouping, sees aggregates
SELECT      compute the output columns
ORDER BY    sort

SELECT c.country, count(*) AS visit_count
FROM cities c
JOIN visits v ON v.city_id = c.id
WHERE v.visited_on >= '2024-01-01'      -- drops 2 visits from 2023
GROUP BY c.country
HAVING count(*) >= 3                    -- drops Georgia and Chile, 2 each
ORDER BY visit_count DESC;

 country  | visit_count
----------+-------------
 Portugal |           5
 Japan    |           4
(2 rows)

One more rule the database will enforce for you: every column in the SELECT list must either appear in GROUP BY or be wrapped in an aggregate. Otherwise Postgres asks the fair question — Kyoto has four visits, so which visitor's name should it print? The error reads column "v.visitor" must appear in the GROUP BY clause or be used in an aggregate function. MySQL historically guessed instead of asking. Postgres refusing is a feature.

Indexes and EXPLAIN ANALYZE why you are here

This is the section most beginner SQL material skips, and it is the one that separates "I can write a query" from "I can fix a slow endpoint". Read it twice.

What

An index is a separate sorted structure the database maintains alongside a table, so it can jump straight to matching rows instead of reading every one.

Where

Automatically on primary keys and unique constraints; everywhere else, only because you created it.

When

On columns you filter, join, or sort by — once you have measured, not before.

How

CREATE INDEX, then EXPLAIN ANALYZE the same query before and after and compare what the planner actually did.

The mental picture. A table is a pile of rows in no particular order. Finding visitor = 'ana' means reading all of them — a sequential scan. A B-tree index on visitor is a sorted map from value to row location, so the database descends a few levels of tree and reads only the rows it needs — an index scan. It is the index at the back of a book versus reading the book.

An index is never free. Beginners index everything and then wonder why writes got slow. Three costs, all real:

• Every write pays. An INSERT must add an entry to every index on the table; an UPDATE to an indexed column must move it. A table with eight indexes writes far slower than the same table with two.
• It costs disk and memory. An index on a wide text column can approach the size of the table, and it competes with the table for cache space — so an unused index actively slows down the queries that would otherwise have been cached.
• The planner may ignore it. An index that no query uses is pure cost. Indexes must be chosen against the queries you actually run, which is exactly why Module 04 designs the schema from the queries.
• They do not fix bad queries. If your endpoint runs one query per item in a loop, an index makes each of the 200 queries fast and the endpoint still terrible. Fix the shape first, then the index.

Reading a plan

EXPLAIN shows the plan the planner chose. EXPLAIN ANALYZE actually runs the query and adds what really happened — which is what you want, with one caution: it runs the query, so on an UPDATE or DELETE it really does update or delete. Wrap those in BEGIN; … ROLLBACK;.

cityguide=# EXPLAIN ANALYZE SELECT * FROM visits WHERE visitor = 'ana';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Seq Scan on visits  (cost=0.00..1.19 rows=5 width=63)
                     (actual time=0.014..0.019 rows=5 loops=1)
   Filter: (visitor = 'ana'::text)
   Rows Removed by Filter: 10
 Planning Time: 0.087 ms
 Execution Time: 0.033 ms

Your exact numbers will differ — timings depend on your machine and are different on every run. The shape will not. Read it as five facts:

• Seq Scan on visits — the node type. It read the whole table. Index Scan using … would mean it used an index; Bitmap Heap Scan is a middle path for "many matches, but not most of the table".
• cost=0.00..1.19 is the planner's estimate in arbitrary units — startup cost before the first row, then total. It is a currency for comparing plans, not milliseconds. Never quote it as a time.
• rows=5 (estimate) versus actual … rows=5 is the single most valuable comparison in the whole output. When those two diverge wildly — estimate 12, actual 400,000 — the planner chose its strategy on bad information, and running ANALYZE tablename; to refresh statistics fixes more slow queries than any index does.
• actual time=0.014..0.019 is per loop, in milliseconds: time to the first row, then to the last. Multiply by loops to get the real total for that node — a node costing 0.2 ms with loops=5000 is a one-second query.
• Rows Removed by Filter: 10 is the smell. Reading 15 rows to keep 5 is fine. Reading 4,000,000 to keep 5 is the exact thing an index exists to prevent.

Now make the planner change its mind

Create the index and re-run the same query:

CREATE INDEX visits_visitor_idx ON visits (visitor);
-- CREATE INDEX

EXPLAIN ANALYZE SELECT * FROM visits WHERE visitor = 'ana';
-- still: Seq Scan on visits

So give it a large table. This inserts 200,000 rows, tells Postgres to refresh its statistics, and asks for one of them:

INSERT INTO visits (city_id, visitor, visited_on, rating)
SELECT 1 + (g % 6),
       'bulk' || g,
       DATE '2024-01-01' + (g % 365),
       1 + (g % 5)
FROM generate_series(1, 200000) AS g;
-- INSERT 0 200000

ANALYZE visits;          -- refresh the planner's statistics. Do not skip this.

EXPLAIN ANALYZE SELECT * FROM visits WHERE visitor = 'bulk123456';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Index Scan using visits_visitor_idx on visits  (cost=0.42..8.44 rows=1 width=45)
                                                (actual time=0.041..0.043 rows=1 loops=1)
   Index Cond: (visitor = 'bulk123456'::text)
 Planning Time: 0.14 ms
 Execution Time: 0.06 ms

Seq Scan became Index Scan, and Filter became Index Cond — that swap is the tell. A Filter means rows were read and then rejected; an Index Cond means they were never read at all. Now drop the index and run it once more to feel the difference on 200,000 rows, then put it back:

DROP INDEX visits_visitor_idx;
EXPLAIN ANALYZE SELECT * FROM visits WHERE visitor = 'bulk123456';
-- Seq Scan, with "Rows Removed by Filter: 200014" and a much larger Execution Time

CREATE INDEX visits_visitor_idx ON visits (visitor);
DELETE FROM visits WHERE visitor LIKE 'bulk%';   -- DELETE 200000, back to 15 rows
ANALYZE visits;

Build this

Verify it yourself

Run each of these from your project folder. Every one is pass/fail on its own — if a number differs, something earlier did not land, and finding out which is the exercise.

$ docker compose exec db psql -U cityguide -d cityguide -c "\dt"
            List of relations
 Schema |    Name     | Type  |   Owner
--------+-------------+-------+-----------
 public | cities      | table | cityguide
 public | city_themes | table | cityguide
 public | themes      | table | cityguide
 public | visits      | table | cityguide
(4 rows)
                                     # four tables, exactly these names

$ docker compose exec db psql -U cityguide -d cityguide \
    -c "SELECT count(*) FROM cities;" -c "SELECT count(*) FROM visits;"
 count
-------
     8
(1 row)

 count
-------
    15
(1 row)
                                     # 8 cities and 15 visits, or your seed is wrong

$ docker compose exec db psql -U cityguide -d cityguide \
    -c "SELECT count(*) FROM visits WHERE rating = NULL;"
 count
-------
     0                               # zero. Not an error. This is the trap.

$ docker compose exec db psql -U cityguide -d cityguide \
    -c "SELECT count(*) FROM visits WHERE rating IS NULL;"
 count
-------
     2                               # the same question, asked correctly

$ docker compose exec db psql -U cityguide -d cityguide \
    -c "SELECT c.name FROM cities c LEFT JOIN visits v ON v.city_id = c.id
        WHERE v.id IS NULL ORDER BY c.name;"
   name
-----------
 Hobart
 Reykjavik
(2 rows)
                                     # the anti-join: cities nobody visited

$ docker compose exec db psql -U cityguide -d cityguide \
    -c "SELECT count(*) FROM cities c LEFT JOIN visits v
          ON v.city_id = c.id WHERE v.rating = 5;" \
    -c "SELECT count(*) FROM cities c LEFT JOIN visits v
          ON v.city_id = c.id AND v.rating = 5;"
 count
-------
     5                               # filter in WHERE  -> became an INNER JOIN
(1 row)

 count
-------
    10                               # filter in ON     -> stayed a LEFT JOIN
(1 row)

$ docker compose exec db psql -U cityguide -d cityguide \
    -c "INSERT INTO visits (city_id, visitor, visited_on, rating)
        VALUES (999, 'zoe', '2025-06-01', 4);"
ERROR:  insert or update on table "visits" violates foreign key constraint "visits_city_id_fkey"
DETAIL:  Key (city_id)=(999) is not present in table "cities".
                                     # the deliberate break. An error here is a PASS.

$ docker compose exec db psql -U cityguide -d cityguide \
    -c "EXPLAIN ANALYZE SELECT * FROM visits WHERE visitor = 'ana';"
                                     # must contain the line "Seq Scan on visits"
                                     # and "Rows Removed by Filter: 10"
                                     # timings will differ from anyone else's - that is fine

Prove it

Write your answer to each in notes/module-03.md, in your own words, before opening the answer. Where yours differs, work out which of you is right — that gap is the module.

1. What is the difference between WHERE and HAVING, and why can you not use count(*) in WHERE?

2. WHERE rating = NULL returned zero rows and no error. Explain both halves of that.

3. You write a LEFT JOIN and then filter on a right-table column in WHERE. What have you actually written?

4. Why not create an index on every column, given that indexes make reads faster?

5. Your query works and returns the right answer. Why would you ever run EXPLAIN ANALYZE on it?

6. visits.city_id uses ON DELETE CASCADE and city_themes.theme_id uses ON DELETE RESTRICT. Why different choices in the same schema?