SQL & queries

Before you can query anything you need a database with some data in it. This first block builds a tiny shop entirely in memory: two tables (customers and orders) and a handful of rows. Every later lab reuses the cur object created here, so run this one first.

1. sqlite3.connect(":memory:") opens a brand-new SQL database that lives in RAM (nothing is written to disk). conn.cursor() gives you cur, the handle you send SQL through.
2. CREATE TABLE defines the shape of a table. customers gets an id marked PRIMARY KEY (a unique row identifier), a required name (NOT NULL = can't be empty), and an optional city. In orders, customer_id REFERENCES customers(id) is a foreign key — it says each order belongs to a customer.
3. executemany("INSERT INTO ... VALUES (?,?,?)", [...]) adds many rows at once. Each ? is a placeholder filled in from the tuples, so 4 customers and 6 orders get inserted safely.
4. conn.commit() saves the changes. The final print runs two SELECT COUNT(*) queries to count the rows in each table and confirm the seed worked.

What the output means: It prints customers: 4 | orders: 6 — proof the two tables exist and hold the rows you inserted.

Try this: Add a fifth customer to the first executemany list and re-run — the count becomes customers: 5. This seeded data is the shop every other lab queries.

This is the most common thing you'll ever do in SQL: read rows back out. A SELECT reads like a sentence — pick these columns, from this table, keep only rows where a condition holds, and sort the result.

1. SELECT name, city FROM customers asks for just two columns out of the customers table (not the whole row).
2. WHERE city = ? keeps only the rows whose city matches — the ? is filled by the tuple ("Seattle",), so it means where city = 'Seattle'. ORDER BY name then sorts those rows alphabetically.
3. The for row in cur.execute(...) loop walks the matching rows one at a time; each row is a tuple like ('Ava', 'Seattle').
4. The second query, WHERE amount >= 50, keeps orders of 50 or more. .fetchall() grabs all matching rows at once into a list instead of looping.

What the output means: First the two Seattle customers print (Ava, then Cy, sorted by name), then the big orders: the three orders whose amount is at least 50.

Try this: Change the city to "Austin" in the tuple, or lower the threshold to amount >= 20, and predict how many rows come back before running.

Real data is split across tables — customers in one, their orders in another — to avoid repeating information. A JOIN stitches them back together by matching a shared column. This lab shows the two joins you'll use 95% of the time: INNER and LEFT.

1. In the first query, FROM orders o JOIN customers c ON c.id = o.customer_id pairs each order with its customer. The letters o and c are short aliases so you can write o.amount and c.name. A plain JOIN is an INNER join: rows survive only if a match exists on both sides.
2. ORDER BY o.amount DESC LIMIT 3 sorts orders biggest-first (DESC = descending) and keeps just the top 3.
3. The second query uses LEFT JOIN: it keeps every customer even if they have no orders. COUNT(o.id) AS n counts each customer's orders (0 for someone with none), and GROUP BY c.id makes that count per-customer.
4. AS n just renames the counted column to n so the output is tidy.

What the output means: First the top 3 orders by amount with the buyer's name. Then orders-per-customer — note ('Dee', 0): Dee bought nothing, so only the LEFT JOIN reveals her. An INNER join would have dropped her entirely.

Try this: Change LEFT JOIN back to plain JOIN and re-run — Dee disappears from the results. That difference is the whole point of LEFT vs INNER.

GROUP BY answers questions like how much did each city spend? It folds many rows into one row per group and lets you summarize each group with an aggregate function like COUNT, SUM, or AVG.

1. The query joins orders to customers (so every order knows its city), then GROUP BY c.city collapses all rows into one row per city.
2. For each city group, COUNT(*) AS n_orders counts its orders and ROUND(SUM(o.amount),2) AS revenue adds up the amounts (rounded to 2 decimals). These aggregates only make sense because the rows are grouped.
3. HAVING revenue > 50 filters the groups after aggregating. This is the key distinction: WHERE filters individual rows before grouping, HAVING filters the summarized groups after.
4. ORDER BY revenue DESC then lists the highest-revenue city first.

What the output means: Two rows, one per city: ('Seattle', 4, 210.0) and ('Austin', 2, 80.0) — city, its order count, and its total revenue. Both clear the HAVING bar of 50.

Try this: Change HAVING revenue > 50 to > 100 and Austin drops out. Then try grouping by c.name instead of c.city to get per-customer totals.

This lab answers one question — which customers spent more than the average customer? — two ways: first as nested subqueries, then as CTEs. Compare them to see why CTEs are the readable, professional style. A subquery is just a query used inside another query.

1. The nested version reads inside-out. The deepest part, SELECT SUM(amount) ... GROUP BY customer_id, gets each customer's total; wrapping it in SELECT AVG(t) FROM (...) gets the average of those totals.
2. The middle layer keeps customer_ids whose SUM(amount) beats that average (HAVING SUM(amount) > (...)), and the outer WHERE id IN (...) turns those ids into names. It works, but you must read it from the innermost parentheses out.
3. The second version uses a CTE — WITH per_customer AS (...) — which names a subquery so you can refer to it by name later. avg_total AS (...) names a second step that reuses the first.
4. Now the final SELECT reads top-to-bottom like plain steps: build per-customer totals, compute the average, then keep customers above it. Same answer, far clearer.

What the output means: subquery: ['Ava'] | CTE: ['Ava'] — both paths return the same customer (Ava), proving the CTE is just a tidier way to write identical logic.

Try this: Read only the CTE version and trace it top-down; then read the subquery version inside-out. The CTE is the same logic but you can follow it like a recipe — that's why pros prefer it.

Window functions are the single most valuable analytics skill in SQL. Unlike GROUP BY (which collapses rows into one summary row), a window function computes across related rows while keeping every original row — so you get running totals and rankings alongside the raw data.

1. Every window function has an OVER (...) clause that defines its "window" — the set of rows it looks at. PARTITION BY c.id restarts the calculation for each customer (it's like GROUP BY but doesn't merge rows), and ORDER BY o.created sets the order within that window.
2. SUM(o.amount) OVER (PARTITION BY c.id ORDER BY o.created) AS running gives a running total: each row shows that customer's spending accumulated up to and including that order.
3. RANK() OVER (ORDER BY o.amount DESC) AS rk ranks every order globally by amount, biggest = rank 1.
4. LAG(o.amount) OVER (PARTITION BY c.id ORDER BY o.created) AS prev reaches back to the previous row in the window — the customer's prior order amount (None on their first order, since there's nothing before it).

What the output means: One row per order (rows are kept, not collapsed): name, amount, the customer's running total, the global rank, and the previous amount. E.g. Ava's three orders show a running total climbing 40 → 100 → 120, and prev lagging one step behind.

Try this: Swap RANK() for ROW_NUMBER() and watch how ties are handled differently. Then remove PARTITION BY c.id from the running SUM to make it a single global running total instead of per-customer.

Expert SQL is knowing why a query is slow and fixing it. EXPLAIN QUERY PLAN asks the database to describe how it will run a query without actually running it — specifically, whether it reads every row or jumps straight to the ones it needs.

1. The first EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1 reports the plan before any index exists. plan_before[0][-1] pulls the human-readable description out of the result.
2. CREATE INDEX idx_orders_customer ON orders(customer_id) builds an index — a sorted lookup structure on the customer_id column, like the index at the back of a book.
3. Running the exact same EXPLAIN QUERY PLAN again shows the plan after the index exists. The database now chooses a faster strategy automatically.
4. The lesson: a SCAN reads every row (slow, O(n)); a SEARCH USING INDEX jumps straight to matches (fast, O(log n)). Same query, very different speed on big tables.

What the output means: before: SCAN orders then after: SEARCH orders USING INDEX idx_orders_customer (customer_id=?) — visible proof the index turned a full scan into a targeted lookup.

Try this: Add an index on a column you never filter by and re-check the plan — it stays a SCAN, showing an index only helps the columns you actually query. Index the columns in WHERE/JOIN/ORDER BY, not everything.

Some changes must happen all together or not at all. Moving money is the classic case: you subtract from one account and add to another — if only half runs, money vanishes. A transaction groups steps so they commit together or roll back together.

1. The setup creates an accounts table with two accounts (account 1 has 100, account 2 has 0) so we have something to transfer between.
2. Inside transfer, db.execute("BEGIN") starts a transaction. The two UPDATE statements subtract from the source and add to the destination — the two halves that must stay in sync.
3. The check if bal < 0: raise ValueError(...) catches an overdraft. If the source would go negative, raising an error jumps to the except block.
4. On success, db.commit() makes both updates permanent. On any error, db.rollback() undoes both updates — the account balances snap back as if nothing happened. That's atomicity, the 'A' in ACID.

What the output means: transfer 30: True (the valid move commits), transfer 999: False (overdraft rolls back), and final balances [(1, 70.0), (2, 30.0)] — only the 30 transfer stuck; the failed 999 left the accounts untouched.

Try this: Comment out the db.rollback() line and re-run the 999 transfer — now the first UPDATE lands but the second doesn't, and money is lost. That broken state is exactly what a transaction exists to prevent.