Module 3 · Postgres & Data · Drills

Drills: SQL

Reading SQL is not writing SQL. Type every query yourself against a real database before you reveal the solution — the effortful recall is the whole point.

How to use this page Run these against a real Postgres so you see actual rows. Fastest local option: install Postgres and use psql (or the free pgAdmin GUI). No install? Use an online playground like DB Fiddle (pick Postgres) or SQLite Online. Assume the users and documents schema from the deep-dive. Attempt each drill first, run it, then click “Show solution”. Tick each box as you go.

A · Warm-up reps Basic

Drill 1 select

From documents, return the title and created_at of documents owned by user 1, newest first, capped at 5 rows.

Show solution 
SELECT title, created_at
FROM documents
WHERE owner_id = 1
ORDER BY created_at DESC
LIMIT 5;

Clause order is fixed: SELECT … FROM … WHERE … ORDER BY … LIMIT. Postgres rejects them out of order.

Drill 2 insert

Insert a new document owned by user 1 with title 'Q3 Notes', and have the database hand back the new row's id.

Show solution 
INSERT INTO documents (owner_id, title)
VALUES (1, 'Q3 Notes')
RETURNING id;

Columns you omit (like created_at) take their DEFAULT. RETURNING is the Postgres way to get the generated id in one round-trip.

Drill 3 update

Rename the document with id = 2 to 'Q3 Notes (final)'. Make sure you change only that one row.

Show solution 
UPDATE documents
SET title = 'Q3 Notes (final)'
WHERE id = 2;

Write the WHERE before the SET in your head — an UPDATE with no WHERE rewrites the entire table.

B · Stretch Intermediate

Drill 4 join

List each document's title next to its owner's name. Only include documents that actually have a valid owner.

Show solution 
SELECT d.title, u.name AS owner
FROM documents d
INNER JOIN users u ON u.id = d.owner_id;

"Only matching rows" means INNER JOIN. The ON clause links the foreign key to the primary key; d and u are aliases.

Drill 5 group by

For every user id that owns documents, return how many documents they own, as doc_count, busiest user first.

Show solution 
SELECT owner_id, COUNT(*) AS doc_count
FROM documents
GROUP BY owner_id
ORDER BY doc_count DESC;

GROUP BY owner_id makes one bucket per user; COUNT(*) counts the rows in each bucket.

Drill 6 index

Create an index that speeds up filtering and joining documents by owner_id.

Show solution 
CREATE INDEX idx_documents_owner
ON documents (owner_id);

Foreign-key columns are the textbook place to index — you filter and join on them constantly. Verify it's used with EXPLAIN SELECT * FROM documents WHERE owner_id = 1; (look for "Index Scan").

C · Build challenge Build

Mini-project Write the "top 3 users by document count" query — a leaderboard of who's uploaded the most. This is the exact shape of analytics you'll put on a DocChat dashboard, and a very common interview ask.

Build · top 3 uploaders

Return each user's name and their document count, ordered highest first, limited to the top 3. Use the users and documents tables.

Show solution 
SELECT u.name, COUNT(d.id) AS doc_count
FROM users u
JOIN documents d ON d.owner_id = u.id
GROUP BY u.id, u.name
ORDER BY doc_count DESC
LIMIT 3;

Note the join brings in the name, the GROUP BY buckets per user, COUNT tallies their docs, and ORDER BY … LIMIT 3 picks the leaders. Want users with zero docs to appear (count 0)? Swap JOIN for LEFT JOIN — then COUNT(d.id) correctly returns 0 for them.

D · Rapid recall Flashcards

Click a card to flip it. Say the answer out loud before you flip — that's the rep that builds storage strength.

What is a PRIMARY KEY?
The column that uniquely identifies each row — unique and never null. Usually an auto-incrementing id.
click to flip
What is a FOREIGN KEY?
A column that references another table's primary key, enforcing that the value points to a real, existing row.
click to flip
INNER vs LEFT JOIN?
INNER keeps only matched rows; LEFT keeps every left-table row, filling unmatched right columns with NULL.
click to flip
What does an index do?
A sorted B-tree on a column so the database finds rows without scanning the whole table — faster reads, slightly slower writes.
click to flip
What does GROUP BY do?
Splits rows into buckets by a column, so aggregates like COUNT/SUM run once per bucket instead of over all rows.
click to flip
Transaction & ROLLBACK?
A BEGIN…COMMIT block is all-or-nothing; ROLLBACK discards every change since BEGIN, as if it never ran.
click to flip

E · Self-check before moving on

Tick each only if you can do it without looking:

Next All ticked? You can speak to the database directly. Next we drive it from Python the modern way — typed models, sessions, and schema migrations: Lesson 3.2 — SQLAlchemy 2.0 & Migrations.