Hands-on lab — Day 86: SELECT: Filtering, Sorting, and Aggregating

# The database is BUILT, not stored. Rebuild it any time with:
#   bash examples/build_db.sh
library.db
*.db

#!/usr/bin/env bash
# Rebuild library.db from examples/seed.sql.
#
# Run from the lab directory:
#   bash examples/build_db.sh
#
# The build is destructive on purpose: it deletes the existing library.db and
# recreates it, so every exercise in this lab starts from the same rows no
# matter what you did to the database in between. That is the whole reason the
# seed is a file and not something you typed once.
set -eu

lab_dir="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
db="${1:-${lab_dir}/examples/library.db}"

rm -f "${db}"
sqlite3 "${db}" < "${lab_dir}/examples/seed.sql"
echo "built: ${db}"

-- Day 086 lab — the model answers to starter/exercises.sql.
--
-- Run:  sqlite3 examples/library.db < examples/exercise-answers.sql
--
-- Read this AFTER you have tried the exercises, not before. Each answer below
-- is one line of the form exNN|value, the same shape starter/exercises.sql
-- emits, so `bash starter/check.sh` can compare the two directly.
--
-- The point of each fix is written above it. In every single case the broken
-- version ran without complaint and returned a plausible number: that is the
-- thing to take away from this file.

.mode list
.headers off

-- 1. `= NULL` is UNKNOWN for every row, so the broken version counted nothing.
--    IS NULL is the only test for absence.
SELECT 'ex01|' || (
  SELECT COUNT(*) FROM loans WHERE returned_on IS NULL
);

-- 2. `city <> 'Pune'` is UNKNOWN where city is NULL, and WHERE keeps only TRUE,
--    so the two members with no city vanished from a question they belong in.
SELECT 'ex02|' || (
  SELECT COUNT(*) FROM members WHERE city IS NULL OR city <> 'Pune'
);

-- 3. GLOB is case-sensitive; LIKE folds case for ASCII letters. Same intent,
--    different matcher.
SELECT 'ex03|' || (
  SELECT COUNT(*) FROM books WHERE title LIKE '%archive%'
);

-- 4. BETWEEN is inclusive at both ends; the strict inequalities dropped 2015
--    and 2018 silently.
SELECT 'ex04|' || (
  SELECT COUNT(*) FROM books WHERE published_year BETWEEN 2015 AND 2018
);

-- 5. AVG already ignores NULLs. COALESCE(rating, 0.0) invents four ratings of
--    zero and drags the average down by more than half a point.
SELECT 'ex05|' || (
  SELECT ROUND(AVG(rating), 2) FROM books
);

-- 6. COUNT(*) counts rows; COUNT(rating) counts non-NULL ratings. The gap
--    between them is exactly the number of unrated books.
SELECT 'ex06|' || (
  SELECT COUNT(*) - COUNT(rating) FROM books
);

-- 7. Ascending order puts NULLs first in SQLite, so the broken version returned
--    a book with no rating as the worst-rated book.
SELECT 'ex07|' || (
  SELECT title FROM books WHERE rating IS NOT NULL ORDER BY rating ASC LIMIT 1
);

-- 8. COUNT(DISTINCT genre) skips NULL. GROUP BY collects all the NULLs into one
--    bucket, which is the bucket the question asked about.
SELECT 'ex08|' || (
  SELECT COUNT(*) FROM (SELECT genre FROM books GROUP BY genre)
);

-- 9. ORDER BY defaults to ASC, which answers the opposite question. DESC, and a
--    tie-break so the answer is deterministic.
SELECT 'ex09|' || (
  SELECT author FROM books GROUP BY author ORDER BY COUNT(*) DESC, author ASC LIMIT 1
);

-- 10. "How many titles this author has" is a property of the GROUP, so only
--     HAVING can filter on it. WHERE runs before the groups exist.
SELECT 'ex10|' || (
  SELECT COUNT(*) FROM (
    SELECT author FROM books GROUP BY author HAVING COUNT(*) > 3
  )
);

-- 11. Both columns are TEXT. Subtracting them coerces each string to the number
--     at its front — 2026 minus 2026 — and confidently returns 0.
SELECT 'ex11|' || (
  SELECT JULIANDAY(returned_on) - JULIANDAY(borrowed_on) FROM loans WHERE loan_id = 2
);

-- 12. `rating >= 4.5` is UNKNOWN when rating is NULL, so every unrated book fell
--     through to the ELSE and was labelled 'poor'. The NULL branch has to come
--     first, because CASE stops at the first WHEN that is TRUE.
SELECT 'ex12|' || (
  SELECT COUNT(*) FROM (
    SELECT CASE
             WHEN rating IS NULL THEN 'unrated'
             WHEN rating >= 4.5  THEN 'excellent'
             WHEN rating >= 4.0  THEN 'good'
             ELSE                     'poor'
           END AS band
    FROM books
  )
  WHERE band = 'unrated'
);

#!/usr/bin/env python3
"""Build GROUP BY and the aggregates by hand, then check SQL agrees.

Run from the lab directory, after `bash examples/build_db.sh`:

    python3 examples/groupby_from_scratch.py

An explicit database path may be passed as the one optional argument, which is
how tests/run_tests.sh points it at a throwaway copy.

The point is not that the Python is better. It is that after you have written
the accumulator loop once, the one-line SQL stops being magic: you know exactly
which dictionary it is filling in, and you know exactly why AVG skips NULLs —
because you had to decide that yourself, on line 60-ish, and there was no other
sensible choice available.

Nothing here imports anything outside the standard library.
"""

from __future__ import annotations

import sqlite3
import sys
from pathlib import Path

LAB_DIR = Path(__file__).resolve().parent.parent
DB_PATH = LAB_DIR / "examples" / "library.db"


# --------------------------------------------------------------------------
# Step 1 — FROM. Pull the raw rows out, and do nothing else to them.
# --------------------------------------------------------------------------
def load_rows(conn: sqlite3.Connection) -> list[dict]:
    """The FROM clause: every row, every column, no filtering yet."""
    conn.row_factory = sqlite3.Row
    cur = conn.execute("SELECT genre, rating, pages, published_year FROM books")
    return [dict(r) for r in cur.fetchall()]


# --------------------------------------------------------------------------
# Step 2 — WHERE. Keep a row only when the predicate is TRUE.
#
# This is where three-valued logic shows up in plain Python: a comparison
# against None cannot be True, so the row is dropped. SQL does exactly this,
# and the reason it feels surprising is that SQL does it silently.
# --------------------------------------------------------------------------
def where(rows: list[dict]) -> list[dict]:
    """WHERE published_year >= 2000 — rows with a NULL year cannot qualify."""
    kept = []
    for row in rows:
        year = row["published_year"]
        if year is None:
            continue  # UNKNOWN, and UNKNOWN is not TRUE
        if year >= 2000:
            kept.append(row)
    return kept


# --------------------------------------------------------------------------
# Step 3 — GROUP BY. One accumulator per distinct key.
#
# The grouping key here is `genre`, and None is a legitimate key: GROUP BY is
# the one place in SQL where all the NULLs are treated as equal to each other
# and land in a single bucket.
# --------------------------------------------------------------------------
def group_by_genre(rows: list[dict]) -> dict:
    """Fold the rows into one accumulator per genre."""
    buckets: dict = {}
    for row in rows:
        key = row["genre"]  # None is a real key, not an error
        acc = buckets.get(key)
        if acc is None:
            acc = {
                "rows": 0,  # COUNT(*)      — every row
                "rating_n": 0,  # COUNT(rating) — non-NULL ratings only
                "rating_sum": 0.0,  # SUM(rating)
                "rating_min": None,  # MIN(rating)
                "rating_max": None,  # MAX(rating)
                "pages_n": 0,
                "pages_sum": 0,
            }
            buckets[key] = acc

        acc["rows"] += 1  # COUNT(*) counts the ROW

        rating = row["rating"]
        if rating is not None:  # every other aggregate SKIPS NULL
            acc["rating_n"] += 1
            acc["rating_sum"] += rating
            if acc["rating_min"] is None or rating < acc["rating_min"]:
                acc["rating_min"] = rating
            if acc["rating_max"] is None or rating > acc["rating_max"]:
                acc["rating_max"] = rating

        pages = row["pages"]
        if pages is not None:
            acc["pages_n"] += 1
            acc["pages_sum"] += pages

    return buckets


# --------------------------------------------------------------------------
# Step 4 — HAVING, then SELECT, then ORDER BY.
# --------------------------------------------------------------------------
def finish(buckets: dict) -> list[tuple]:
    """HAVING COUNT(*) >= 3, project the columns, then sort."""
    out = []
    for key, acc in buckets.items():
        if acc["rows"] < 3:  # HAVING — filters BUCKETS, not rows
            continue

        # SELECT — the projection. AVG is SUM over the NON-NULL count, and it
        # is NULL (not 0) when nothing in the bucket had a value at all.
        avg_rating = (
            round(acc["rating_sum"] / acc["rating_n"], 6) if acc["rating_n"] else None
        )
        avg_pages = round(acc["pages_sum"] / acc["pages_n"], 6) if acc["pages_n"] else None
        label = key if key is not None else "(unclassified)"
        out.append(
            (
                label,
                acc["rows"],
                acc["rating_n"],
                avg_rating,
                acc["rating_min"],
                acc["rating_max"],
                avg_pages,
            )
        )

    # ORDER BY books DESC, genre_label ASC — last of all, on the projected rows.
    out.sort(key=lambda r: (-r[1], r[0]))
    return out


# --------------------------------------------------------------------------
# The one-line replacement. Written in the order you TYPE a SELECT, executed
# in the order the functions above are called.
# --------------------------------------------------------------------------
SQL = """
SELECT IFNULL(genre, '(unclassified)') AS genre_label,
       COUNT(*)                        AS books,
       COUNT(rating)                   AS rated,
       ROUND(AVG(rating), 6)           AS avg_rating,
       MIN(rating)                     AS min_rating,
       MAX(rating)                     AS max_rating,
       ROUND(AVG(pages), 6)            AS avg_pages
FROM books
WHERE published_year >= 2000
GROUP BY genre
HAVING COUNT(*) >= 3
ORDER BY books DESC, genre_label ASC
"""

HEADER = ("genre_label", "books", "rated", "avg_rating", "min_rating", "max_rating", "avg_pages")


def render(rows: list[tuple]) -> str:
    widths = [max(len(str(r[i])) for r in ([HEADER] + rows)) for i in range(len(HEADER))]
    lines = ["  ".join(str(h).ljust(widths[i]) for i, h in enumerate(HEADER))]
    lines.append("  ".join("-" * w for w in widths))
    for r in rows:
        lines.append("  ".join(str(v).ljust(widths[i]) for i, v in enumerate(r)))
    return "\n".join(lines)


def main() -> int:
    db_path = Path(sys.argv[1]).resolve() if len(sys.argv) > 1 else DB_PATH
    if not db_path.exists():
        print(f"error: {db_path} does not exist — run: bash examples/build_db.sh", file=sys.stderr)
        return 1

    with sqlite3.connect(db_path) as conn:
        rows = load_rows(conn)
        kept = where(rows)
        buckets = group_by_genre(kept)
        by_hand = finish(buckets)

        cur = conn.execute(SQL)
        by_sql = [tuple(r) for r in cur.fetchall()]

    print("The 20 lines of Python:")
    print(f"  FROM      -> {len(rows)} rows")
    print(f"  WHERE     -> {len(kept)} rows survive")
    print(f"  GROUP BY  -> {len(buckets)} buckets")
    print(f"  HAVING    -> {len(by_hand)} buckets survive")
    print()
    print(render(by_hand))
    print()
    print("The one SQL statement:")
    print()
    print(render(by_sql))
    print()

    if by_hand == by_sql:
        print(f"IDENTICAL: {len(by_hand)} rows match exactly.")
        return 0

    print("MISMATCH", file=sys.stderr)
    for hand, sql in zip(by_hand, by_sql):
        if hand != sql:
            print(f"  by hand: {hand}", file=sys.stderr)
            print(f"  by sql : {sql}", file=sys.stderr)
    return 1


if __name__ == "__main__":
    raise SystemExit(main())

-- 01 — WHERE: comparison and boolean operators, IN, BETWEEN
--
-- Run:  sqlite3 -header -column examples/library.db < examples/queries/01-filters.sql

.print '--- 1.1 science books published this century, longest first'
SELECT title, published_year, pages
FROM books
WHERE genre = 'science' AND published_year >= 2000
ORDER BY pages DESC;

.print ''
.print '--- 1.2 two genres AND a year, with the brackets that make it mean that'
SELECT title, genre, published_year
FROM books
WHERE (genre = 'science' OR genre = 'history') AND published_year >= 2015
ORDER BY published_year;

.print ''
.print '--- 1.2b the same words without brackets: AND binds tighter than OR, so it means'
.print '         science-of-any-year OR history-since-2015 — a different question'
SELECT COUNT(*) AS with_brackets
FROM books
WHERE (genre = 'science' OR genre = 'history') AND published_year >= 2015;

SELECT COUNT(*) AS without_brackets
FROM books
WHERE genre = 'science' OR genre = 'history' AND published_year >= 2015;

.print ''
.print '--- 1.3 IN is the readable form of a chain of ORs'
SELECT title, genre
FROM books
WHERE genre IN ('poetry', 'mystery')
ORDER BY genre, title;

.print ''
.print '--- 1.4 BETWEEN is inclusive at BOTH ends'
SELECT title, published_year
FROM books
WHERE published_year BETWEEN 2015 AND 2018
ORDER BY published_year, title;

.print ''
.print '--- 1.5 the same range written out, to prove BETWEEN includes the edges'
SELECT COUNT(*) AS between_count
FROM books
WHERE published_year BETWEEN 2015 AND 2018;

SELECT COUNT(*) AS explicit_count
FROM books
WHERE published_year >= 2015 AND published_year <= 2018;

.print ''
.print '--- 1.6 NOT IN: everything except two genres (watch what happens to NULL genres)'
SELECT COUNT(*) AS not_in_count
FROM books
WHERE genre NOT IN ('poetry', 'mystery');

-- 02 — LIKE and GLOB: two pattern matchers with different rules
--
-- Run:  sqlite3 -header -column examples/library.db < examples/queries/02-patterns.sql
--
-- LIKE uses % (any run of characters) and _ (exactly one character), and for
-- ASCII letters it is CASE-INSENSITIVE by default in SQLite.
-- GLOB uses * and ?, plus [character classes], and is always CASE-SENSITIVE.

.print '--- 2.1 LIKE: every title containing "archive", in any case'
SELECT title
FROM books
WHERE title LIKE '%archive%'
ORDER BY title;

.print ''
.print '--- 2.2 GLOB with the same intent, lower case — case-sensitive, so it finds nothing'
SELECT COUNT(*) AS glob_lowercase_matches
FROM books
WHERE title GLOB '*archive*';

.print ''
.print '--- 2.3 GLOB spelled the way the data is spelled'
SELECT title
FROM books
WHERE title GLOB '*Archive*'
ORDER BY title;

.print ''
.print '--- 2.4 the underscore matches exactly one character — five of them, for "Quiet"'
SELECT title
FROM books
WHERE title LIKE 'The _____ Algorithm';

.print ''
.print '--- 2.4b four underscores is one too few, and the result is silence, not an error'
SELECT COUNT(*) AS four_underscores
FROM books
WHERE title LIKE 'The ____ Algorithm';

.print ''
.print '--- 2.5 GLOB character classes, which LIKE has no equivalent for'
SELECT title
FROM books
WHERE title GLOB '[AN]*'
ORDER BY title;

.print ''
.print '--- 2.6 anchored prefix search: authors whose surname starts with a letter range'
SELECT DISTINCT author
FROM books
WHERE author GLOB '* [A-M]*'
ORDER BY author;

-- 03 — NULL and three-valued logic: the traps, and the correct forms
--
-- Run:  sqlite3 -header -column examples/library.db < examples/queries/03-null-traps.sql
--
-- NULL is not a value. It is the absence of one. Every comparison against it
-- returns UNKNOWN, and WHERE keeps only rows where the predicate is TRUE.
-- UNKNOWN is not TRUE, so those rows silently disappear.

.print '--- 3.1 the truth, straight from the engine: NULL = NULL is not true'
SELECT NULL = NULL           AS null_eq_null,
       NULL <> NULL          AS null_ne_null,
       NULL IS NULL          AS null_is_null,
       (NULL AND 0)          AS null_and_false,
       (NULL AND 1)          AS null_and_true,
       (NULL OR 1)           AS null_or_true,
       (NULL OR 0)           AS null_or_false,
       (NOT NULL)            AS not_null;

.print ''
.print '--- 3.2 THE TRAP: books still on loan, written the wrong way'
SELECT COUNT(*) AS wrong_still_out
FROM loans
WHERE returned_on = NULL;

.print ''
.print '--- 3.3 the same wrong idea in its other popular disguise'
SELECT COUNT(*) AS also_wrong_still_out
FROM loans
WHERE returned_on <> '';

.print ''
.print '--- 3.4 the only correct test'
SELECT COUNT(*) AS still_out
FROM loans
WHERE returned_on IS NULL;

.print ''
.print '--- 3.5 the mirror trap: "not from Pune" quietly drops members with no city'
SELECT COUNT(*) AS naive_not_pune
FROM members
WHERE city <> 'Pune';

SELECT COUNT(*) AS honest_not_pune
FROM members
WHERE city IS NULL OR city <> 'Pune';

SELECT COUNT(*) AS total_members FROM members;

.print ''
.print '--- 3.6 IS NOT DISTINCT FROM: the NULL-safe equality, spelled IS in SQLite'
SELECT COUNT(*) AS unrated_via_is
FROM books
WHERE rating IS NULL;

.print ''
.print '--- 3.7 COALESCE and IFNULL substitute a value for the absence of one'
SELECT title,
       rating                       AS raw_rating,
       COALESCE(rating, 0.0)        AS rating_as_zero,
       IFNULL(genre, 'unclassified') AS genre_filled
FROM books
WHERE rating IS NULL OR genre IS NULL
ORDER BY book_id;

.print ''
.print '--- 3.8 NULLIF is the inverse: turn a sentinel value back into NULL'
SELECT NULLIF(0, 0) AS zero_becomes_null, NULLIF(5, 0) AS five_stays_five;

-- 04 — ORDER BY, DISTINCT, LIMIT and OFFSET
--
-- Run:  sqlite3 -header -column examples/library.db < examples/queries/04-sorting.sql

.print '--- 4.1 two sort keys: genre ascending, then rating descending inside each genre'
SELECT genre, title, rating
FROM books
WHERE genre IS NOT NULL AND rating IS NOT NULL
ORDER BY genre ASC, rating DESC
LIMIT 12;

.print ''
.print '--- 4.2 where NULLs sort in SQLite: first when ascending, last when descending'
SELECT title, rating
FROM books
ORDER BY rating ASC
LIMIT 6;

.print ''
.print '--- 4.3 the same column descending — the NULLs move to the end'
SELECT title, rating
FROM books
ORDER BY rating DESC
LIMIT 6;

.print ''
.print '--- 4.4 forcing NULLs last regardless of direction'
SELECT title, rating
FROM books
ORDER BY rating IS NULL, rating ASC
LIMIT 6;

.print ''
.print '--- 4.5 SQLite also spells it out: NULLS LAST'
SELECT title, rating
FROM books
ORDER BY rating ASC NULLS LAST
LIMIT 6;

.print ''
.print '--- 4.6 ORDER BY can use a SELECT alias, on every engine, because it runs after SELECT'
SELECT title, pages * 2 AS reading_minutes
FROM books
WHERE pages > 400
ORDER BY reading_minutes DESC;

.print ''
.print '--- 4.6b SQLite ALSO allows the alias in WHERE, as an extension. Standard SQL does'
.print '         not, and PostgreSQL rejects it. Portable code repeats the expression.'
SELECT COUNT(*) AS accepted_by_sqlite
FROM (SELECT title, pages * 2 AS reading_minutes FROM books WHERE reading_minutes > 800);

SELECT COUNT(*) AS the_portable_spelling
FROM (SELECT title, pages * 2 AS reading_minutes FROM books WHERE pages * 2 > 800);

.print ''
.print '--- 4.7 DISTINCT removes duplicate ROWS, not duplicate values in one column'
SELECT DISTINCT author FROM books ORDER BY author;

.print ''
.print '--- 4.8 DISTINCT over two columns keeps a row per distinct PAIR'
SELECT DISTINCT author, genre FROM books ORDER BY author, genre;

.print ''
.print '--- 4.9 top-N: LIMIT without ORDER BY is a coin toss, so always pair them'
SELECT title, rating
FROM books
ORDER BY rating DESC NULLS LAST, title ASC
LIMIT 5;

.print ''
.print '--- 4.10 page two of the same list, via OFFSET'
SELECT title, rating
FROM books
ORDER BY rating DESC NULLS LAST, title ASC
LIMIT 5 OFFSET 5;

-- 05 — the five aggregates, and what NULL does to each of them
--
-- Run:  sqlite3 -header -column examples/library.db < examples/queries/05-aggregates.sql
--
-- The rule that explains everything below: every aggregate except COUNT(*)
-- IGNORES NULL inputs. It does not treat them as zero. It does not treat them
-- as anything. They are removed before the arithmetic starts.

.print '--- 5.1 COUNT(*) counts ROWS; COUNT(column) counts NON-NULL VALUES in that column'
SELECT COUNT(*)               AS rows_total,
       COUNT(rating)          AS rows_with_a_rating,
       COUNT(genre)           AS rows_with_a_genre,
       COUNT(*) - COUNT(rating) AS unrated_books
FROM books;

.print ''
.print '--- 5.2 COUNT(DISTINCT column) counts distinct non-NULL values'
SELECT COUNT(author)          AS author_cells,
       COUNT(DISTINCT author) AS distinct_authors,
       COUNT(DISTINCT genre)  AS distinct_genres_excluding_null
FROM books;

.print ''
.print '--- 5.3 AVG over a column with NULLs divides by the NON-NULL count'
SELECT ROUND(AVG(rating), 4)                  AS avg_ignoring_nulls,
       ROUND(SUM(rating) / COUNT(rating), 4)  AS same_thing_by_hand,
       ROUND(SUM(rating) / COUNT(*), 4)       AS avg_if_nulls_counted_as_zero
FROM books;

.print ''
.print '--- 5.4 and the version people mean when they "fix" the NULLs — a different number'
SELECT ROUND(AVG(COALESCE(rating, 0.0)), 4) AS avg_with_nulls_as_zero
FROM books;

.print ''
.print '--- 5.5 SUM of an all-NULL set is NULL, not 0. TOTAL is the same sum returning 0.0'
SELECT SUM(rating)   AS sum_of_no_rows,
       TOTAL(rating) AS total_of_no_rows,
       COUNT(*)      AS matching_rows
FROM books
WHERE genre = 'no-such-genre';

.print ''
.print '--- 5.6 MIN and MAX also skip NULLs entirely'
SELECT MIN(rating) AS lowest_rating,
       MAX(rating) AS highest_rating,
       MIN(published_year) AS earliest_year,
       MAX(published_year) AS latest_year
FROM books;

.print ''
.print '--- 5.7 an aggregate over zero rows still returns exactly one row'
SELECT COUNT(*) AS n, AVG(rating) AS avg_rating, MAX(pages) AS longest
FROM books
WHERE published_year = 1066;

-- 06 — GROUP BY: one output row per distinct grouping key
--
-- Run:  sqlite3 -header -column examples/library.db < examples/queries/06-group-by.sql
--
-- GROUP BY collapses the rows that survived WHERE into buckets. After it runs,
-- the only things you may ask for are the grouping key itself and aggregates
-- over the bucket — because there is no longer one row to take a value from.

.print '--- 6.1 how many books per genre, commonest first'
SELECT genre, COUNT(*) AS n
FROM books
GROUP BY genre
ORDER BY n DESC, genre;

.print ''
.print '--- 6.2 GROUP BY puts all the NULLs in ONE bucket — the one place NULLs are treated as equal'
SELECT IFNULL(genre, '(unclassified)') AS genre_label, COUNT(*) AS n
FROM books
GROUP BY genre
ORDER BY n DESC, genre_label;

.print ''
.print '--- 6.3 two aggregates per bucket, and the COUNT gap that reveals the NULLs'
SELECT IFNULL(genre, '(unclassified)') AS genre_label,
       COUNT(*)                        AS books,
       COUNT(rating)                   AS rated,
       ROUND(AVG(rating), 3)           AS avg_rating
FROM books
GROUP BY genre
ORDER BY books DESC, genre_label;

.print ''
.print '--- 6.4 grouping by an EXPRESSION rather than a column: books per decade'
SELECT (published_year / 10) * 10 AS decade, COUNT(*) AS n
FROM books
WHERE published_year IS NOT NULL
GROUP BY decade
ORDER BY decade;

.print ''
.print '--- 6.5 grouping by two keys gives one row per combination that actually occurs'
SELECT author, IFNULL(genre, '(unclassified)') AS genre_label, COUNT(*) AS n
FROM books
GROUP BY author, genre
ORDER BY author, genre_label;

.print ''
.print '--- 6.6 the loans table: who borrows most'
SELECT member_id, COUNT(*) AS loans_taken
FROM loans
GROUP BY member_id
ORDER BY loans_taken DESC, member_id
LIMIT 5;

.print ''
.print '--- 6.7 an aggregate over a CASE is how you count a subset inside a bucket'
SELECT member_id,
       COUNT(*)                                      AS loans_taken,
       SUM(CASE WHEN returned_on IS NULL THEN 1 ELSE 0 END) AS still_out,
       COUNT(returned_on)                            AS returned
FROM loans
GROUP BY member_id
ORDER BY still_out DESC, member_id;

.print ''
.print '--- 6.8 WHERE runs BEFORE GROUP BY, so this counts only the loans of 2026 Q1'
SELECT member_id, COUNT(*) AS q1_loans
FROM loans
WHERE borrowed_on BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY member_id
ORDER BY q1_loans DESC, member_id
LIMIT 5;

-- 07 — HAVING: the filter that WHERE cannot express
--
-- Run:  sqlite3 -header -column examples/library.db < examples/queries/07-having.sql
--
-- WHERE filters ROWS before grouping. HAVING filters GROUPS after grouping.
-- "Authors with more than three books" is not a fact about any single row, so
-- WHERE cannot see it. There is no row that knows how many books its author
-- wrote — only the bucket knows.

.print '--- 7.1 authors with more than three books in the catalogue'
SELECT author, COUNT(*) AS titles
FROM books
GROUP BY author
HAVING COUNT(*) > 3
ORDER BY titles DESC, author;

.print ''
.print '--- 7.2 the attempt WHERE cannot make (kept as a comment, because it is an error)'
--  SELECT author, COUNT(*) AS titles FROM books WHERE COUNT(*) > 3 GROUP BY author;
--  -> Error: in prepare, misuse of aggregate: COUNT()
.print '     see the comment in this file, and section 3 of tests/run_tests.sh'

.print ''
.print '--- 7.3 WHERE and HAVING in the same query, each doing its own job'
SELECT genre, COUNT(*) AS n, ROUND(AVG(rating), 3) AS avg_rating
FROM books
WHERE published_year >= 2000        -- throws away rows
GROUP BY genre
HAVING COUNT(*) >= 2                -- throws away buckets
ORDER BY n DESC, genre;

.print ''
.print '--- 7.4 HAVING on an aggregate that is not in the SELECT list at all'
SELECT genre
FROM books
GROUP BY genre
HAVING AVG(pages) > 350
ORDER BY genre;

.print ''
.print '--- 7.5 books borrowed more than twice — the classic popularity query'
SELECT book_id, COUNT(*) AS times_borrowed
FROM loans
GROUP BY book_id
HAVING COUNT(*) > 2
ORDER BY times_borrowed DESC, book_id;

.print ''
.print '--- 7.6 HAVING on an expression built from TWO aggregates: two or more books still out'
SELECT member_id,
       COUNT(*)                     AS loans_taken,
       COUNT(*) - COUNT(returned_on) AS still_out
FROM loans
GROUP BY member_id
HAVING COUNT(*) - COUNT(returned_on) >= 2
ORDER BY still_out DESC, member_id;

.print ''
.print '--- 7.7 SQLite lets a bare column ride along in an aggregate query; most engines do not'
SELECT genre, title, COUNT(*) AS n
FROM books
GROUP BY genre
ORDER BY genre;
.print '     the title above is ONE arbitrary row from each bucket, not a summary.'
.print '     PostgreSQL rejects this query outright. Do not rely on it.'

-- 08 — computed columns, aliases, scalar functions and CASE
--
-- Run:  sqlite3 -header -column examples/library.db < examples/queries/08-case-and-functions.sql
--
-- A scalar function takes one row's values and returns one value. An aggregate
-- takes many rows and returns one value. That single distinction decides which
-- clause each one is legal in.

.print '--- 8.1 a computed column with an alias'
SELECT title,
       pages,
       ROUND(pages / 250.0, 2) AS evenings_needed
FROM books
ORDER BY evenings_needed DESC
LIMIT 5;

.print ''
.print '--- 8.2 the string functions worth knowing'
SELECT UPPER(SUBSTR(author, 1, 1)) AS initial,
       LENGTH(title)               AS title_length,
       LOWER(genre)                AS genre_lower,
       REPLACE(title, 'The ', '')  AS title_without_the,
       TRIM('   padded   ')        AS trimmed
FROM books
WHERE book_id IN (1, 15)
ORDER BY book_id;

.print ''
.print '--- 8.3 concatenation is || in SQL, not + and not a function'
SELECT title || ' (' || author || ')' AS citation
FROM books
ORDER BY book_id
LIMIT 3;

.print ''
.print '--- 8.4 numeric and null-handling scalars'
SELECT ABS(-7)                    AS abs_value,
       ROUND(3.14159, 3)          AS rounded,
       CAST('42' AS INTEGER) + 1  AS cast_then_add,
       TYPEOF(rating)             AS rating_type,
       TYPEOF(NULL)               AS null_type
FROM books
WHERE book_id = 4;

.print ''
.print '--- 8.5 date functions: SQLite stores dates as text and reads them with these'
SELECT borrowed_on,
       STRFTIME('%Y-%m', borrowed_on)               AS month_bucket,
       JULIANDAY(returned_on) - JULIANDAY(borrowed_on) AS days_held
FROM loans
WHERE returned_on IS NOT NULL
ORDER BY loan_id
LIMIT 5;

.print ''
.print '--- 8.6 CASE turns a value into a label — the SQL equivalent of if/elif/else'
SELECT title,
       rating,
       CASE
         WHEN rating IS NULL  THEN 'unrated'
         WHEN rating >= 4.5   THEN 'excellent'
         WHEN rating >= 4.0   THEN 'good'
         WHEN rating >= 3.5   THEN 'fair'
         ELSE                      'poor'
       END AS band
FROM books
ORDER BY book_id
LIMIT 10;

.print ''
.print '--- 8.7 GROUP BY over a CASE expression: a histogram of rating bands'
SELECT CASE
         WHEN rating IS NULL  THEN 'unrated'
         WHEN rating >= 4.5   THEN 'excellent'
         WHEN rating >= 4.0   THEN 'good'
         WHEN rating >= 3.5   THEN 'fair'
         ELSE                      'poor'
       END AS band,
       COUNT(*) AS n
FROM books
GROUP BY band
ORDER BY n DESC, band;

.print ''
.print '--- 8.8 the WHEN order matters: put the NULL branch first or it never fires'
SELECT COUNT(*) AS mislabelled_as_poor
FROM books
WHERE rating IS NULL
  AND (CASE WHEN rating >= 4.0 THEN 'good' ELSE 'poor' END) = 'poor';

-- Day 086 lab — the seed for library.db
--
-- Everything in this lab runs against this one database. It is deliberately
-- small enough to check by hand and deliberately full of NULLs, because NULL
-- is where the day's real lessons live.
--
-- Build it with:
--   bash examples/build_db.sh
-- or directly:
--   rm -f examples/library.db && sqlite3 examples/library.db < examples/seed.sql
--
-- Three deliberate holes are drilled into the data, and every one of them is
-- an exercise later:
--   * books.rating is NULL for books nobody has rated yet
--   * books.genre is NULL for books that were never classified
--   * members.city is NULL for members who did not give one
--   * loans.returned_on is NULL for books that are still out
--
-- The last one carries the most weight: "still on loan" is not a value, it is
-- the absence of one, and that is why `returned_on <> ''` finds nothing.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS loans;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS books;

CREATE TABLE books (
  book_id        INTEGER PRIMARY KEY,
  title          TEXT    NOT NULL,
  author         TEXT    NOT NULL,
  genre          TEXT,             -- NULL = never classified
  published_year INTEGER,          -- NULL = publication date unknown
  pages          INTEGER,
  rating         REAL,             -- NULL = not yet rated; 1.0 to 5.0 otherwise
  copies         INTEGER NOT NULL
);

CREATE TABLE members (
  member_id  INTEGER PRIMARY KEY,
  full_name  TEXT NOT NULL,
  joined_on  TEXT NOT NULL,        -- ISO-8601 date, stored as TEXT
  city       TEXT,                 -- NULL = not supplied
  email      TEXT
);

CREATE TABLE loans (
  loan_id     INTEGER PRIMARY KEY,
  book_id     INTEGER NOT NULL REFERENCES books(book_id),
  member_id   INTEGER NOT NULL REFERENCES members(member_id),
  borrowed_on TEXT NOT NULL,
  returned_on TEXT               -- NULL = still on loan
);

INSERT INTO books (book_id, title, author, genre, published_year, pages, rating, copies) VALUES
  (1,  'The Silent Archive',        'Priya Raman',      'mystery',    2018, 312, 4.5,  3),
  (2,  'Grammar of Machines',       'Ada Fenwick',      'science',    2011, 480, 4.8,  2),
  (3,  'Salt and Longitude',        'Tomas Berg',       'history',    1997, 528, 3.9,  1),
  (4,  'The Quiet Algorithm',       'Ada Fenwick',      'science',    2021, 264, NULL, 4),
  (5,  'Nine Rivers',               'Kofi Mensah',      'fiction',    2005, 398, 4.1,  2),
  (6,  'A Map of Small Errors',     'Priya Raman',      'mystery',    2022, 288, 4.4,  5),
  (7,  'Ledger of Tides',           'Marta Iglesias',   'fiction',    1988, 356, 3.2,  1),
  (8,  'The Glasshouse Problem',    'Ada Fenwick',      'science',    2016, 344, 4.6,  3),
  (9,  'Winter Counting',           'Hana Sato',        NULL,         2019, 210, 4.0,  2),
  (10, 'The Lost Cartographers',    'Tomas Berg',       'history',    2003, 612, 4.2,  1),
  (11, 'Field Notes on Rain',       'Hana Sato',        'poetry',     2014, 96,  NULL, 6),
  (12, 'Eleven Ways to Fail',       'Daniel Okoro',     'science',    2020, 302, 3.7,  2),
  (13, 'The Anchor Room',           'Marta Iglesias',   'mystery',    2012, 274, 4.3,  3),
  (14, 'Continental Drift Blues',   'Kofi Mensah',      'fiction',    2017, 421, 3.5,  1),
  (15, 'Small Gods of Arithmetic',  'Ada Fenwick',      NULL,         NULL, 198, 4.9,  1),
  (16, 'The Paper Observatory',     'Hana Sato',        'science',    2009, 366, 4.4,  2),
  (17, 'Coasts of Elsewhere',       'Tomas Berg',       'history',    2023, 455, NULL, 4),
  (18, 'A Winter Grammar',          'Priya Raman',      'poetry',     1999, 128, 3.8,  1),
  (19, 'The Long Instrument',       'Daniel Okoro',     'fiction',    2015, 512, 4.7,  2),
  (20, 'Notes Toward a Machine',    'Ada Fenwick',      'science',    2024, 240, NULL, 3),
  (21, 'The Weather in Numbers',    'Marta Iglesias',   'science',    2013, 288, 4.0,  2),
  (22, 'Wintering Grounds',         'Kofi Mensah',      NULL,         2007, 334, 3.4,  1),
  (23, 'The Second Archive',        'Priya Raman',      'mystery',    2025, 296, 4.6,  4),
  (24, 'Poems for a Dry Season',    'Hana Sato',        'poetry',     2021, 112, 4.2,  2);

INSERT INTO members (member_id, full_name, joined_on, city, email) VALUES
  (1,  'Anita Desai',      '2021-03-14', 'Pune',      'anita.desai@library.invalid'),
  (2,  'Ben Oyelaran',     '2022-07-02', 'Lagos',     'ben.oyelaran@library.invalid'),
  (3,  'Chen Wei',         '2020-11-30', 'Singapore', NULL),
  (4,  'Dana Kowalski',    '2023-01-19', NULL,        'dana.k@library.invalid'),
  (5,  'Elif Demir',       '2019-05-08', 'Izmir',     'elif.demir@library.invalid'),
  (6,  'Farid Nazari',     '2024-02-11', 'Pune',      NULL),
  (7,  'Grace Mwangi',     '2022-09-23', 'Nairobi',   'grace.mwangi@library.invalid'),
  (8,  'Hugo Almeida',     '2018-08-16', NULL,        NULL),
  (9,  'Ines Moreau',      '2023-10-05', 'Lyon',      'ines.moreau@library.invalid'),
  (10, 'Jonas Lindqvist',  '2025-04-27', 'Uppsala',   'jonas.l@library.invalid'),
  (11, 'Keiko Tanaka',     '2021-12-01', 'Sendai',    'keiko.tanaka@library.invalid'),
  (12, 'Luis Ferreira',    '2024-06-30', 'Porto',     NULL);

INSERT INTO loans (loan_id, book_id, member_id, borrowed_on, returned_on) VALUES
  (1,  1,  1,  '2026-01-05', '2026-01-19'),
  (2,  2,  1,  '2026-01-05', '2026-02-02'),
  (3,  6,  2,  '2026-01-11', '2026-01-25'),
  (4,  8,  3,  '2026-01-14', NULL),
  (5,  5,  4,  '2026-01-18', '2026-02-01'),
  (6,  13, 5,  '2026-01-20', '2026-01-27'),
  (7,  1,  6,  '2026-01-22', NULL),
  (8,  19, 7,  '2026-01-26', '2026-02-16'),
  (9,  4,  2,  '2026-02-01', '2026-02-15'),
  (10, 11, 8,  '2026-02-03', NULL),
  (11, 16, 9,  '2026-02-04', '2026-02-18'),
  (12, 2,  10, '2026-02-07', '2026-02-21'),
  (13, 23, 1,  '2026-02-09', NULL),
  (14, 6,  11, '2026-02-10', '2026-02-24'),
  (15, 3,  12, '2026-02-12', '2026-03-05'),
  (16, 8,  4,  '2026-02-14', '2026-02-28'),
  (17, 21, 5,  '2026-02-15', NULL),
  (18, 6,  7,  '2026-02-17', '2026-03-03'),
  (19, 10, 3,  '2026-02-19', '2026-03-12'),
  (20, 1,  9,  '2026-02-21', '2026-03-07'),
  (21, 24, 2,  '2026-02-23', NULL),
  (22, 19, 6,  '2026-02-25', '2026-03-11'),
  (23, 13, 10, '2026-03-01', '2026-03-15'),
  (24, 6,  12, '2026-03-02', NULL),
  (25, 20, 1,  '2026-03-04', '2026-03-18'),
  (26, 9,  8,  '2026-03-06', '2026-03-20'),
  (27, 2,  11, '2026-03-08', NULL),
  (28, 17, 5,  '2026-03-09', '2026-03-23'),
  (29, 1,  4,  '2026-03-11', '2026-03-25'),
  (30, 12, 7,  '2026-03-13', NULL),
  (31, 6,  9,  '2026-03-15', '2026-03-29'),
  (32, 8,  1,  '2026-03-17', '2026-03-31'),
  (33, 23, 2,  '2026-03-19', NULL),
  (34, 5,  3,  '2026-03-21', '2026-04-04'),
  (35, 19, 12, '2026-03-23', '2026-04-06'),
  (36, 16, 6,  '2026-03-25', NULL),
  (37, 1,  11, '2026-03-27', '2026-04-10'),
  (38, 13, 8,  '2026-03-29', '2026-04-12'),
  (39, 6,  10, '2026-04-01', NULL),
  (40, 21, 4,  '2026-04-03', '2026-04-17'),
  (41, 2,  5,  '2026-04-05', NULL),
  (42, 23, 9,  '2026-04-07', '2026-04-21'),
  (43, 8,  7,  '2026-04-09', NULL),
  (44, 6,  1,  '2026-04-11', '2026-04-25'),
  (45, 15, 3,  '2026-04-13', NULL);

-- A last sanity line so a successful build is visible rather than silent.
SELECT 'seeded: ' || (SELECT COUNT(*) FROM books) || ' books, '
                  || (SELECT COUNT(*) FROM members) || ' members, '
                  || (SELECT COUNT(*) FROM loans) || ' loans';

lesson_id: D086
day: 86
kind: guided-build
languages: [sql, python, bash]
setup_commands:
  - cd labs/sections/programming-with-python/day-086-select-filtering-sorting-and-aggregating
  - sqlite3 --version
  - python3 --version
  - bash examples/build_db.sh
run_commands:
  - sqlite3 -header -column examples/library.db < examples/queries/01-filters.sql
  - sqlite3 -header -column examples/library.db < examples/queries/02-patterns.sql
  - sqlite3 -header -column examples/library.db < examples/queries/03-null-traps.sql
  - sqlite3 -header -column examples/library.db < examples/queries/04-sorting.sql
  - sqlite3 -header -column examples/library.db < examples/queries/05-aggregates.sql
  - sqlite3 -header -column examples/library.db < examples/queries/06-group-by.sql
  - sqlite3 -header -column examples/library.db < examples/queries/07-having.sql
  - sqlite3 -header -column examples/library.db < examples/queries/08-case-and-functions.sql
  - python3 examples/groupby_from_scratch.py
  - sqlite3 examples/library.db < starter/exercises.sql
  - bash starter/check.sh
  - sqlite3 examples/library.db < examples/exercise-answers.sql
test_commands:
  - bash tests/run_tests.sh
cleanup_commands:
  - rm -f examples/library.db
  - 'git checkout -- starter/exercises.sql  # optional: reset your work'
requires_network: false
requires_api_key: false
estimated_minutes: 30
last_executed: '2026-08-16'
executed_on: 'macOS 26.5.2 (Apple Silicon, arm64), sqlite3 3.51.0, Python 3.14.0, bash 3.2.57 — bash tests/run_tests.sh -> 124 checks, 0 failure(s), exit 0'

# Dependencies

There are none to install. That is not laziness; it is the point of the week.

| Tool | Version verified here | Why this lab needs it | Licence |
| --- | --- | --- | --- |
| `sqlite3` (the shell) | 3.51.0 | Runs every query in `examples/queries/`, the seed, and the whole test harness | Public domain |
| `python3` | 3.14.0 | Runs `examples/groupby_from_scratch.py`, which builds GROUP BY out of a dictionary of accumulators and then checks SQL agrees | Python Software Foundation License |
| `bash` | 3.2.57 | Runs `tests/run_tests.sh`, `examples/build_db.sh` and `starter/check.sh` | GPL-3.0 |
| `sqlite3` (the Python module) | Standard library | The comparison script's connection to the same database file | Python Software Foundation License |

All four are free, and three of the four are already on any macOS or Linux
machine you would be reading this on.

## Installing the one that might be missing

macOS ships the `sqlite3` shell. On Debian and Ubuntu the shell is packaged
separately from the library, so it is possible to have a perfectly working
`import sqlite3` in Python and no `sqlite3` command at all:

```bash
sudo apt install sqlite3      # Debian, Ubuntu
sudo dnf install sqlite       # Fedora
```

`tests/run_tests.sh` checks for the command before it does anything else and
stops with that instruction rather than failing halfway through a run.

## What is deliberately absent

**No ORM.** Not SQLAlchemy, not Django's, not Peewee. Every one of them is a
good tool and you will meet them later. Today the whole subject is the shape of
a SELECT and the order its clauses run in, and an ORM's job is to hide exactly
that. Learning the abstraction before the thing it abstracts is how people end
up unable to explain why their query is slow.

**No pandas.** The Alternatives section of the lesson covers it honestly as the
dataframe answer to the same questions, including where it wins and where it
loses. It is not installed here, and the lesson says so rather than showing
output that was never produced.

**No database server.** SQLite is a library and a file. There is no daemon to
start, no port to open, no user to create, and nothing to uninstall afterwards
except one file you can delete with `rm`.

**No seed data from the internet.** `examples/seed.sql` is 45 loans, 24 books
and 12 members written by hand, chosen so that every number in the tests can be
checked by reading the file. The whole lab runs with the network switched off.

# This lab installs nothing. There is no pip line to run.
#
# It needs the sqlite3 shell, which macOS ships and which Debian and Ubuntu
# package as `sqlite3`, plus python3 for the from-scratch comparison. The
# `sqlite3` module python3 uses is part of the standard library.
#
# Verified on the authoring machine, 2026-08-16:
#   sqlite3  3.51.0
#   python3  3.14.0
#   bash     3.2.57
#
# See README.md in this directory for why the list is empty on purpose.

#!/usr/bin/env bash
# Score starter/exercises.sql against the required answers.
#
# Run from the lab directory:
#   bash starter/check.sh
#
# Exits 0 when all twelve are right, non-zero otherwise. The required answers
# are printed in the exercise comments too — nothing here is hidden. What is
# hidden is the QUERY, which is the part you are meant to write.
set -u

lab_dir="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
db="${lab_dir}/examples/library.db"

if [ ! -f "${db}" ]; then
  echo "Building the database first (it was missing)."
  bash "${lab_dir}/examples/build_db.sh" >/dev/null || exit 1
fi

# label -> required answer. Same twelve values as the exercise comments.
expected_labels="ex01 ex02 ex03 ex04 ex05 ex06 ex07 ex08 ex09 ex10 ex11 ex12"
expected_for() {
  case "$1" in
    ex01) echo "15" ;;
    ex02) echo "10" ;;
    ex03) echo "2" ;;
    ex04) echo "4" ;;
    ex05) echo "4.16" ;;
    ex06) echo "4" ;;
    ex07) echo "Ledger of Tides" ;;
    ex08) echo "6" ;;
    ex09) echo "Ada Fenwick" ;;
    ex10) echo "3" ;;
    ex11) echo "28.0" ;;
    ex12) echo "4" ;;
    *)    echo "" ;;
  esac
}

actual="$(sqlite3 "${db}" < "${lab_dir}/starter/exercises.sql" 2>&1)" || {
  echo "Your exercises.sql did not run. SQLite said:"
  echo "${actual}"
  exit 1
}

right=0
wrong=0
echo "Exercise   your answer                required"
echo "---------  -------------------------  -------------------------"
for label in ${expected_labels}; do
  want="$(expected_for "${label}")"
  got="$(printf '%s\n' "${actual}" | grep "^${label}|" | head -1 | cut -d'|' -f2-)"
  if [ -z "${got}" ]; then
    got="(no ${label} line)"
  fi
  if [ "${got}" = "${want}" ]; then
    printf '%-9s  %-25s  %-25s  ok\n' "${label}" "${got}" "${want}"
    right=$((right + 1))
  else
    printf '%-9s  %-25s  %-25s  WRONG\n' "${label}" "${got}" "${want}"
    wrong=$((wrong + 1))
  fi
done

echo
echo "${right} correct, ${wrong} still wrong."
[ "${wrong}" -eq 0 ] || exit 1
echo "All twelve. Every one of them ran and lied to you before you fixed it."

-- Day 086 lab — YOUR WORK. Twelve numbered exercises.
--
-- Run them at any time, from the lab directory:
--   sqlite3 examples/library.db < starter/exercises.sql
-- Score them:
--   bash starter/check.sh
--
-- Every exercise below RUNS right now. None of them is blank, and none of them
-- is an error. Each one is a query somebody wrote in a hurry, which returns a
-- confident, well-formatted, WRONG answer. Your job is to make each one right.
--
-- That is the shape of the whole day. SQL almost never tells you that you asked
-- the wrong question; it just answers the one you actually asked.
--
-- Each statement prints one line: exNN|value. `bash starter/check.sh` compares
-- those values to the required answers and tells you which are still wrong.
-- Do NOT change the exNN label or the number of statements.

.mode list
.headers off

-- ---------------------------------------------------------------------------
-- Exercise 1 — How many loans are still outstanding?
-- A book that is still out has no returned_on date at all.
-- Required answer: 15
-- Hint: NULL is never equal to anything, including NULL.
-- ---------------------------------------------------------------------------
SELECT 'ex01|' || (
  SELECT COUNT(*) FROM loans WHERE returned_on = NULL
);

-- ---------------------------------------------------------------------------
-- Exercise 2 — How many members are NOT from Pune?
-- A member who never told us their city is certainly not from Pune.
-- Required answer: 10
-- Hint: `city <> 'Pune'` is UNKNOWN when city is NULL, and WHERE keeps only TRUE.
-- ---------------------------------------------------------------------------
SELECT 'ex02|' || (
  SELECT COUNT(*) FROM members WHERE city <> 'Pune'
);

-- ---------------------------------------------------------------------------
-- Exercise 3 — How many titles contain the word "archive", in any case?
-- Required answer: 2
-- Hint: GLOB is case-sensitive. One of SQLite's two pattern matchers is not.
-- ---------------------------------------------------------------------------
SELECT 'ex03|' || (
  SELECT COUNT(*) FROM books WHERE title GLOB '*archive*'
);

-- ---------------------------------------------------------------------------
-- Exercise 4 — How many books were published in 2015, 2016, 2017 or 2018?
-- Required answer: 4
-- Hint: BETWEEN includes both endpoints. Strict inequalities do not.
-- ---------------------------------------------------------------------------
SELECT 'ex04|' || (
  SELECT COUNT(*) FROM books WHERE published_year > 2015 AND published_year < 2018
);

-- ---------------------------------------------------------------------------
-- Exercise 5 — What is the average rating of the books that HAVE a rating,
-- rounded to two decimal places?
-- Required answer: 4.16
-- Hint: an unrated book is not a book rated zero. Do not invent data.
-- ---------------------------------------------------------------------------
SELECT 'ex05|' || (
  SELECT ROUND(AVG(COALESCE(rating, 0.0)), 2) FROM books
);

-- ---------------------------------------------------------------------------
-- Exercise 6 — How many books have never been rated?
-- Required answer: 4
-- Hint: COUNT(*) and COUNT(column) count different things. The gap is the answer.
-- ---------------------------------------------------------------------------
SELECT 'ex06|' || (
  SELECT COUNT(rating) FROM books
);

-- ---------------------------------------------------------------------------
-- Exercise 7 — What is the title of the LOWEST-rated book that has a rating?
-- Required answer: Ledger of Tides
-- Hint: ascending order puts the NULLs first in SQLite, and a NULL is not a
-- low rating — it is no rating.
-- ---------------------------------------------------------------------------
SELECT 'ex07|' || (
  SELECT title FROM books ORDER BY rating ASC LIMIT 1
);

-- ---------------------------------------------------------------------------
-- Exercise 8 — How many genre buckets does the catalogue have, counting the
-- unclassified books as one bucket of their own?
-- Required answer: 6
-- Hint: COUNT(DISTINCT genre) skips NULL. GROUP BY does not.
-- ---------------------------------------------------------------------------
SELECT 'ex08|' || (
  SELECT COUNT(DISTINCT genre) FROM books
);

-- ---------------------------------------------------------------------------
-- Exercise 9 — Which author has the most titles in the catalogue?
-- Required answer: Ada Fenwick
-- Hint: ORDER BY defaults to ascending, which gives you the answer to the
-- opposite question.
-- ---------------------------------------------------------------------------
SELECT 'ex09|' || (
  SELECT author FROM books GROUP BY author ORDER BY COUNT(*) LIMIT 1
);

-- ---------------------------------------------------------------------------
-- Exercise 10 — How many authors have MORE THAN THREE titles?
-- Required answer: 3
-- Hint: "how many titles this author has" is a fact about a group, not about a
-- row, so WHERE cannot see it. There is exactly one clause that can.
-- ---------------------------------------------------------------------------
SELECT 'ex10|' || (
  SELECT COUNT(DISTINCT author) FROM books
);

-- ---------------------------------------------------------------------------
-- Exercise 11 — For how many days was loan 2 held?
-- Required answer: 28.0
-- Hint: SQLite has no date type. Those two columns are TEXT, and subtracting
-- one piece of text from another gives you arithmetic on whatever number the
-- engine can squeeze out of the front of each string.
-- ---------------------------------------------------------------------------
SELECT 'ex11|' || (
  SELECT returned_on - borrowed_on FROM loans WHERE loan_id = 2
);

-- ---------------------------------------------------------------------------
-- Exercise 12 — How many books land in the 'unrated' band of this histogram?
-- Required answer: 4
-- Hint: CASE evaluates its WHEN branches in order and stops at the first TRUE.
-- A comparison against NULL is never TRUE, so it falls through to the ELSE.
-- ---------------------------------------------------------------------------
SELECT 'ex12|' || (
  SELECT COUNT(*) FROM (
    SELECT CASE
             WHEN rating >= 4.5 THEN 'excellent'
             WHEN rating >= 4.0 THEN 'good'
             ELSE                    'poor'
           END AS band
    FROM books
  )
  WHERE band = 'unrated'
);

#!/usr/bin/env bash
# Tests for the Day 086 lab. Run from the lab directory:
#   bash tests/run_tests.sh
#
# Every check here compares an ACTUAL RESULT VALUE against a number or string
# that was worked out from the seed data by hand. A test that only proves a
# query parsed would pass on every one of the twelve broken queries in
# starter/exercises.sql, which is exactly the failure mode this day is about.
#
# The suite builds its own throwaway database under a mktemp -d directory and
# removes it in a trap, so it never touches examples/library.db and never
# depends on what you did to it. Nothing here reaches the network, nothing
# needs sudo, and nothing survives the run.
set -u

lab_dir="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
checks=0
failures=0
work_root=""

cleanup() {
  [ -n "${work_root}" ] && [ -d "${work_root}" ] && rm -rf "${work_root}"
}
trap cleanup EXIT INT TERM

pass() { checks=$((checks + 1)); echo "  ok: $1"; }
fail() { checks=$((checks + 1)); failures=$((failures + 1)); echo "  FAIL: $1"; }

# check <label> <expected> <actual>
check() {
  if [ "$2" = "$3" ]; then
    pass "$1 = $3"
  else
    fail "$1: expected [$2] but got [$3]"
  fi
}

# check_ne <label> <must-not-equal> <actual>
check_ne() {
  if [ "$2" != "$3" ]; then
    pass "$1 (is not $2)"
  else
    fail "$1: value must NOT be [$2] but it is"
  fi
}

if ! command -v sqlite3 >/dev/null 2>&1; then
  echo "FAIL: the sqlite3 shell is not on PATH." >&2
  echo "  macOS ships it; on Debian or Ubuntu: sudo apt install sqlite3" >&2
  exit 1
fi
python_bin="${PYTHON:-python3}"
if ! command -v "${python_bin}" >/dev/null 2>&1; then
  echo "FAIL: python3 is not on PATH (needed for the from-scratch comparison)." >&2
  exit 1
fi

work_root="$(mktemp -d)"
db="${work_root}/library.db"

# q <sql> -> the single value the query returns, with no header and no padding
q() { sqlite3 "${db}" "$1"; }

echo "Day 086 — Ask the Database Questions"
echo "sqlite3 $(sqlite3 --version | cut -d' ' -f1), $(${python_bin} --version)"
echo "throwaway database: ${db}"
echo

echo "1. The seed builds, and builds the same thing every time"
bash "${lab_dir}/examples/build_db.sh" "${db}" >/dev/null 2>&1
if [ -f "${db}" ]; then pass "examples/build_db.sh created the database"
else fail "examples/build_db.sh did not create a database"; fi
check "books"   "24" "$(q 'SELECT COUNT(*) FROM books;')"
check "members" "12" "$(q 'SELECT COUNT(*) FROM members;')"
check "loans"   "45" "$(q 'SELECT COUNT(*) FROM loans;')"
check "the deliberate holes: unrated books"      "4"  "$(q 'SELECT COUNT(*)-COUNT(rating) FROM books;')"
check "the deliberate holes: unclassified books" "3"  "$(q 'SELECT COUNT(*)-COUNT(genre) FROM books;')"
check "the deliberate holes: members with no city" "2" "$(q 'SELECT COUNT(*)-COUNT(city) FROM members;')"
check "the deliberate holes: loans still out"    "15" "$(q 'SELECT COUNT(*)-COUNT(returned_on) FROM loans;')"
# Rebuilding must be idempotent — the same rows, not a doubled table.
bash "${lab_dir}/examples/build_db.sh" "${db}" >/dev/null 2>&1
check "rebuilding gives the same 24 books, not 48" "24" "$(q 'SELECT COUNT(*) FROM books;')"
echo

echo "2. WHERE: comparisons, boolean operators, IN, BETWEEN"
check "science books from 2000 onwards" "7" \
  "$(q "SELECT COUNT(*) FROM books WHERE genre='science' AND published_year>=2000;")"
check "BETWEEN 2015 AND 2018 includes both endpoints" "4" \
  "$(q 'SELECT COUNT(*) FROM books WHERE published_year BETWEEN 2015 AND 2018;')"
check "the same range spelled out with >= and <=" "4" \
  "$(q 'SELECT COUNT(*) FROM books WHERE published_year>=2015 AND published_year<=2018;')"
check "IN over two genres" "7" \
  "$(q "SELECT COUNT(*) FROM books WHERE genre IN ('poetry','mystery');")"
check "brackets change the meaning: (A OR B) AND C" "5" \
  "$(q "SELECT COUNT(*) FROM books WHERE (genre='science' OR genre='history') AND published_year>=2015;")"
check "AND binds tighter than OR: A OR (B AND C)" "8" \
  "$(q "SELECT COUNT(*) FROM books WHERE genre='science' OR genre='history' AND published_year>=2015;")"
# NOT IN drops the NULL-genre rows: 24 - 7 matching - 3 unclassified = 14.
check "NOT IN silently excludes the NULL genres too" "14" \
  "$(q "SELECT COUNT(*) FROM books WHERE genre NOT IN ('poetry','mystery');")"
echo

echo "3. LIKE and GLOB really do differ on case"
check "LIKE %archive% folds case"        "2" "$(q "SELECT COUNT(*) FROM books WHERE title LIKE '%archive%';")"
check "GLOB *archive* does not"          "0" "$(q "SELECT COUNT(*) FROM books WHERE title GLOB '*archive*';")"
check "GLOB *Archive* spelled as stored" "2" "$(q "SELECT COUNT(*) FROM books WHERE title GLOB '*Archive*';")"
check "LIKE underscore matches exactly one character" "The Quiet Algorithm" \
  "$(q "SELECT title FROM books WHERE title LIKE 'The _____ Algorithm';")"
check "one underscore too few matches nothing, and does not error" "0" \
  "$(q "SELECT COUNT(*) FROM books WHERE title LIKE 'The ____ Algorithm';")"
check "GLOB character classes have no LIKE equivalent" "4" \
  "$(q "SELECT COUNT(*) FROM books WHERE title GLOB '[AN]*';")"
echo

echo "4. NULL and three-valued logic"
# An empty result string is how the sqlite3 shell renders a NULL scalar.
check "NULL = NULL is NULL, not 1"  "" "$(q 'SELECT NULL = NULL;')"
check "NULL <> NULL is NULL too"    "" "$(q 'SELECT NULL <> NULL;')"
check "NULL IS NULL is 1"           "1" "$(q 'SELECT NULL IS NULL;')"
check "NULL AND false is FALSE"     "0" "$(q 'SELECT NULL AND 0;')"
check "NULL AND true is UNKNOWN"    "" "$(q 'SELECT NULL AND 1;')"
check "NULL OR true is TRUE"        "1" "$(q 'SELECT NULL OR 1;')"
check "NULL OR false is UNKNOWN"    "" "$(q 'SELECT NULL OR 0;')"
check "NOT NULL is UNKNOWN"         "" "$(q 'SELECT NOT NULL;')"
check "the trap: returned_on = NULL finds nothing" "0" \
  "$(q 'SELECT COUNT(*) FROM loans WHERE returned_on = NULL;')"
check "the other trap: returned_on <> empty string finds the RETURNED ones" "30" \
  "$(q "SELECT COUNT(*) FROM loans WHERE returned_on <> '';")"
check "IS NULL is the only correct test" "15" \
  "$(q 'SELECT COUNT(*) FROM loans WHERE returned_on IS NULL;')"
check "naive not-from-Pune loses the members with no city" "8" \
  "$(q "SELECT COUNT(*) FROM members WHERE city <> 'Pune';")"
check "the honest version keeps them" "10" \
  "$(q "SELECT COUNT(*) FROM members WHERE city IS NULL OR city <> 'Pune';")"
check "and 8 + 2 Pune members would be 10, not 12 — so the naive query lost 2" "2" \
  "$(q "SELECT COUNT(*) FROM members WHERE city = 'Pune';")"
echo

echo "5. The NULL traps must NOT be 'fixed' by inventing data"
# These are the checks that go red if somebody makes the numbers agree by
# writing zeros and empty strings into the holes instead of understanding them.
check_ne "AVG(rating) must not equal the COALESCE-to-zero average" \
  "$(q 'SELECT ROUND(AVG(COALESCE(rating,0.0)),2) FROM books;')" \
  "$(q 'SELECT ROUND(AVG(rating),2) FROM books;')"
check "AVG(rating) ignores the 4 NULLs" "4.16" "$(q 'SELECT ROUND(AVG(rating),2) FROM books;')"
check "COALESCE to zero gives a different, wrong answer" "3.47" \
  "$(q 'SELECT ROUND(AVG(COALESCE(rating,0.0)),2) FROM books;')"
check "SUM over zero matching rows is NULL, not 0" "" \
  "$(q "SELECT SUM(rating) FROM books WHERE genre='no-such-genre';")"
check "TOTAL over the same zero rows is 0.0" "0.0" \
  "$(q "SELECT TOTAL(rating) FROM books WHERE genre='no-such-genre';")"
check "an aggregate over zero rows still returns exactly one row" "1" \
  "$(q 'SELECT COUNT(*) FROM (SELECT AVG(rating) FROM books WHERE published_year=1066);')"
# If a well-meaning fix replaced NULL ratings with 0.0 in the table itself, this
# MIN would become 0.0 and this check would fail.
check "MIN(rating) is a real rating, not an invented zero" "3.2" "$(q 'SELECT MIN(rating) FROM books;')"
echo

echo "6. ORDER BY, DISTINCT, LIMIT and OFFSET"
check "ascending puts NULLs first in SQLite" "" \
  "$(q 'SELECT rating FROM books ORDER BY rating ASC LIMIT 1;')"
check "descending puts them last" "4.9" \
  "$(q 'SELECT rating FROM books ORDER BY rating DESC LIMIT 1;')"
check "NULLS LAST overrides the ascending default" "3.2" \
  "$(q 'SELECT rating FROM books ORDER BY rating ASC NULLS LAST LIMIT 1;')"
check "so does ORDER BY rating IS NULL, rating" "3.2" \
  "$(q 'SELECT rating FROM books ORDER BY rating IS NULL, rating ASC LIMIT 1;')"
# genre ASC first, so the winner is the best book of the ALPHABETICALLY FIRST
# genre — fiction — not the best book overall. Key order is the whole meaning.
check "two sort keys: first key wins, so this is the top FICTION book" "The Long Instrument" \
  "$(q "SELECT title FROM books WHERE genre IS NOT NULL AND rating IS NOT NULL ORDER BY genre ASC, rating DESC LIMIT 1;")"
check "swap the keys and you get the best book overall instead" "Grammar of Machines" \
  "$(q "SELECT title FROM books WHERE genre IS NOT NULL AND rating IS NOT NULL ORDER BY rating DESC, genre ASC LIMIT 1;")"
check "the top MYSTERY needs the genre in the WHERE, not the ORDER BY" "The Second Archive" \
  "$(q "SELECT title FROM books WHERE genre='mystery' ORDER BY rating DESC NULLS LAST LIMIT 1;")"
check "ORDER BY may use a SELECT alias — standard SQL, works everywhere" "The Lost Cartographers" \
  "$(q 'SELECT title, pages*2 AS reading_minutes FROM books ORDER BY reading_minutes DESC LIMIT 1;' | cut -d'|' -f1)"
# SQLite ACCEPTS a SELECT alias in WHERE as an extension; standard SQL does not,
# and PostgreSQL rejects it. Pinning the behaviour here so the lesson's claim
# stays honest about which engine does what.
check "SQLite accepts a SELECT alias in WHERE, as an extension" "6" \
  "$(q 'SELECT COUNT(*) FROM (SELECT title, pages*2 AS reading_minutes FROM books WHERE reading_minutes > 800);')"
check "the portable spelling gives the same six rows" "6" \
  "$(q 'SELECT COUNT(*) FROM (SELECT title, pages*2 AS reading_minutes FROM books WHERE pages*2 > 800);')"
check "DISTINCT over one column: distinct authors" "7" \
  "$(q 'SELECT COUNT(*) FROM (SELECT DISTINCT author FROM books);')"
check "DISTINCT over two columns keeps one row per PAIR" "15" \
  "$(q 'SELECT COUNT(*) FROM (SELECT DISTINCT author, genre FROM books);')"
check "top-N with a deterministic tie-break" "Small Gods of Arithmetic" \
  "$(q 'SELECT title FROM books ORDER BY rating DESC NULLS LAST, title ASC LIMIT 1;')"
check "OFFSET 5 starts page two of the same list" "The Silent Archive" \
  "$(q 'SELECT title FROM books ORDER BY rating DESC NULLS LAST, title ASC LIMIT 1 OFFSET 5;')"
echo

echo "7. Aggregates, and what NULL does to each"
check "COUNT(*) counts rows"              "24" "$(q 'SELECT COUNT(*) FROM books;')"
check "COUNT(rating) counts values"       "20" "$(q 'SELECT COUNT(rating) FROM books;')"
check "COUNT(genre) counts values"        "21" "$(q 'SELECT COUNT(genre) FROM books;')"
check "COUNT(DISTINCT genre) skips NULL"  "5"  "$(q 'SELECT COUNT(DISTINCT genre) FROM books;')"
check "COUNT(DISTINCT author)"            "7"  "$(q 'SELECT COUNT(DISTINCT author) FROM books;')"
check "AVG is SUM over the NON-NULL count" "1" \
  "$(q 'SELECT ROUND(AVG(rating),6) = ROUND(SUM(rating)/COUNT(rating),6) FROM books;')"
check "MIN(published_year)" "1988" "$(q 'SELECT MIN(published_year) FROM books;')"
check "MAX(published_year)" "2025" "$(q 'SELECT MAX(published_year) FROM books;')"
echo

echo "8. GROUP BY"
check "one bucket per distinct genre, NULLs together in one more" "6" \
  "$(q 'SELECT COUNT(*) FROM (SELECT genre FROM books GROUP BY genre);')"
check "the science bucket has 7 books" "7" \
  "$(q "SELECT COUNT(*) FROM books GROUP BY genre HAVING genre='science';")"
check "the unclassified bucket has 3" "3" \
  "$(q 'SELECT COUNT(*) FROM books GROUP BY genre HAVING genre IS NULL;')"
check "grouping by an expression: books published in the 2010s" "9" \
  "$(q 'SELECT n FROM (SELECT (published_year/10)*10 AS decade, COUNT(*) AS n FROM books WHERE published_year IS NOT NULL GROUP BY decade) WHERE decade=2010;')"
check "grouping by two keys gives one row per combination that occurs" "15" \
  "$(q 'SELECT COUNT(*) FROM (SELECT author, genre FROM books GROUP BY author, genre);')"
check "the busiest borrower took 6 loans" "6" \
  "$(q 'SELECT COUNT(*) AS n FROM loans GROUP BY member_id ORDER BY n DESC LIMIT 1;')"
check "SUM over a CASE counts a subset inside each bucket" "15" \
  "$(q 'SELECT SUM(still) FROM (SELECT SUM(CASE WHEN returned_on IS NULL THEN 1 ELSE 0 END) AS still FROM loans GROUP BY member_id);')"
check "WHERE runs before GROUP BY: Q1 loans for member 1" "5" \
  "$(q "SELECT COUNT(*) FROM loans WHERE member_id=1 AND borrowed_on BETWEEN '2026-01-01' AND '2026-03-31';")"
echo

echo "9. HAVING — the filter WHERE cannot express"
check "authors with more than three titles" "3" \
  "$(q 'SELECT COUNT(*) FROM (SELECT author FROM books GROUP BY author HAVING COUNT(*) > 3);')"
check "the most prolific of them" "Ada Fenwick" \
  "$(q 'SELECT author FROM books GROUP BY author ORDER BY COUNT(*) DESC, author ASC LIMIT 1;')"
# The proof that WHERE cannot do this: the engine refuses the query outright.
where_err="$(sqlite3 "${db}" 'SELECT author FROM books WHERE COUNT(*) > 3 GROUP BY author;' 2>&1)"
if printf '%s' "${where_err}" | grep -qi 'misuse of aggregate'; then
  pass "WHERE COUNT(*) > 3 is rejected: ${where_err}"
else
  fail "WHERE COUNT(*) > 3 should be rejected as a misuse of an aggregate, but SQLite said: ${where_err}"
fi
check "WHERE and HAVING together: post-2000 genres with 2+ books" "6" \
  "$(q 'SELECT COUNT(*) FROM (SELECT genre FROM books WHERE published_year>=2000 GROUP BY genre HAVING COUNT(*)>=2);')"
check "HAVING on an aggregate absent from the SELECT list" "2" \
  "$(q 'SELECT COUNT(*) FROM (SELECT genre FROM books GROUP BY genre HAVING AVG(pages) > 350);')"
check "books borrowed more than twice" "7" \
  "$(q 'SELECT COUNT(*) FROM (SELECT book_id FROM loans GROUP BY book_id HAVING COUNT(*) > 2);')"
check "the most borrowed book was taken out 7 times" "7" \
  "$(q 'SELECT COUNT(*) AS n FROM loans GROUP BY book_id ORDER BY n DESC LIMIT 1;')"
check "HAVING over two aggregates: members with 2+ books still out" "5" \
  "$(q 'SELECT COUNT(*) FROM (SELECT member_id FROM loans GROUP BY member_id HAVING COUNT(*)-COUNT(returned_on) >= 2);')"
echo

echo "10. Scalar functions and CASE"
check "|| concatenates" "The Silent Archive (Priya Raman)" \
  "$(q "SELECT title || ' (' || author || ')' FROM books WHERE book_id=1;")"
check "a scalar function on NULL returns NULL" "" "$(q 'SELECT LOWER(genre) FROM books WHERE book_id=15;')"
check "TYPEOF names the storage class of the value in the row" "null" \
  "$(q 'SELECT TYPEOF(rating) FROM books WHERE book_id=4;')"
check "JULIANDAY subtraction gives real elapsed days" "28.0" \
  "$(q 'SELECT JULIANDAY(returned_on)-JULIANDAY(borrowed_on) FROM loans WHERE loan_id=2;')"
check "subtracting the raw TEXT dates confidently returns nonsense" "0" \
  "$(q 'SELECT returned_on - borrowed_on FROM loans WHERE loan_id=2;')"
check "STRFTIME buckets a stored date by month" "2026-01" \
  "$(q "SELECT STRFTIME('%Y-%m', borrowed_on) FROM loans WHERE loan_id=1;")"
check "GROUP BY over a CASE: the 'good' band" "8" \
  "$(q "SELECT n FROM (SELECT CASE WHEN rating IS NULL THEN 'unrated' WHEN rating>=4.5 THEN 'excellent' WHEN rating>=4.0 THEN 'good' WHEN rating>=3.5 THEN 'fair' ELSE 'poor' END AS band, COUNT(*) AS n FROM books GROUP BY band) WHERE band='good';")"
check "with the NULL branch first, 4 books are 'unrated'" "4" \
  "$(q "SELECT COUNT(*) FROM (SELECT CASE WHEN rating IS NULL THEN 'unrated' WHEN rating>=4.0 THEN 'good' ELSE 'poor' END AS band FROM books) WHERE band='unrated';")"
check "with the NULL branch, 'poor' holds the 6 genuinely low-rated books" "6" \
  "$(q "SELECT COUNT(*) FROM (SELECT CASE WHEN rating IS NULL THEN 'unrated' WHEN rating>=4.0 THEN 'good' ELSE 'poor' END AS band FROM books) WHERE band='poor';")"
# Drop the NULL branch and 'poor' swells from 6 to 10: the 4 unrated books fall
# through the ELSE and are quietly reported as the worst books in the library.
check "without it, 'poor' swells to 10 as the 4 unrated books fall through" "10" \
  "$(q "SELECT COUNT(*) FROM (SELECT CASE WHEN rating>=4.0 THEN 'good' ELSE 'poor' END AS band FROM books) WHERE band='poor';")"
echo

echo "11. Every example query file runs against a fresh database"
for f in "${lab_dir}"/examples/queries/*.sql; do
  name="$(basename "${f}")"
  if err="$(sqlite3 "${db}" < "${f}" 2>&1 >/dev/null)" && [ -z "${err}" ]; then
    pass "examples/queries/${name} runs clean"
  else
    fail "examples/queries/${name} produced errors: ${err}"
  fi
done
echo

echo "12. The from-scratch GROUP BY agrees with the one-line SQL"
gb_out="$("${python_bin}" "${lab_dir}/examples/groupby_from_scratch.py" "${db}" 2>&1)"
gb_status=$?
if [ "${gb_status}" -eq 0 ]; then pass "groupby_from_scratch.py exits 0"
else fail "groupby_from_scratch.py exited ${gb_status}"; fi
if printf '%s' "${gb_out}" | grep -q 'IDENTICAL: 3 rows match exactly.'; then
  pass "the Python accumulators and the SQL agree on all 3 rows"
else
  fail "the Python and SQL results did not match: $(printf '%s' "${gb_out}" | tail -3)"
fi
check "the pipeline it prints: FROM 24 rows" "  FROM      -> 24 rows" \
  "$(printf '%s\n' "${gb_out}" | grep 'FROM ')"
check "WHERE keeps 20" "  WHERE     -> 20 rows survive" \
  "$(printf '%s\n' "${gb_out}" | grep 'WHERE ')"
check "GROUP BY makes 6 buckets" "  GROUP BY  -> 6 buckets" \
  "$(printf '%s\n' "${gb_out}" | grep 'GROUP BY ')"
check "HAVING leaves 3" "  HAVING    -> 3 buckets survive" \
  "$(printf '%s\n' "${gb_out}" | grep 'HAVING ')"
echo

echo "13. The exercises: the answer key is right and the starter is wrong"
answers="$(sqlite3 "${db}" < "${lab_dir}/examples/exercise-answers.sql" 2>&1)"
starter="$(sqlite3 "${db}" < "${lab_dir}/starter/exercises.sql" 2>&1)"
check "the answer key emits 12 labelled lines" "12" "$(printf '%s\n' "${answers}" | grep -c '^ex[0-9][0-9]|')"
check "the starter emits the same 12 labels"   "12" "$(printf '%s\n' "${starter}" | grep -c '^ex[0-9][0-9]|')"

expect_answer() {
  check "answer $1" "$2" "$(printf '%s\n' "${answers}" | grep "^$1|" | cut -d'|' -f2-)"
}
expect_answer ex01 "15"
expect_answer ex02 "10"
expect_answer ex03 "2"
expect_answer ex04 "4"
expect_answer ex05 "4.16"
expect_answer ex06 "4"
expect_answer ex07 "Ledger of Tides"
expect_answer ex08 "6"
expect_answer ex09 "Ada Fenwick"
expect_answer ex10 "3"
expect_answer ex11 "28.0"
expect_answer ex12 "4"

# Every starter answer must DIFFER from the model answer. If a starter query
# ever accidentally became right, the exercise would teach nothing.
disagreements=0
for label in ex01 ex02 ex03 ex04 ex05 ex06 ex07 ex08 ex09 ex10 ex11 ex12; do
  a="$(printf '%s\n' "${answers}" | grep "^${label}|" | cut -d'|' -f2-)"
  s="$(printf '%s\n' "${starter}" | grep "^${label}|" | cut -d'|' -f2-)"
  [ "${a}" != "${s}" ] && disagreements=$((disagreements + 1))
done
check "all 12 starter queries return a WRONG answer before you fix them" "12" "${disagreements}"
echo

echo "14. The lab stays offline, stays out of your way, and cleans up"
if grep -rInE 'https?://[a-zA-Z0-9]' "${lab_dir}/examples" "${lab_dir}/starter" "${lab_dir}/tests" >/dev/null 2>&1; then
  fail "something under examples/, starter/ or tests/ names a URL"
else
  pass "no URL anywhere in examples/, starter/ or tests/"
fi
if grep -rIn 'sudo' "${lab_dir}/examples" "${lab_dir}/starter" >/dev/null 2>&1; then
  fail "an example or starter file calls sudo"
else
  pass "nothing under examples/ or starter/ calls sudo"
fi
if [ -e "${lab_dir}/library.db" ]; then
  fail "a database was left in the lab root; it belongs under examples/"
else
  pass "no stray database in the lab root"
fi
if grep -q 'library.db' "${lab_dir}/.gitignore" 2>/dev/null; then
  pass "the built database is git-ignored, so it is never committed"
else
  fail ".gitignore does not exclude the built database"
fi
echo

echo "${checks} checks, ${failures} failure(s)."
[ "${failures}" -eq 0 ] || exit 1
exit 0