Overview

Prerequisites

• Prior topics: 4 · Just Enough Python -- Python drives every database-access example in this topic's Python-marked (†) examples, and by this point you should be comfortable running a .py script and reading typed function signatures.
• Tools & environment: a macOS/Linux terminal; SQLite (sqlite3 --version confirms it is installed -- the same engine Python's stdlib sqlite3 module embeds); Python 3.x with pytest installed in a venv. psql/PostgreSQL is only referenced for cross-checking dialect-agnostic SQL semantics (JOIN/GROUP BY/HAVING/NULL behavior) -- it is not required to complete this topic.
• Assumed knowledge: basic Python (variables, functions, running a script). No prior SQL or database background is assumed -- this topic is your SQL starting point.

Why this exists -- the big idea

Application data outlives the process that created it. A list or dictionary in memory vanishes the moment your program exits; a table in a database survives restarts, crashes, and years of accumulated history. That durability comes with a cost: the data has to be queried, related to other data, and kept internally consistent -- exactly the problems the relational model and SQL were built to solve.

Keep-this-if-you-forget-everything: declare what result you want and let the engine decide how to get it. This is the mechanism-vs-policy big idea in concrete form: SQL is declarative policy (the result you describe), and the query planner is the mechanism (how it actually fetches rows) -- you never write a loop to find "every book over $25," you just say WHERE price > 25 and the engine figures out the rest. Normalization -- splitting data into related tables instead of repeating it -- is the other half of this topic's foundation: it keeps one fact in exactly one place, so updating an author's name never means hunting down every book row that repeated it.

How this topic is organized

• Beginner (Examples 1-30) -- schema declaration (CREATE TABLE, .schema), the CRUD basics (INSERT, SELECT, UPDATE, DELETE), WHERE/ORDER BY/LIMIT filtering and paging, the four core constraints (NOT NULL, UNIQUE, DEFAULT, CHECK), foreign keys (declaring and enforcing), a first JOIN, and the first two Python sqlite3 examples (connecting, querying, and parameterized inserts).
• Intermediate (Examples 31-58) -- multi-table joins with aliases, LEFT JOIN and anti-joins, GROUP BY/HAVING aggregation, NULL's three-valued logic and COALESCE, normalization walkthroughs (1NF and 3NF), more Python sqlite3 (named parameters, executemany, fetchone streaming, sqlite3.Row), transactions (commit/rollback/context-manager), injection-safe vs. injection-unsafe queries side by side, upsert (ON CONFLICT), subqueries, self-joins, and CASE expressions.
• Advanced (Examples 59-80) -- additive schema migrations with PRAGMA user_version tracking, diagnosing and fixing the N+1 query problem, composite primary keys, ON DELETE CASCADE/RESTRICT, SAVEPOINT/ROLLBACK TO partial-transaction undo, a typed Python data-access-layer module tested with pytest, CSV export via the CLI, integrity-check pragmas, designing a 3NF schema from scratch, and correlated subqueries.

Every example cites the concept (co-NN) it exercises, and every example is fully self-contained -- none of them depend on state left behind by an earlier example, so you can run any single one from a clean, empty directory.

Scope: the usable slice

This topic covers the usable slice: schema design, core queries, and safe database access from Python, entirely from the CLI. Window functions, common table expressions (CTEs), indexing strategy, and transaction isolation levels are deliberately out of scope here -- they are deferred to a later Advanced SQL topic in this curriculum. If a SQL feature is not exercised by an example in this topic, it is out of scope on purpose, not by oversight.

Accuracy: current SQLite is 3.53.3 (2026-06-26), public domain (no license required). Note that the SQLite version bundled with a given Python install varies -- this topic reads sqlite3.sqlite_version at runtime rather than asserting a fixed bundled version wherever that matters. Every concrete, checkable claim in this topic (constraint error text, CLI dot-command behavior, PRAGMA semantics, Python sqlite3 API surface) was verified against sqlite.org and docs.python.org primary documentation on 2026-07-12, and re-verified with no drift found on 2026-07-14.

Examples by Level

Beginner (Examples 1–30)

• Example 1: Create Author Table
• Example 2: Open Database CLI
• Example 3: Insert Single Row
• Example 4: Insert Multiple Rows
• Example 5: Select All Columns
• Example 6: Select Projection
• Example 7: Where Equality
• Example 8: Where Comparison
• Example 9: Where And Or
• Example 10: Where Like Prefix
• Example 11: Where In Set
• Example 12: Order By Ascending
• Example 13: Order By Descending
• Example 14: Limit Rows
• Example 15: Limit Offset Paging
• Example 16: Select Distinct
• Example 17: Type Affinity
• Example 18: Not Null Constraint
• Example 19: Unique Constraint
• Example 20: Default Value
• Example 21: Check Constraint
• Example 22: Autoincrement Rowid
• Example 23: Update One Row
• Example 24: Update All Rows
• Example 25: Delete Row
• Example 26: Declare Foreign Key
• Example 27: Enforce Foreign Key
• Example 28: Inner Join Two Tables
• Example 29: Python Connect And Query
• Example 30: Python Parameterized Insert

Intermediate (Examples 31–58)

• Example 31: Left Join Unmatched
• Example 32: Join with Aliases
• Example 33: Three-Table Join
• Example 34: Group By Count
• Example 35: Group By Sum
• Example 36: Group By Avg
• Example 37: Min-Max Aggregate
• Example 38: Having Filter Groups
• Example 39: Where Plus Having
• Example 40: Count Star vs Column
• Example 41: Null Is Null
• Example 42: Null Coalesce
• Example 43: Null Three-Valued
• Example 44: Aggregate Over Join
• Example 45: Normalize Repeating Group (1NF)
• Example 46: Normalize Transitive Dependency (3NF)
• Example 47: Python Named Params
• Example 48: Python Executemany
• Example 49: Python Fetchone Loop
• Example 50: Python Row Factory
• Example 51: Transaction Commit
• Example 52: Transaction Rollback
• Example 53: Transaction Context Manager
• Example 54: Injection Safe vs Unsafe
• Example 55: Upsert On Conflict
• Example 56: Subquery in Where
• Example 57: Self Join
• Example 58: Case Expression

Advanced (Examples 59–80)

• Example 59: Migration Add Column
• Example 60: Migration Backfill
• Example 61: Migration Version Tracking
• Example 62: N+1 Query Problem Demonstrated
• Example 63: N+1 Fixed with a Single Join
• Example 64: N+1 Fixed with a Batched Fetch
• Example 65: Composite Primary Key
• Example 66: Cascade Delete
• Example 67: Restrict Delete
• Example 68: Savepoint Partial Rollback
• Example 69: Python Report Function
• Example 70: Group Concat
• Example 71: Anti-Join Missing Rows
• Example 72: Atomic Transfer
• Example 73: Python DAL Module
• Example 74: Seed from SQL File
• Example 75: Export Query to CSV
• Example 76: Integrity Checks
• Example 77: Design a 3NF Schema
• Example 78: Correlated Subquery
• Example 79: Join, Group, and Having Report
• Example 80: pytest Rollback Integration

---

← Previous: 9 · Project Management · Next: Beginner Examples →