SQL & queries

1 · Your first database — in Python essential

Almost everyone's first brush with a database goes badly, and it's not their fault. You want to learn SQL, so you follow a guide — and within ten minutes you're being told to install a database server, create a superuser role, edit a config file, and start a background daemon. An hour later you have a broken login prompt and you still haven't run a single SELECT. The environment beat you before the language even got a turn. That barrier stops far more people than the syntax ever does.

Here's the reframe that changes everything: a database is not a server you have to run — it can just be a file, or even a scratchpad in memory. SQLite is a complete SQL engine that lives inside your program. There's no server, no port, no password; you open it much the way you open a file. And Python already ships it as sqlite3, so "set up a database" collapses into one line of code. Think of the difference between renting a warehouse (a full database server — powerful, but paperwork) and grabbing a notebook off your desk (SQLite — instant, yours, and good enough for learning and for a surprising amount of real work).

The mental model to hold onto is small, and it won't change no matter which database you graduate to later. A table is a grid — columns name the fields, rows hold the records — exactly like one tab of a spreadsheet. Our whole chapter runs on a toy shop with just two tables: customers (who) and orders (what they bought), linked by a shared id. Every query you write from here on is a question aimed at that grid, so it pays to keep the grid picture in your head.

The one common mistake to name up front: a beginner sees :memory: and quietly assumes the data is being saved somewhere. It isn't. An in-memory database vanishes the instant the program ends — which is perfect for a tutorial (every run starts clean, nothing to reset) but is exactly the trap that loses a Saturday's work if you mistake it for storage. When you want data to survive, you pass a filename instead of :memory: and SQLite writes a real file to disk. Same code, same SQL — only where the bytes live changes.

We use sqlite3 with an in-memory database so every example is self-contained. Our model: a tiny shop with customers and orders. Run this first — the later labs build on this cur.

setup_db.pyimport sqlite3
conn = sqlite3.connect(":memory:")      # a real SQL database, entirely in RAM
cur = conn.cursor()
cur.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT);
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    amount REAL NOT NULL,
    created TEXT NOT NULL
);
""")
cur.executemany("INSERT INTO customers VALUES (?,?,?)",
    [(1,"Ava","Seattle"),(2,"Ben","Austin"),(3,"Cy","Seattle"),(4,"Dee","Austin")])
cur.executemany("INSERT INTO orders VALUES (?,?,?,?)",
    [(1,1,40.0,"2026-01-05"),(2,1,60.0,"2026-02-01"),(3,2,25.0,"2026-01-20"),
     (4,3,90.0,"2026-02-10"),(5,1,20.0,"2026-03-02"),(6,2,55.0,"2026-03-05")])
conn.commit()
print("customers:", cur.execute("SELECT COUNT(*) FROM customers").fetchone()[0],
      "| orders:", cur.execute("SELECT COUNT(*) FROM orders").fetchone()[0])

customers: 4 | orders: 6

2 · SELECT, WHERE, ORDER BY — reading rows essential

Picture the moment SQL was invented for. A shelf holds ten million index cards — every customer, every order your shop has ever taken. Someone asks: "who are our customers in Seattle, listed alphabetically?" Without SQL you'd write a loop: open a card, check the city, keep it or toss it, sort the survivors by hand. With SQL you say what you want and the engine figures out how to get it. That shift — from how to what — is the whole reason the language feels like magic the first time it clicks.

The trick to reading any SELECT is to hear it as an ordinary English sentence: pick these columns, from this table, keeping only the rows where a condition holds, and sort the result. Four everyday words map onto four clauses — SELECT (which columns), FROM (which table), WHERE (which rows to keep), ORDER BY (how to sort). Once you can translate a query back into that sentence, you can read almost anything.

Here's the detail that quietly trips up beginners and is worth burning in now: SQL does not run in the order you write it. You write SELECT first, but the engine does it nearly last. It first goes to the FROM table, then narrows with WHERE, then decides which columns to hand back (SELECT), and finally sorts with ORDER BY. Think of a chef reading an order ticket: they don't plate the dish (the part the customer sees) until after they've fetched the ingredients and done the cooking. Knowing this execution order explains a puzzle you'll hit soon — why WHERE can't reference a column alias you invented in SELECT: at the moment WHERE runs, that alias doesn't exist yet.

One more habit that separates a beginner from a professional, and it's a security habit as much as a style one: never glue user input into a query string. The instinct is to build the query with an f-string — f"... WHERE city = '{city}'" — and it works right up until someone types a city name containing a quote and an extra command, and now they're running their SQL against your database. That's SQL injection, and it's one of the oldest and most damaging bugs on the web. The fix is painless: leave a ? placeholder in the query and pass the value separately as a tuple, and the driver quotes it safely for you. Every lab below uses ? for exactly this reason.

A query reads like a sentence: select these columns, from this table, where a condition holds, ordered by something. SQLite runs them in the order FROM → WHERE → SELECT → ORDER BY.

select.py# (assumes `cur` from Step 1)
for row in cur.execute(
        "SELECT name, city FROM customers WHERE city = ? ORDER BY name", ("Seattle",)):
    print(row)
print("big orders:", cur.execute("SELECT id, amount FROM orders WHERE amount >= 50").fetchall())

('Ava', 'Seattle')
('Cy', 'Seattle')
big orders: [(2, 60.0), (4, 90.0), (6, 55.0)]

3 · JOINs — combining tables intermediate

Imagine you stored everything in one giant table instead. Every order row would repeat the customer's name, city, email, phone — all of it, copied on every single order. Ava places 500 orders and her city is written 500 times. Then she moves to Denver and you have to find and fix 500 rows, and if you miss three, your database now disagrees with itself about where Ava lives. That mess is exactly what splitting data into separate tables prevents: store each fact once, in the table it belongs to (Ava's city lives in customers, her purchases live in orders), and connect them with a shared id. The formal name for this cleanup is normalization, and it's the whole subject of SD2.

But splitting creates a new problem: the answer to a real question — "what did Ava buy?" — now lives in two tables at once. A JOIN is how you put the pieces back together on demand. The everyday analogy is a coat-check: you hold a numbered ticket (the order's customer_id) and the rack holds coats with matching numbers (each customer's id); the JOIN is the attendant walking the ticket to the coat. It matches rows from two tables wherever a shared column agrees, producing one wider row that has columns from both sides.

Now the part that actually decides whether a query is correct, and it's where most JOIN bugs are born: what happens to rows that have no partner? Suppose a customer signed up but never ordered. An INNER JOIN (a plain JOIN) keeps a row only when a match exists on both sides — so that customer silently disappears from your results. A LEFT JOIN keeps every row from the left table whether or not it found a match, filling the missing right-hand columns with NULL. The choice is not cosmetic. Ask "total revenue per customer" with an INNER JOIN and your zero-order customers vanish; the report looks fine and is quietly wrong. Ask it with a LEFT JOIN and they show up with a revenue of zero, which is the honest answer.

The classic mistake, in one sentence: reaching for a plain JOIN out of habit when the question really needs "…including the ones with nothing on the other side." Whenever a requirement contains the words "all" or "even those without," that's your cue to think LEFT JOIN, not INNER. The diagram and table below make the "which rows survive" rule concrete.

Data is split across tables to avoid duplication (SD2). A JOIN stitches them back on a matching column. The type decides which non-matching rows survive.

join.py# INNER: only customers who have orders
inner = cur.execute("""
    SELECT c.name, o.amount FROM orders o
    JOIN customers c ON c.id = o.customer_id
    ORDER BY o.amount DESC LIMIT 3
""").fetchall()
print("top 3 orders:", inner)

# LEFT: every customer, even those with NO orders (NULL amount)
left = cur.execute("""
    SELECT c.name, COUNT(o.id) AS n
    FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id ORDER BY n
""").fetchall()
print("orders per customer:", left)   # Dee has 0

top 3 orders: [('Cy', 90.0), ('Ava', 60.0), ('Ben', 55.0)]
orders per customer: [('Dee', 0), ('Cy', 1), ('Ben', 2), ('Ava', 3)]

4 · GROUP BY & aggregation intermediate

So far every query has handed you back rows — individual records. But the questions a business actually asks are almost never about one row; they're about summaries. Nobody in a Monday meeting asks "show me order #4,812." They ask "how much did each city spend last quarter?", "how many orders per customer?", "what's our average order value?" Those questions all share a shape: chop the data into piles, then compute one number per pile. That is precisely what GROUP BY plus an aggregate function does, and it's the engine behind every dashboard you've ever seen.

The mental picture that makes it stick: imagine dealing a deck of cards into piles by suit. GROUP BY c.city is the dealing step — it sorts every order into a pile per city. Then an aggregate function is you looking at one pile and reporting a single fact about it: COUNT(*) ("how many cards in this pile?"), SUM(amount) ("add up their values"), AVG(amount) ("the average"). The result has one row per pile, not one row per card — the many collapse into the few. That collapse is the defining feature, and also the source of the classic beginner confusion.

Here's that confusion, and it's worth slowing down for because interviewers love it: the difference between WHERE and HAVING. They both filter, so people use them interchangeably and get mysterious errors. The rule is about timing. WHERE runs before the piles are formed — it decides which individual cards get dealt at all ("only orders from 2026"). HAVING runs after — it decides which finished piles to keep ("only cities whose total exceeds 50"). So a condition on a raw column (a single order's date) belongs in WHERE; a condition on an aggregate (a whole city's total) can only go in HAVING, because that total doesn't even exist until after grouping. Try to filter on SUM(amount) in a WHERE and the database rightly refuses — at that point in execution, there's no sum yet.

A useful discipline that flows from this: reach for WHERE whenever you can, and save HAVING for genuinely post-aggregate conditions. Filtering rows out early (in WHERE) means fewer rows to pile up and add, which is simply less work for the engine. HAVING is not "a second WHERE" — it's the one that speaks the language of groups.

To answer how much did each city spend? group rows and apply an aggregate (SUM, COUNT, AVG). WHERE filters rows before grouping; HAVING filters the groups after.

groupby.pyrows = cur.execute("""
    SELECT c.city, COUNT(*) AS n_orders, ROUND(SUM(o.amount),2) AS revenue
    FROM orders o JOIN customers c ON c.id = o.customer_id
    GROUP BY c.city
    HAVING revenue > 50
    ORDER BY revenue DESC
""").fetchall()
for r in rows: print(r)

('Seattle', 4, 210.0)
('Austin', 2, 80.0)

5 · Subqueries & CTEs advanced

You will one day inherit a query that looks like this and your heart will sink. Six levels of parentheses, opening brackets marching off the right edge of the screen, a SELECT buried so deep inside other SELECTs that you have to count matching parens with your finger to figure out what feeds what. Someone wrote it under deadline, it worked, and now it's yours to change. Reading it feels like reading a sentence with eight nested "which was the thing that…" clauses — technically grammatical, humanly unparseable. This section is about the tool that untangles exactly that.

First the raw idea: a subquery is simply a query used inside another query. That's it — a SELECT whose result feeds the query around it. It might produce a single value ("the average order total"), a list of ids to match against, or a whole temporary table. Subqueries are genuinely useful; the problem is only what happens when you nest them. Each layer wraps the last, so you have to read the thing inside-out — start at the deepest parenthesis and work outward — which is the opposite of how anyone reads anything.

The fix is a Common Table Expression, or CTE: the WITH name AS (...) syntax. A CTE takes a subquery, gives it a name, and lets you refer to that name later. Think of it as the difference between a run-on sentence and a numbered recipe. Instead of "the customers whose total exceeds the average of the totals of all customers grouped by customer," you write step 1: per_customer = each customer's total; step 2: avg_total = the average of those; step 3: keep customers above it. Same logic, but now it reads top-to-bottom, each step building on the named one before it — the way you'd actually explain it to a colleague at a whiteboard.

The mistake to avoid isn't a syntax error — it's a readability debt. Nested subqueries "work," so it's tempting to keep nesting rather than refactor. But every layer you add is a layer the next person (often future-you) has to unwind. The professional rule of thumb: the moment a query nests more than about twice, stop and reach for CTEs. They don't usually run faster — the engine often treats them the same — but they turn a write-once puzzle into something you can read, test a piece at a time, and safely modify. The lab below writes the identical question both ways so you can feel the difference directly.

A subquery is a query inside another. A CTE (WITH … AS) names a subquery so complex logic reads top-to-bottom instead of nesting inward — the professional way to structure big queries.

cte.py# Customers who spent above the average customer total.
# First as a nested subquery:
q1 = cur.execute("""
    SELECT name FROM customers WHERE id IN (
        SELECT customer_id FROM orders GROUP BY customer_id
        HAVING SUM(amount) > (SELECT AVG(t) FROM (
            SELECT SUM(amount) AS t FROM orders GROUP BY customer_id))
    )
""").fetchall()

# Same logic as CTEs — far more readable:
q2 = cur.execute("""
    WITH per_customer AS (
        SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id
    ),
    avg_total AS (SELECT AVG(total) AS a FROM per_customer)
    SELECT c.name FROM per_customer p
    JOIN customers c ON c.id = p.customer_id, avg_total
    WHERE p.total > avg_total.a
""").fetchall()
print("subquery:", [r[0] for r in q1], "| CTE:", [r[0] for r in q2])

subquery: ['Ava'] | CTE: ['Ava']

6 · Window functions — analytics without collapsing rows advanced

Here's the wall you hit right after you learn GROUP BY. Your boss asks for a report: each order, with the customer's running total next to it — order 1: $40, running $40; order 2: $60, running $100; and so on. You reach for GROUP BY… and immediately realize it can't do this. Grouping collapses each customer down to one summary row, but the report needs every individual order to survive and carry an extra computed number. You want the summary and the detail, on the same line. Beginners spend hours fighting this with self-joins and correlated subqueries, and the result is slow and unreadable. The right tool is the window function, and once you meet it you'll wonder how you managed without it.

The core idea lives in the word "window." A window function looks at a window of rows around the current row and computes something across them — but crucially it keeps every row. Contrast the two directly: GROUP BY is a blender (many rows in, one smoothie out); a window function is a highlighter (every row stays on the page, you just annotate each one with a fact drawn from its neighbours). Think of a race: GROUP BY tells you "the average finish time was 4:02"; a window function writes each runner's placement (1st, 2nd, 3rd) next to their name while everyone stays on the results board.

Every window function carries an OVER (...) clause, and that clause is where the whole idea lives, so it's worth decoding slowly. PARTITION BY c.id means "restart the calculation for each customer" — it's like GROUP BY's piles, except the rows don't merge, they just get their own private scope. ORDER BY o.created inside the OVER() sets the sequence within that window, which is what makes a "running" total meaningful — running implies an order to run along. Get those two knobs and you can express most analytics questions: SUM(...) OVER(...) for running totals, RANK() / ROW_NUMBER() for placements, LAG() / LEAD() to peek at the previous or next row.

The mistake to watch for is confusing OVER(ORDER BY ...) with the query's own final ORDER BY. They're different jobs: the one inside OVER() only orders the rows for the purpose of the window calculation (which row counts as "previous"), while the query's trailing ORDER BY sorts the rows you actually get back. It's completely normal to have both, set differently. This is genuinely the highest-leverage SQL skill for analytics work and interviews alike — the moment you're fluent with OVER(), a whole class of "impossible in SQL" reports becomes a one-liner.

Window functions compute across rows related to the current row without collapsing them like GROUP BY. They power running totals, rankings, and per-group comparisons — the single most valuable SQL skill for analytics and interviews.

window.pyrows = cur.execute("""
    SELECT c.name, o.amount,
        SUM(o.amount) OVER (PARTITION BY c.id ORDER BY o.created) AS running,
        RANK()        OVER (ORDER BY o.amount DESC)               AS rk,
        LAG(o.amount) OVER (PARTITION BY c.id ORDER BY o.created) AS prev
    FROM orders o JOIN customers c ON c.id = o.customer_id
    ORDER BY c.name, o.created
""").fetchall()
for r in rows: print(r)   # (name, amount, per-customer running total, global rank, prev amount)

('Ava', 40.0, 40.0, 4, None)
('Ava', 60.0, 100.0, 2, 40.0)
('Ava', 20.0, 120.0, 6, 60.0)
('Ben', 25.0, 25.0, 5, None)
('Ben', 55.0, 80.0, 3, 25.0)
('Cy', 90.0, 90.0, 1, None)

7 · Read the query plan & optimize expert

This is the section that separates "I can write SQL" from "I can run SQL in production." The story plays out the same way everywhere: a query that returned instantly against 500 test rows takes 30 seconds against 5 million real ones, the page times out, and someone pages you. The query didn't change — the data grew. And here's the maddening part: the fix is often a single line, but only if you know why it's slow. Guessing ("maybe add more RAM?", "maybe rewrite it?") wastes days. The professional move is not to guess at all — it's to ask the database what it's doing.

The tool for that is EXPLAIN QUERY PLAN. Put those words in front of any query and SQLite describes how it intends to execute it — without actually running it. It's the difference between wondering why traffic is slow and pulling up the map that shows your route. And there is really only one distinction you must read in that output, but it's the one that matters most: does the plan say SCAN or SEARCH?

The analogy that makes this land is a physical book. A SCAN is reading every page from cover to cover to find each mention of "photosynthesis" — thorough, and hopeless once the book is long. That's O(n): double the rows, double the work. A SEARCH USING INDEX is flipping to the index at the back, finding "photosynthesis → p. 412," and jumping straight there. That's roughly O(log n), and the gap between them widens as the table grows — which is exactly why the query that was fine in testing dies in production. A database index is literally that back-of-book index: a pre-sorted lookup structure on a column, so the engine can seek instead of scan. (It's the same log-n-vs-n idea as hashing and balanced trees over in the DSA track.)

But an index is not free, and the beginner mistake is to over-correct into the opposite error: having been burned by a slow scan, they slap an index on every column "just in case." That backfires. Each index has to be updated on every INSERT, UPDATE, and DELETE, so a table drowning in indexes writes slowly and wastes disk. The discipline is surgical: index the columns you actually filter, join, or sort on — the ones in your WHERE, JOIN, and ORDER BY — then measure with EXPLAIN QUERY PLAN to confirm the plan flipped from SCAN to SEARCH, and stop. Add data-driven, remove speculative. The lab below shows the exact same query going from SCAN to SEARCH the instant the right index appears.

Expert SQL is knowing why a query is slow. EXPLAIN QUERY PLAN shows whether SQLite scans the whole table or seeks via an index. A full scan on a big table is the usual culprit; the fix is usually the right index.

explain.py# BEFORE: filtering orders by customer_id with no index -> full table SCAN
plan_before = cur.execute(
    "EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1").fetchall()
print("before:", plan_before[0][-1])

cur.execute("CREATE INDEX idx_orders_customer ON orders(customer_id)")

# AFTER: same query now SEARCHes via the index -> O(log n)
plan_after = cur.execute(
    "EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1").fetchall()
print("after: ", plan_after[0][-1])

before: SCAN orders
after:  SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

8 · Transactions — correctness under concurrency expert

Here is the nightmare every database exists to prevent. You transfer $100 from savings to checking. The database subtracts $100 from savings — and right then the server loses power, the network drops, or the process crashes. It never got to add the $100 to checking. Reboot, and the money is simply gone: it left one account and arrived in none. No amount of clever code around the two statements saves you, because the failure struck between them. This isn't a rare edge case — with enough traffic, "between two statements" happens constantly. The mechanism that makes it impossible is the transaction.

A transaction wraps several changes into one indivisible unit with a simple promise: all of them happen, or none of them do. You mark the start with BEGIN, do your work, and then either COMMIT (make every change permanent at once) or ROLLBACK (undo everything back to the start, as if you never began). The analogy is a wedding vow: it isn't "half-married" if the officiant faints mid-sentence — either the whole ceremony completes or it didn't happen. The transfer either fully lands on both accounts or neither budges. There is no in-between state the outside world can ever observe.

That all-or-nothing guarantee is the "A" — Atomicity — in the famous ACID acronym, and it's worth having all four in your head because they're the promises a real database makes that a pile of files never will. Atomic: all-or-nothing, as above. Consistent: rules and constraints hold before and after (a balance can't go negative if you forbid it). Isolated: concurrent transactions don't see each other's half-done work, so two simultaneous transfers can't corrupt one account. Durable: once COMMIT returns, the data survives a crash — it's really on disk, not just in memory.

The mistake beginners make is treating error handling as optional decoration. They write the two UPDATEs inside a try, forget the except, and when something throws, the transaction is left dangling — or worse, they never call rollback() and a half-applied change leaks out. The pattern that keeps the promise is disciplined and always the same: BEGIN, do the steps, commit() on success, and rollback() in an except block on any failure. The rollback is not the sad path you hope to skip — it's the whole point of the machinery, the thing that turns "money vanished" into "nothing happened, try again." In the lab below, deliberately deleting the rollback() is how you watch money disappear.

Multi-step changes must be all-or-nothing. A transaction commits together or rolls back together — the 'A' (atomicity) in ACID. The classic example is moving money between accounts.

transaction.pyimport sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance REAL)")
db.executemany("INSERT INTO accounts VALUES (?,?)", [(1, 100.0), (2, 0.0)])
db.commit()

def transfer(db, src, dst, amount):
    try:
        db.execute("BEGIN")
        db.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (amount, src))
        db.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, dst))
        bal = db.execute("SELECT balance FROM accounts WHERE id=?", (src,)).fetchone()[0]
        if bal < 0:
            raise ValueError("insufficient funds")
        db.commit(); return True
    except Exception:
        db.rollback(); return False              # neither update lands

print("transfer 30:", transfer(db, 1, 2, 30))    # True
print("transfer 999:", transfer(db, 1, 2, 999))  # False -> rolled back
print("balances:", db.execute("SELECT id, balance FROM accounts").fetchall())

transfer 30: True
transfer 999: False
balances: [(1, 70.0), (2, 30.0)]

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT)")
db.executemany("INSERT INTO customers (name, city) VALUES (?, ?)",
               [("Ada", "London"), ("Grace", "New York")])

for row in db.execute("SELECT id, name, city FROM customers"):
    print(row)
# (1, 'Ada', 'London')
# (2, 'Grace', 'New York')

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL)")
db.executemany("INSERT INTO orders (customer, amount) VALUES (?, ?)",
               [("Ada", 250.0), ("Grace", 80.0), ("Ada", 500.0), ("Grace", 150.0)])

rows = db.execute(
    "SELECT customer, amount FROM orders WHERE amount > ? ORDER BY amount DESC",
    (100,)).fetchall()
print(rows)
# [('Ada', 500.0), ('Ada', 250.0), ('Grace', 150.0)]

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT)")
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, cust_id INTEGER, amount REAL)")
db.executemany("INSERT INTO customers (name) VALUES (?)", [("Ada",), ("Grace",)])
db.executemany("INSERT INTO orders (cust_id, amount) VALUES (?, ?)",
               [(1, 250.0), (1, 500.0), (2, 150.0)])

rows = db.execute(
    "SELECT c.name, o.amount "
    "FROM orders o JOIN customers c ON c.id = o.cust_id "
    "ORDER BY c.name, o.amount").fetchall()
print(rows)   # [('Ada', 250.0), ('Ada', 500.0), ('Grace', 150.0)]

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL)")
db.executemany("INSERT INTO orders (customer, amount) VALUES (?, ?)",
               [("Ada", 250.0), ("Ada", 500.0), ("Grace", 150.0)])

rows = db.execute(
    "SELECT customer, COUNT(*) AS n, SUM(amount) AS total "
    "FROM orders GROUP BY customer HAVING total > 300 "
    "ORDER BY total DESC").fetchall()
print(rows)   # [('Ada', 2, 750.0)]

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL)")
db.executemany("INSERT INTO orders (customer, amount) VALUES (?, ?)",
               [("Ada", 750.0), ("Grace", 250.0)])

q = (
    "WITH totals AS ("
    "  SELECT customer, SUM(amount) AS total FROM orders GROUP BY customer) "
    "SELECT customer, total, "
    "  ROUND(total * 100.0 / (SELECT SUM(total) FROM totals), 1) AS pct "
    "FROM totals ORDER BY pct DESC")
print(db.execute(q).fetchall())
# [('Ada', 750.0, 75.0), ('Grace', 250.0, 25.0)]

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL)")
db.executemany("INSERT INTO orders (customer, amount) VALUES (?, ?)",
               [("Ada", 250.0), ("Ada", 500.0), ("Grace", 150.0)])

ranked = db.execute(
    "SELECT customer, amount, "
    "  ROW_NUMBER() OVER (PARTITION BY customer ORDER BY amount DESC) AS rnk "
    "FROM orders").fetchall()
print(ranked)
# [('Ada', 500.0, 1), ('Ada', 250.0, 2), ('Grace', 150.0, 1)]

# Before an index: a full scan for a customer lookup
plan = db.execute(
    "EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer = 'Ada'").fetchall()
print(plan)                          # ... SCAN orders
db.execute("CREATE INDEX idx_cust ON orders(customer)")
plan2 = db.execute(
    "EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer = 'Ada'").fetchall()
print(plan2)                         # ... SEARCH orders USING INDEX idx_cust

Knowledge check check yourself