Day 36 · Spreadsheets with a contract

SQL I — Tables & Queries

You will be able to

Explain the relational model: tables, rows, columns, primary keys, constraints
Create tables and insert data from Python with sqlite3 and parameter binding
Write SELECT queries with WHERE, ORDER BY, LIMIT, and LIKE
Aggregate with COUNT/SUM/AVG/MIN/MAX and group with GROUP BY/HAVING
Predict how NULL behaves in comparisons, aggregates, and counts

Today's ~120 minutes

Spaced-rep warm-up: due cards (Week 5 selections)	10 min
ELI5 + tech read: the contract and the pipeline order	18 min
Guided: build & seed the shelf, aggregates & NULL casino	42 min
Practice: the eight-query gauntlet	20 min
Project: your dataset under contract, commit	20 min
Quiz + flashcards	10 min

Builds on: Day 5 — Files, CSV & JSON · Day 4 — Collections — lists and dicts · Day 22 — Big O & complexity

The analogy

🧸 ELI5⚙ Tech

A spreadsheet lets anyone type anything anywhere: a birthday in the salary column, a name spelled three ways, a row half-filled. A database table is a spreadsheet with a CONTRACT. Before any data arrives you declare the columns, their types, and the rules: "id is a unique number, title can never be empty, price must be positive." From then on, the database is a bouncer — rows that violate the contract are refused at the door, loudly, instead of quietly corrupting your data.

The second half of the deal: because the database knows the contract, you stop telling it HOW to find things and start telling it WHAT you want. "Give me the ten most expensive books published after 2015" is one declarative sentence — no loops, no index variables. The database's query planner picks the how. That is the deep mental shift from Python: SQL is you describing the shape of an answer, and the engine doing the walking. SQLite — the database used today — keeps the whole contract-and-contents in a single file on your laptop, no server, which is why it hides inside your phone, your browser, and roughly every device you own.

Why this matters on the job

SQL is the closest thing software has to a universal language — every company you will ever consult for keeps its truth in relational tables, and "can you pull the numbers?" is an FDE question you will field in customer meetings for the rest of your career. Your AI work sits on it too: the Day 42 service, the capstone's metadata store, eval results on Day 140, and the structured half of "SQL + vectors" retrieval on Day 118 are all tables. Interviews test SQL directly (a screen of its own at many companies), and GROUP BY/HAVING plus NULL traps are precisely where candidates faceplant.

Guided practice

guided 1

Create the contract, load the shelf

20 min

Create sql_lab.py with the starter. Run it once: it builds books.db, creates the table with constraints, and seeds 12 rows — including some NULL page counts on purpose.
Prove the contract works: uncomment the two doomed inserts (empty title, negative price) and watch IntegrityError refuse them. This is the bouncer doing its job — read each error message fully.
Prove parameter binding matters: insert the book title "It's a Trap" via the ? placeholder — works fine. Now TRY building the same INSERT with an f-string and watch the apostrophe shatter the SQL. Delete the f-string version; never write it again.
Run the four starter queries (WHERE, ORDER BY + LIMIT, LIKE, a computed column). Before each, say out loud what rows you expect; then run and check.
Open the db file with the sqlite3 command-line tool too (sqlite3 books.db ".schema") — same file, two clients. The database is the file.

🐍 python — editable, runs in your browser

import sqlite3

conn = sqlite3.connect("books.db")
conn.execute("DROP TABLE IF EXISTS books")
conn.execute("""
    CREATE TABLE books (
        id       INTEGER PRIMARY KEY,
        title    TEXT NOT NULL CHECK (length(title) > 0),
        author   TEXT NOT NULL,
        year     INTEGER,
        price    REAL NOT NULL CHECK (price >= 0),
        pages    INTEGER              -- nullable on purpose
    )
""")

seed = [
    ("Deep Work", "Cal Newport", 2016, 14.99, 296),
    ("Clean Code", "Robert Martin", 2008, 32.50, 464),
    ("The Pragmatic Programmer", "Andrew Hunt", 1999, 39.99, 352),
    ("Fluent Python", "Luciano Ramalho", 2022, 55.00, 1012),
    ("Designing Data-Intensive Applications", "Martin Kleppmann", 2017, 44.99, 616),
    ("The Mythical Man-Month", "Fred Brooks", 1975, 29.99, 336),
    ("Refactoring", "Martin Fowler", 2018, 47.99, None),
    ("A Philosophy of Software Design", "John Ousterhout", 2021, 21.99, 196),
    ("Grokking Algorithms", "Aditya Bhargava", 2016, 34.99, 256),
    ("Python Crash Course", "Eric Matthes", 2023, 39.99, None),
    ("The Design of Everyday Things", "Don Norman", 2013, 17.99, 368),
    ("Thinking, Fast and Slow", "Daniel Kahneman", 2011, 16.99, 499),
]
conn.executemany(
    "INSERT INTO books (title, author, year, price, pages) VALUES (?, ?, ?, ?, ?)",
    seed,
)
conn.commit()

# the bouncer at work -- uncomment one at a time:
# conn.execute("INSERT INTO books (title, author, price) VALUES ('', 'X', 1.0)")
# conn.execute("INSERT INTO books (title, author, price) VALUES ('Y', 'X', -5)")

def q(sql, params=()):
    print("\n--", sql.strip().splitlines()[0])
    for row in conn.execute(sql, params):
        print("  ", row)

q("SELECT title, price FROM books WHERE price < 30 ORDER BY price")
q("SELECT title, year FROM books ORDER BY year DESC LIMIT 3")
q("SELECT title FROM books WHERE title LIKE '%Design%'")
q("SELECT title, round(price * 0.9, 2) AS sale_price FROM books WHERE year >= 2015", ())

Ctrl/⌘+Enter runs · Tab indents · numpy/pandas/sklearn auto-load on import (torch and network calls need a local run)

guided 2

Aggregates, buckets & the NULL casino

22 min

Warm up with whole-table aggregates: count the books, the min/max/avg price. Predict avg price by eye first — being roughly right builds the sanity-check reflex.
The three counts. Run COUNT(*), COUNT(pages), and AVG(pages) in one query. Explain the difference out loud: 12 rows, 10 non-null page counts, and an average computed over 10, not 12. Which average did you WANT? (Business question, not SQL question — that is the point.)
The NULL trap, personally experienced. Run WHERE pages = NULL (zero rows — even though two rows have NULL pages!), then WHERE pages IS NULL (two rows). Say why: comparing to unknown is unknown, and WHERE only passes TRUE.
Group into buckets: books per author-era — use the starter's decade expression. Check that every SELECT column is either aggregated or in the GROUP BY.
WHERE vs HAVING. Find decades with more than 2 books, but only counting books under 50 dollars: price filter in WHERE (row-level, pre-bucket), count filter in HAVING (bucket-level). Swap them and observe the error / wrong result — the pipeline order is real.

🐍 python — editable, runs in your browser

Ctrl/⌘+Enter runs · Tab indents · numpy/pandas/sklearn auto-load on import (torch and network calls need a local run)

On your own

The eight-query gauntlet

20 min

Answer each with ONE query against books.db (no Python post-processing): (1) titles containing "the" case-insensitively; (2) the three cheapest books from this millennium; (3) each author's book count and total spend to buy them all; (4) the average price of books WITH a known page count vs the overall average — two queries, explain the difference; (5) decades whose average price exceeds 30; (6) books priced above the table's average price (needs a subquery: WHERE price > (SELECT AVG(price) FROM books)); (7) the year span (max - min) of the collection; (8) authors with exactly one book, alphabetically.

Constraints: use ORDER BY + LIMIT, GROUP BY + HAVING, LIKE, and one subquery across the set. Hints: LIKE is case-insensitive for ASCII in SQLite by default; "this millennium" is year >= 2000; check each aggregate against the seed data by eye.

Ship before you stop

Your dataset, under contract

Put real data under contract. Take the tasks from your Day 7 tracker, the log records from Day 14, or any CSV you have handy, and build my_data.py: a script that (1) creates a table whose constraints actually encode your data's rules (at least one CHECK, one NOT NULL beyond the key); (2) loads the data via executemany with placeholders; (3) proves the bouncer works with two intentionally-rejected rows in a try/except; (4) answers five questions you genuinely care about, each as one query, with the NULL behavior of at least one column explicitly tested. Commit the script (not the .db file — add it to .gitignore).

Rubric — check what you completed (0/5)

Schema has a primary key, a CHECK, and a NOT NULL that fit the data's real rulesAll inserts use ? placeholders; no string-formatted SQL anywhereTwo contract-violating rows demonstrably rejected and handled with try/exceptFive queries run, at least one using GROUP BY + HAVING and one using a subqueryOne query demonstrates IS NULL vs = NULL on your data; .db file gitignored, script committed

Common mistakes & misconceptions

Filtering aggregates in WHERE ("WHERE COUNT(*) > 2"). WHERE runs before grouping and cannot see bucket values — that filter belongs in HAVING.
Testing NULL with = or !=. Both return UNKNOWN and the row silently vanishes from results. Only IS NULL / IS NOT NULL work.
Assuming COUNT(column) counts rows. It counts NON-NULL values in that column; COUNT(*) counts rows. Averages likewise skip NULLs.
Building SQL with f-strings. The first apostrophe breaks it and user input owns your database (Day 44 makes this formal). Placeholders, always.
Forgetting conn.commit() after writes and wondering why another connection sees nothing. Reads see your own uncommitted work; others do not.
SELECT * in application code. Schema changes reorder/add columns and silently break positional row unpacking — name the columns you need.

Knowledge check

Q1. You want product categories having more than 10 items priced under 20 dollars. Where do the two conditions go?

Q2. Two of 12 rows have NULL in bonus. What does SELECT COUNT(*), COUNT(bonus), AVG(bonus) return conceptually?

Q3. Why must user-supplied values go into queries via ? placeholders instead of f-strings?

Go deeper — curated resources

courseSQLBolt — interactive lessons 1–12 ↗30 min docssqlite3 — official Python docs (tutorial section) ↗20 min docsSQLite Documentation — language reference ↗15 min

If you have a third hour

SQLite: how it stores your table ↗ — Skim the file-format overview: the whole database is B-tree pages in one file — vocabulary that pays off on Day 38.

Done means

Contract violations demonstrably rejected; placeholder-vs-f-string experiment run
The three counts (rows / non-null / avg basis) explained in your own words
Eight-query gauntlet answered with single queries each
Personal dataset project committed with .db gitignored
Quiz ≥ 2/3

How this connects

← Back: The rows you seeded came from Day 5's CSV/JSON skills, the dict-like access patterns echo Day 4, and the query planner's freedom to choose HOW is the declarative cousin of Day 22's complexity thinking.

Forward →: Tomorrow (Day 37) splits data across tables and JOINs it back. Day 38 opens the hood on how these queries execute and when they need indexes. The Day 42 service persists through exactly this sqlite3 API, and Day 118 marries SQL tables to vector search.

Unlocks: D37 SQL II — Joins & Modeling · D42 Week 6 Checkpoint: API + DB Mini-Service · D65 pandas I — DataFrames