Week 4 · Session 1 — Asking a database questions

Last week you learned to keep data alive. Today you learn to ask it questions.

Week 3 ended with a database in a container and a named volume, and the point of that volume was that the data outlives the container. Today that data is the subject.

One command starts everything. If Docker is not running, start it first — the whale or the OrbStack icon in your menu bar.

You do

docker run -d --name pg \
  -e POSTGRES_PASSWORD=secret \
  -v pgdata:/var/lib/postgresql/data \
  -p 5432:5432 postgres:17

docker exec pg pg_isready -U postgres

You want accepting connections. Hands up when you see it.

Today, in four parts

Why a database at all — you already have files and Python. What does this buy you.
Asking for rows — SELECT, WHERE, ORDER BY, and the four clauses that cover most days.
Asking for summaries — counting, grouping, and the four kinds of join, including the one that finds what is missing.
Making it fast — what the database is actually doing, and the one command that shows you.

By the end you will query half a million rows, get an answer in under a millisecond, and be able to explain why it was slow before you fixed it.

You have CSV files and you have Python. Why would you want a database?

The four things a database gives you that a folder of files does not

	what it means	why you care
A query language	say what you want, not how to fetch it	one line replaces a loop, a filter and a sort
Indexes	a lookup structure kept beside the data	find one row in half a million without reading them all
Constraints	rules the data cannot break	an order cannot reference a customer who does not exist
Transactions	all of it happens, or none of it does	money leaves one account and arrives in the other

The first row is the mental shift. In Python you write how — open the file, loop, compare, append. In SQL you write what — the rows where the status is ready — and the database decides how to get them. It often chooses better than you would.

Three tables, and the shape of the data you will be asking about

Your database has a coffee shop in it — the same one from Week 2, now with history.

customers            drinks               orders
  id                   id                   id
  name                 name                 customer_id  ---> customers.id
  city                 price_inr            drink_id     ---> drinks.id
  joined_on            is_hot               size
                                            status
     120 rows              8 rows           placed_at
                                              500,000 rows

orders does not repeat the customer's name or the drink's price. It stores an id that points at the row that does. That is the whole idea behind splitting data into tables: each fact is written down exactly once, in one place, and referred to from everywhere else.

Four minutes. Get inside the database and look around.

docker exec -it pg psql -U postgres

Your prompt becomes postgres=#. You are now inside the database, not in your shell.

\dt

SELECT * FROM drinks;

SELECT count(*) FROM orders;

\dt lists the tables. Backslash commands are psql's own — they are not SQL.

To get out: \q

Hands up when count(*) gives you 500000.

A query is four clauses, and they always come in this order

SELECT   name, price_inr        -- which columns you want back
FROM     drinks                 -- which table they live in
WHERE    price_inr > 150        -- which rows qualify
ORDER BY price_inr DESC         -- how to sort what survived
LIMIT    3;                     -- how many to actually show

The order is fixed and the database will refuse anything else. You cannot put WHERE before FROM, even though the sentence would still make sense to a human.

SELECT * means every column. It is fine while exploring and a bad habit in real code — you get columns you did not ask for, and the query breaks in new ways when somebody adds one.

The -- is a comment, exactly like # in Python.

What you can put in a `WHERE`

	example
comparison	`price_inr >= 150`	`= < > <= >= <>` — note `<>` not `!=`, though both work
a list	`status IN ('ready','brewing')`	much cleaner than three `OR`s
a range	`price_inr BETWEEN 100 AND 200`	inclusive at both ends
a pattern	`name LIKE '%latte%'`	`%` is any run of characters, `_` is exactly one
missing	`city IS NULL`	never `= NULL` — that is never true, not even for nulls
combining	`WHERE is_hot AND price_inr < 150`	`AND`, `OR`, `NOT`, and brackets

The last two rows cause real bugs. NULL means unknown, so NULL = NULL is not true — the database cannot say two unknown things are equal. IS NULL is the only way to ask.

Six minutes. Four questions, four queries.

Still inside psql. Answer these, one query each:

-- 1. the three most expensive drinks
SELECT name, price_inr FROM drinks ORDER BY price_inr DESC LIMIT 3;

-- 2. every cold drink
-- 3. all customers from Bangalore who joined after 1 March 2026
-- 4. the 5 most recent orders

Number one is done for you. Write two, three and four yourself.

Hints: the columns are is_hot, city, joined_on, placed_at. A date is written '2026-03-01' in quotes.

Read out your answer to number three.

Aggregates collapse many rows into one answer

Everything so far returned rows. These return a number about rows.

SELECT count(*)          FROM orders;          -- 500000
SELECT count(*)          FROM orders WHERE status = 'cancelled';
SELECT avg(price_inr)    FROM drinks;
SELECT min(price_inr), max(price_inr) FROM drinks;
SELECT sum(price_inr)    FROM drinks WHERE is_hot;

Five functions cover almost everything: count, sum, avg, min, max.

count(*) counts rows. count(city) counts rows where city is not null, which is a different number and a genuinely useful distinction — it is how you find out how much of a column is actually filled in.

`GROUP BY` runs the aggregate once per group instead of once overall

SELECT status, count(*)
FROM orders
GROUP BY status
ORDER BY count DESC;

  status   | count
-----------+-------
 collected |  1666
 pending   |   834
 brewing   |   834
 ready     |   833
 cancelled |   833

Read it as: make one bucket per distinct status, then count each bucket.

The rule that catches everyone: every column in your SELECT must either be in the GROUP BY or inside an aggregate. Ask for SELECT status, size, count(*) grouped only by status and the database refuses — it has many sizes per status and no way to pick one.

`WHERE` filters rows. `HAVING` filters groups. That is the entire difference.

SELECT city, count(*) AS customers
FROM customers
GROUP BY city
HAVING count(*) > 20
ORDER BY customers DESC;

WHERE runs before the grouping and decides which rows go into buckets. HAVING runs after and decides which buckets survive. So you cannot put count(*) in a WHERE — at that point nothing has been counted yet.

AS customers renames the output column. Purely cosmetic, and it makes results readable — but remember it is created at SELECT time, which is why you cannot use that alias in the WHERE.

A `JOIN` puts two tables side by side, matched on a shared value

orders knows drink_id. It does not know the drink's name or price — those live in drinks. A join follows the arrow.

SELECT o.id, d.name, o.size
FROM orders o
JOIN drinks d ON d.id = o.drink_id
LIMIT 5;

Three things are happening. FROM orders o nicknames the table o so you can write o.id instead of orders.id. JOIN drinks d brings in the second table. ON d.id = o.drink_id is the matching rule — for each order, find the drink whose id equals this order's drink_id.

Once joined, every column of both tables is available as if it were one wide table.

Five minutes. Join all three tables and answer a real business question.

Which drinks made the most money?

SELECT d.name,
       count(*)          AS orders,
       sum(d.price_inr)  AS revenue_inr
FROM orders o
JOIN drinks d ON d.id = o.drink_id
WHERE o.status = 'collected'
GROUP BY d.name
ORDER BY revenue_inr DESC
LIMIT 5;

Type it, run it, then look at the two number columns and tell me what is strange.

Then, on your own: which city has spent the most? You will need customers as well — that is one more JOIN on o.customer_id.

A foreign key is a promise the database keeps for you

orders.customer_id is not just a number that happens to match. It is declared as a foreign key pointing at customers.id, and the database enforces it:

INSERT INTO orders (customer_id, ...) VALUES (99999, ...);

ERROR:  insert or update on table "orders" violates foreign key constraint
DETAIL:  Key (customer_id)=(99999) is not present in table "customers".

You cannot record an order for a customer who does not exist. You also cannot delete a customer who still has orders — not without saying what should happen to them.

That is the difference between a spreadsheet and a database. In a spreadsheet, VLOOKUP points at a range and quietly returns #N/A when it breaks. Here, the broken row never gets written in the first place.

Two tiny tables, so you can see exactly what each join does

Everything on the next slide uses these. Three customers, four orders — and two deliberate mismatches.

customers                    orders
 id | name                    id | customer_id | drink
----+-------                 ----+-------------+----------
  1 | Aarav                   10 |           1 | latte
  2 | Priya                   11 |           1 | mocha
  3 | Rohan                   12 |           2 | espresso
                              13 |          99 | walk-in

Rohan has never ordered anything. And order 13 is a walk-in, recorded against customer 99, who does not exist.

Those two rows are the whole point. Every join type differs only in what it does with them — and if your data has no mismatches, every join gives the same answer and you learn nothing.

Four joins, one question: what happens to the rows that do not match?

	keeps	result on our two tables
`INNER JOIN`	only rows matching on both sides	3 rows — Rohan and the walk-in both vanish
`LEFT JOIN`	everything on the left, nulls where nothing matched	4 rows — Rohan appears with a null drink
`RIGHT JOIN`	everything on the right	4 rows — the walk-in appears with a null name
`FULL JOIN`	everything from both	5 rows — Rohan and the walk-in

JOIN on its own means INNER JOIN. That is the default, and it is the one that silently throws rows away.

Left and right are about the order you wrote the tables in, nothing else. A LEFT JOIN B and B RIGHT JOIN A give identical results — which is why almost nobody writes RIGHT JOIN. Put the table you care about first and use LEFT.

The most useful join in practice is the one that finds what is missing

LEFT JOIN plus WHERE ... IS NULL gives you everything on the left that has no match on the right. It has a name — an anti-join — and once you have seen it you will use it constantly.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

 name
-------
 Rohan

Customers who have never ordered. Products never sold. Employees with no manager. Invoices with no payment. Every one of those questions is this exact shape.

The WHERE runs after the join, so it is filtering the null rows that LEFT JOIN created — which is why it cannot be done with an INNER JOIN.

Two more you should recognise, and one you should fear

CROSS JOIN pairs every row with every row. Three customers and four orders gives twelve rows. Occasionally useful — generating every size for every drink, filling a calendar — and almost always a mistake when you did not ask for it.

SELF JOIN is a table joined to itself, with two aliases. Employees and their managers, customers in the same city, a row and the row before it.

SELECT a.name, b.name AS also
FROM customers a
JOIN customers b ON a.city = b.city AND a.id < b.id;

And the one to fear: leave the ON off a join and you get a cross join, silently.

FROM customers, orders        -- 120 x 500,000 = 60,000,000 rows

Half a million rows, and one question: how does it find them?

Everything so far has been about what to ask. This block is about what the database does when you ask it — because on 500,000 rows, the difference between a good query and a bad one stops being theoretical.

EXPLAIN ANALYZE
SELECT count(*) FROM orders
WHERE placed_at BETWEEN '2026-01-05 09:00' AND '2026-01-05 09:30';

EXPLAIN shows the plan the database intends to use. EXPLAIN ANALYZE actually runs it and reports what really happened, with real timings.

It is the single most useful command in this session, and almost nobody learns it until something is already on fire.

The plan says it read 166,066 rows in order to throw them away

Real output, on this table, with no index:

->  Parallel Seq Scan on orders  (actual time=4.665..7.426 rows=600 loops=3)
      Rows Removed by Filter: 166066
Execution Time: 9.578 ms

Seq Scan means sequential scan: start at row one, look at every single row, keep the ones that match. Rows Removed by Filter is the confession — a hundred and sixty-six thousand rows were read from disk, checked, and discarded, per worker.

That is not a bug. With no index there is genuinely no other way: the database has no idea where January the fifth lives, so it has to look everywhere.

One index, and the same query is fifty-six times faster

CREATE INDEX idx_orders_time ON orders(placed_at);

The same query, re-run, verbatim output:

->  Bitmap Index Scan on idx_orders_time  (actual time=0.055..0.055 rows=1801 loops=1)
      Index Cond: (placed_at >= '2026-01-05 09:00:00' AND placed_at <= '2026-01-05 09:30:00')
Execution Time: 0.170 ms

9.578 ms became 0.170 ms. But the timing is not the lesson — the plan changed. Seq Scan became Index Scan, and Rows Removed by Filter vanished completely, because the database no longer reads rows it does not want.

An index is a sorted structure kept beside the table. Think of the index at the back of a textbook: you do not read the book to find a word.

Indexes are not free, which is why the database does not just index everything

	measured on this table
`orders` table itself	32 MB
index on `placed_at`	11 MB
index on `customer_id`	3.4 MB

One index on a timestamp cost a third of the table's own size. And every INSERT, UPDATE and DELETE now has to update the index as well as the row — writes get slower so that reads get faster.

The payoff is proportional to how much you skip. The same index on a query returning 4,167 rows — a big slice of the table — only gave 3.4×, not 56×, because the database still had to fetch all of those rows. Indexes reward selective questions.

Six minutes. Make a query slow, then make it fast, and prove both.

-- 1. no index on this column yet. Look at the plan.
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- 2. write down two things: the scan type, and Rows Removed by Filter

-- 3. now fix it
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- 4. run the same EXPLAIN ANALYZE again

Say out loud what changed in the plan — not the time, the plan.

Then the real question: drop the index and try WHERE size = 'large'. Does an index help there? Why not?

Five things that make a query slow, in the order you will meet them

	the tell in `EXPLAIN`	the fix
No index on what you filter	`Seq Scan` + big `Rows Removed by Filter`	add an index on that column
`SELECT *` when you need two columns	wide rows, more I/O than needed	name the columns
A function wrapping the column	`Seq Scan` despite an index existing	index the expression, or rewrite
Joining without the join condition	`rows` in the millions, query hangs	check every `JOIN` has an `ON`
Index on a low-variety column	planner ignores your index	do not build it

Row three surprises people. WHERE date(placed_at) = '2026-01-05' cannot use an index on placed_at, because the index stores the raw timestamps and you asked about a function of them. Rewrite it as a range — >= '2026-01-05' AND < '2026-01-06' — and the index works again.

What you can do now that you could not at ten o'clock

You started with two weeks of Python that reads files. Now:

you can ask a database a question in a language designed for asking questions
you can collapse half a million rows into a five-row answer
you can pull three tables together and get a number a business would care about
and when it is slow, you can ask the database why, and get a real answer

That last one is the part that separates people who use a database from people who understand one. EXPLAIN ANALYZE is not an advanced topic. It is the first thing to reach for.

Thursday is the other half of operating a system: not what the data says, but what the software is doing while it runs.

What to read before Thursday

PostgreSQL tutorial — the official one — chapters 1 to 3 repeat today at your own pace. About thirty minutes.

Select Star SQL — a free interactive book. You write real queries against real data in the browser. The best beginner SQL resource that exists.

Use The Index, Luke — indexes and plans, properly explained. Read the first two pages; the rest is there when you need it.

PostgreSQL EXPLAIN docs — how to read a plan, in detail.

Leave pg running. Thursday uses containers again, and your data is in a volume either way.