Learn the concept
A join combines related rows. An inner join returns matching pairs; a left join retains rows from the left side even without a match. Joining a one-to-many relationship increases row count, which can accidentally inflate aggregates.
GROUP BY summarizes rows by a key. COUNT(*) counts rows, while counting a column excludes null values. Decide which business quantity you intend to count. A customer with no tickets should not disappear from a report if the report claims to include all customers.
Pagination needs a stable order. Offset-based pagination is simple but can shift as records change. Keyset pagination continues after a known ordering key and is useful for large or changing datasets. Include a unique tie-breaker when the primary sort field is not unique.
Run and inspect
rows = [("customer-a", 2), ("customer-b", 0)]
assert sum(count for _, count in rows) == 2
# SQL shape: SELECT customer_id, COUNT(*) FROM tickets GROUP BY customer_id
# Stable page shape: WHERE id > ? ORDER BY id LIMIT ?
Your exercise
Create customers and tickets tables. Report all customers including those with zero tickets, then paginate tickets by ID with no duplicates.
Check your understanding
The report handles one-to-many relationships correctly and two consecutive pages neither overlap nor omit stable records.
Guided workshop: import records transactionally
A database can enforce uniqueness and preserve related updates as one transaction. Use parameters for values; string formatting SQL would allow input to alter the query structure. Keep authorization in application code as well as any database policies.
# academy-check
import sqlite3
with sqlite3.connect(":memory:") as db:
db.execute("CREATE TABLE documents (id TEXT PRIMARY KEY, text TEXT NOT NULL)")
db.execute("CREATE TABLE imports (document_id TEXT, batch TEXT)")
with db:
db.execute("INSERT INTO documents VALUES (?, ?)", ("a", "hello"))
db.execute("INSERT INTO imports VALUES (?, ?)", ("a", "batch-1"))
try:
with db:
db.execute("INSERT INTO documents VALUES (?, ?)", ("b", "world"))
db.execute("INSERT INTO documents VALUES (?, ?)", ("a", "duplicate"))
except sqlite3.IntegrityError:
pass
assert db.execute("SELECT count(*) FROM documents").fetchone()[0] == 1
rows = db.execute("SELECT d.id, i.batch FROM documents d JOIN imports i ON d.id=i.document_id").fetchall()
assert rows == [("a", "batch-1")]
The duplicate a violates the primary key. The transaction rolls back the earlier insertion of b too. Catching the exception outside the transaction context lets the context manager see the failure. Catching and suppressing it inside the block can lead to committing partial work.
Exercise: add documents b and c, then retrieve pages with WHERE id > ? ORDER BY id LIMIT ?. For page size 2, the first page is a,b and the next is c. Keep the last ID as a cursor. This simple cursor assumes a stable unique ordering; document the consistency you expect if records change between requests.
Worked extension: count imports per batch with GROUP BY batch. A join can multiply rows when one document has several import records, so count(*) counts joined rows rather than distinct documents. Choose count(DISTINCT d.id) only when that matches the question.
Checkpoint: explain why an index speeds a matching lookup but adds storage and write work. Show rollback with a failing assertion inside a transaction and verify the database after catching it. A passing query test alone does not establish that failure recovery works.