# /// script
# requires-python = ">=3.12,<3.15"
# dependencies = [
#     "marimo[sql]",
#     "duckdb",
# ]
# ///
"""SQL Handbook, Chapter D: Building and Loading.
"""

import marimo

__generated_with = "0.24.0"
app = marimo.App(width="medium", sql_output="polars")


@app.cell
def _():
    import tempfile
    from pathlib import Path

    import duckdb
    import marimo as mo

    return Path, duckdb, mo, tempfile


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    # Building and Loading

    Chapter D of the SQL Handbook. Five sections, each one concept.

    Parts B and C ask a database questions. This chapter covers where the tables came
    from, how rows get into them, and how you run any of it from a notebook.

    Section 27 is the mechanics of running a query at all. If you have not run one yet,
    read it first and come back here.

    Two databases are open below. The course warehouse, Northwind, is read and never
    written to. Everything this chapter builds goes into a second, empty database that
    lives in memory and is gone when the notebook closes. Nothing on disk changes.

    To run the code yourself, download the `.py` into your project and open it with
    `uv run marimo edit`.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Opening the databases

    The first cell loads three Northwind tables from parquet files. The second opens an
    empty database with nothing in it. How both of those lines work is §26 and §27.
    """)
    return


@app.cell
def _(duckdb, mo):
    # Look for a data/northwind folder here and in every folder above, so the
    # chapter works wherever you save it. If there is none, read the copy on the
    # course site instead.
    _here = mo.notebook_dir()
    _source = next(
        (p / "data" / "northwind" for p in [_here, *_here.parents]
         if (p / "data" / "northwind").is_dir()),
        "https://oim7510.github.io/data/northwind",
    )

    con = duckdb.connect()
    for _table in ("customers", "orders", "products"):
        con.execute(
            f"""CREATE TABLE {_table} AS SELECT * FROM '{_source}/{_table}.parquet'"""
        )
    return (con,)


@app.cell
def _(duckdb):
    db = duckdb.connect()
    return (db,)


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 24. DDL: `CREATE TABLE`, types, constraints

    A table does not appear on its own. Somebody writes down its columns, gives each
    column a type, and adds the rules a row has to satisfy. From then on the database
    refuses every row that breaks one of them, which is what makes a column of prices
    something you can add up without looking at it first. `CREATE TABLE` belongs to SQL's
    **data definition language**, or DDL.

    📖 **Used in:** Building and querying your database
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### The shape of a `CREATE TABLE`

    A small consultancy bills clients for projects. Here is its `project` table. One line
    per column, with the type after the name and the rules after the type.
    """)
    return


@app.cell
def _(db):
    db.execute("""
        CREATE OR REPLACE TABLE project (
            code    VARCHAR       PRIMARY KEY,
            name    VARCHAR       NOT NULL,
            budget  DECIMAL(10,2) NOT NULL CHECK (budget > 0),
            started DATE          NOT NULL,
            status  VARCHAR       NOT NULL DEFAULT 'active'
                                  CHECK (status IN ('active', 'closed'))
        )
    """)
    project_table = db.sql("DESCRIBE project")
    project_table
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    `DESCRIBE` reads the statement back. `null` says `NO` on every column that was
    declared `NOT NULL`, `key` says `PRI` on the primary key, and `default` carries the
    value the database supplies when a row leaves `status` out.

    **The trap.** Running a plain `CREATE TABLE` twice is an error:

    ```
    Catalog Error: Table with name "project" already exists!
    ```

    `CREATE OR REPLACE TABLE` re-runs cleanly, which is why every statement in this
    chapter uses it. There is one case it refuses, and that case is two knowledge points
    below.

    👉 **Try it:** add a `client VARCHAR NOT NULL` column to the statement, run the cell
    again, and read `DESCRIBE project` to confirm the column arrived and is required.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Choosing a type

    A lot of the SQL you will meet outside this course was written for SQLite, which
    offers four types and leans on `TEXT` for everything awkward. DuckDB has real types, and the
    translation is worth having in one place.

    | what you will see | what to write here | for |
    |---|---|---|
    | `INTEGER` | `INTEGER` | counts, quantities, ids |
    | `TEXT` | `VARCHAR` | names, codes, anything typed |
    | `REAL` | `DECIMAL(10,2)` | money |
    | `REAL` | `DOUBLE` | measurements, ratios, anything continuous |
    | `TEXT` holding `'2026-01-12'` | `DATE` | a day |
    | `TEXT` holding a timestamp | `TIMESTAMP` | a day and a time |
    | `BLOB` | leave it out | files belong beside the database |

    `DECIMAL(10,2)` means 10 digits with two of them after the point, so it holds up to
    99,999,999.99 and stores cents exactly.
    """)
    return


@app.cell
def _(db):
    project_types = db.sql("""
        SELECT SUM(CAST(0.10 AS REAL))          AS ten_dimes_as_real,
               SUM(CAST(0.10 AS DECIMAL(10,2))) AS ten_dimes_as_decimal
        FROM range(10)
    """)
    project_types
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    **The trap.** `budget` is a `DECIMAL` because of this. `REAL` is binary floating
    point, and one tenth has no exact binary form, so 10 dimes come to a dollar and a
    remainder. Plenty of SQLite material says to use `REAL` for money. A
    course whose every deliverable carries a section called *How I know these numbers are
    right* stores dollars in a type that can hold them.

    👉 **Try it:** change `range(10)` to `range(1000)` and read both numbers again. Then
    run `SELECT CAST(0.1 AS REAL) + CAST(0.2 AS REAL)` on its own.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `NOT NULL`, `DEFAULT` and `CHECK`

    Three rules can sit on a column, and all three are enforced at the moment a row
    arrives.

    - **`NOT NULL`** refuses a row that leaves the column empty.
    - **`DEFAULT`** supplies a value when the row leaves the column out. It makes the
      column optional to write and still never empty.
    - **`CHECK`** refuses a row whose value fails a condition you write yourself. It takes
      any expression that comes out true or false, so `budget > 0` and
      `status IN ('active', 'closed')` are both fine.

    Here is what each refusal looks like against the `project` table above.

    ```sql
    INSERT INTO project (code, name, budget, started)
    VALUES ('AURORA', 'Aurora rollout', -1, DATE '2026-01-12');
    ```

    ```
    Constraint Error: CHECK constraint failed on table project with expression CHECK((budget > 0))
    ```

    ```sql
    INSERT INTO project (code, budget, started)
    VALUES ('AURORA', 48000.00, DATE '2026-01-12');
    ```

    ```
    Constraint Error: NOT NULL constraint failed: project.name
    ```

    ```sql
    INSERT INTO project (code, name, budget, started, status)
    VALUES ('AURORA', 'Aurora rollout', 48000.00, DATE '2026-01-12', 'paused');
    ```

    ```
    Constraint Error: CHECK constraint failed on table project with expression CHECK((status IN ('active', 'closed')))
    ```

    A `CHECK` is the cheapest place to put a rule you would otherwise write into every
    query that reads the table, and the message names the expression that failed.

    👉 **Try it:** reproduce all three. Then add
    `CHECK (started >= DATE '2020-01-01')` to the statement and find out what happens to a
    row whose `started` is in 2019.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### A compound key

    Chapter A §2 called this a compound key and Chapter A §3 said an intersection table
    needs one. This is the syntax. A key spanning two columns cannot go on either column,
    so it goes on its own line at the end of the statement.
    """)
    return


@app.cell
def _(db):
    db.execute("""
        CREATE OR REPLACE TABLE ord_line (
            order_id     INTEGER,
            product_code VARCHAR,
            quantity     INTEGER NOT NULL CHECK (quantity > 0),
            PRIMARY KEY (order_id, product_code)
        )
    """)
    db.execute("INSERT INTO ord_line VALUES (1, 'CHAI', 12), (1, 'KONBU', 4)")
    keys_table = db.sql("DESCRIBE ord_line")
    keys_table
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    `key` says `PRI` on both columns. One order line is one product on one order, so the
    pair has to be unique and neither half is unique on its own. A second line for `CHAI`
    on order 1 is refused:

    ```sql
    INSERT INTO ord_line VALUES (1, 'CHAI', 5);
    ```

    ```
    Constraint Error: Duplicate key "order_id: 1, product_code: CHAI" violates primary key constraint.
    ```

    In a full design both columns would also be foreign keys, one into the orders table
    and one into products. The next knowledge point is that syntax.

    👉 **Try it:** insert `(2, 'CHAI', 3)` and confirm it is accepted. Then state in one
    sentence what changes if the primary key is `PRIMARY KEY (order_id)` alone.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Declaring a foreign key

    Chapter A §4 showed two `Constraint Error` messages from a `customer` and `ord` pair
    and said the syntax was here. It is `REFERENCES`, and it goes on the column that holds
    the other table's key.

    The two tables are created in one cell and dropped child first, for a reason the
    output below explains.
    """)
    return


@app.cell
def _(db):
    db.execute("DROP TABLE IF EXISTS ord")
    db.execute("DROP TABLE IF EXISTS customer")
    db.execute("""
        CREATE TABLE customer (
            id      VARCHAR PRIMARY KEY,
            company VARCHAR NOT NULL
        )
    """)
    db.execute("""
        CREATE TABLE ord (
            id          INTEGER PRIMARY KEY,
            customer_id VARCHAR NOT NULL REFERENCES customer(id),
            ordered_on  DATE    NOT NULL
        )
    """)
    db.execute("INSERT INTO customer VALUES ('VINET', 'Vins et alcools Chevalier')")
    db.execute("INSERT INTO ord VALUES (10248, 'VINET', DATE '2016-07-04')")
    fk_table = db.sql("""
        SELECT table_name, constraint_type, constraint_text
        FROM duckdb_constraints()
        WHERE table_name IN ('customer', 'ord')
        ORDER BY table_name, constraint_type
    """)
    fk_table
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    `duckdb_constraints()` lists what the database is now enforcing. Both messages in
    Chapter A §4 come from these two rows, and `ON DELETE CASCADE` is the parser error
    quoted there.

    **The trap.** `CREATE OR REPLACE TABLE customer` is refused while `ord` points at it,
    which is why those two `DROP` lines are at the top of the cell:

    ```
    Dependency Error: Cannot drop entry "customer" because there are entries that depend on it.
    table "ord" depends on table "customer".
    Use DROP...CASCADE to drop all dependents.
    ```

    The suggestion in the last line does not help. `DROP ... CASCADE` in DuckDB reaches
    views, and a foreign-key child stays where it is. A parent goes away after its children, and
    §26 shows the same order running forwards for a load.

    👉 **Try it:** add `CREATE OR REPLACE TABLE customer (id VARCHAR PRIMARY KEY)` as its
    own cell and read the error. Then delete the `DROP TABLE IF EXISTS ord` line from the
    cell above and run it again.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### There is no `AUTOINCREMENT`

    Most tables want an id nobody has to think about. SQLite spells that
    `INTEGER PRIMARY KEY AUTOINCREMENT`, which is the line an agent will usually reach
    for and which DuckDB rejects:

    ```sql
    CREATE OR REPLACE TABLE expense (id INTEGER PRIMARY KEY AUTOINCREMENT);
    ```

    ```
    Parser Error: syntax error at or near "AUTOINCREMENT"
    ```

    DuckDB splits the idea in two. A **sequence** is an object that hands out the next
    number each time it is asked, and a column gets one by naming it in its `DEFAULT`.
    """)
    return


@app.cell
def _(db):
    db.execute("DROP TABLE IF EXISTS expense")
    db.execute("CREATE OR REPLACE SEQUENCE seq_expense_id START 1")
    db.execute("""
        CREATE OR REPLACE TABLE expense (
            id       INTEGER       PRIMARY KEY DEFAULT nextval('seq_expense_id'),
            code     VARCHAR       NOT NULL,
            spent_on DATE          NOT NULL,
            amount   DECIMAL(10,2) NOT NULL CHECK (amount > 0),
            category VARCHAR
        )
    """)
    id_table = db.sql("""
        SELECT column_name, column_type, "default" FROM (DESCRIBE expense)
    """)
    id_table
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The `default` column holds `nextval('seq_expense_id')`. Any row that leaves `id` out
    gets the next number, which §25 uses.

    The other option is to supply the ids yourself, and it is the right one more often
    than it sounds. Northwind's `CustomerID` is `VINET`, a code a person chose, and
    Chapter A §2 is about when that beats a number.

    👉 **Try it:** run `SELECT nextval('seq_expense_id')` three times and watch the value
    move. Then run `CREATE OR REPLACE SEQUENCE seq_expense_id START 1` and read the error.
    Which table is keeping the sequence in place? Rerun the cell above to start the
    sequence from 1 again.

    ### ✔ Check your understanding

    1. `budget` is `DECIMAL(10,2)` and `NOT NULL` and carries a `CHECK`. Which of those
       three would stop a row that says the budget is zero, and which would stop a row
       that says nothing about the budget at all?
    2. A colleague's schema stores every date as `TEXT`. Name one query that gives a wrong
       answer because of it.
    3. `status` has both a `DEFAULT` and a `CHECK`. What does each one do that the other
       does not?
    4. `ord_line` has a primary key over two columns. What would go wrong if it were
       declared over `order_id` alone, and what would go wrong over `product_code` alone?
    5. An agent hands you a `CREATE TABLE` with `AUTOINCREMENT` and `budget REAL`. State
       what happens to each when you run it here.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 25. DML: `INSERT`, `UPDATE`, `DELETE`

    Three statements change rows. `INSERT` adds them, `UPDATE` changes ones that are
    already there, and `DELETE` removes them. All three are plain ANSI SQL and take a
    table name, so they move between engines with no edits. Together they are SQL's
    **data manipulation language**, or DML.

    Everything below runs against the `project` and `expense` tables from §24. The
    warehouse is read-only in this book, and it stays that way here.

    📖 **Used in:** Building and querying your database
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `INSERT` names its columns

    An `INSERT` gives a table name, a list of columns, and one `VALUES` group per row. The
    first row below leaves `status` out and takes the `DEFAULT`.
    """)
    return


@app.cell
def _(db):
    db.execute("""
        INSERT INTO project (code, name, budget, started) VALUES
            ('AURORA', 'Aurora rollout', 48000.00, DATE '2026-01-12')
    """)
    db.execute("""
        INSERT INTO project (code, name, budget, started, status) VALUES
            ('BEACON', 'Beacon audit',    15250.50, DATE '2026-02-03', 'active'),
            ('CEDAR',  'Cedar migration',  7500.00, DATE '2025-11-30', 'active')
    """)
    project_rows = db.sql("SELECT * FROM project ORDER BY code")
    project_rows
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Three rows went in, and `AURORA` came out `active` without anybody saying so.

    **The trap.** The column list is optional. `INSERT INTO project VALUES (...)` matches
    values to columns by position, so it works until somebody adds a column to the table,
    at which point every one of those statements is loading values into the wrong columns
    or failing. Name the columns.

    👉 **Try it:** insert a fourth project with a `code` that already exists and read the
    message. Then insert one whose `started` is a string, `'2026-04-01'`, and see whether
    DuckDB accepts it.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### The columns you leave out

    `expense` has a sequence on its `id`, so the `INSERT` omits it and the database fills
    it in. `category` is the one column with no `NOT NULL`, so leaving it out gives a
    `NULL`, which Part B §9 is about.
    """)
    return


@app.cell
def _(db):
    db.execute("""
        INSERT INTO expense (code, spent_on, amount, category) VALUES
            ('AURORA', DATE '2026-01-20', 1250.00, 'travel'),
            ('AURORA', DATE '2026-02-11',  340.75, 'software'),
            ('BEACON', DATE '2026-02-14',   89.99, 'software')
    """)
    db.execute("""
        INSERT INTO expense (code, spent_on, amount) VALUES
            ('CEDAR', DATE '2025-12-02', 2100.00)
    """)
    expense_rows = db.sql("SELECT * FROM expense ORDER BY id")
    expense_rows
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    There are four rows with ids 1 to 4, none of which was typed, and one `NULL`
    category.

    👉 **Try it:** insert a fifth expense and predict its id before you run it. Then
    insert one with `amount` set to `0` and read which constraint stops it.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `UPDATE` changes the rows its `WHERE` names

    `UPDATE` takes a table, a `SET` clause naming the columns to change, and a `WHERE`
    naming the rows. The `WHERE` is optional to write and never optional to think about,
    because leaving it off changes every row in the table and reports success.
    """)
    return


@app.cell
def _(db):
    db.execute("UPDATE project SET status = 'closed' WHERE code = 'CEDAR'")
    update_rows = db.sql("SELECT code, name, status FROM project ORDER BY code")
    update_rows
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    One row moved. DuckDB reports how many in the editor's output area, and the way to
    know for certain is to read the table back, which is what the cell above does.

    👉 **Try it:** run `UPDATE project SET budget = budget * 1.1 WHERE status = 'active'`
    and read the two budgets before and after. Write down which rows you expected to move
    before you run it.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `DELETE` removes whole rows

    `DELETE FROM` takes a table and a `WHERE`. There is no column list, because a delete
    removes the entire row. `DELETE FROM project` with no `WHERE` empties the table.
    """)
    return


@app.cell
def _(db):
    db.execute("DELETE FROM project WHERE code = 'BEACON'")
    delete_rows = db.sql("SELECT code, name, status FROM project ORDER BY code")
    delete_rows
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Two rows are left. Nothing stopped that delete, because no other table declares a foreign
    key into `project`. Where one does, the delete is refused, and Chapter A §4 has the
    message and the three designs that answer for it.

    👉 **Try it:** delete `VINET` from the `customer` table built in §24, which does have a
    child row pointing at it, and read the message. Then delete the `ord` row first and
    try again.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Transactions, `COMMIT` and `ROLLBACK`

    Sooner or later somebody runs an `UPDATE` without its `WHERE`. Wrap a destructive
    statement in a transaction and there is a way back: `BEGIN TRANSACTION`, run it, look
    at the result, then `COMMIT` to keep it or `ROLLBACK` to put everything back the way
    it was.

    The cell below does exactly that, and prints the count from inside the transaction
    beside the count after the rollback.
    """)
    return


@app.cell
def _(db):
    db.execute("BEGIN TRANSACTION")
    db.execute("UPDATE project SET status = 'closed'")
    _inside = db.sql("SELECT count(*) FROM project WHERE status = 'closed'").fetchall()[0][0]
    db.execute("ROLLBACK")
    _after = db.sql("SELECT count(*) FROM project WHERE status = 'closed'").fetchall()[0][0]
    rollback_counts = db.sql(f"""
        SELECT {_inside} AS closed_inside_the_transaction,
               {_after}  AS closed_after_the_rollback
    """)
    rollback_counts
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Inside the transaction both projects were closed. After the `ROLLBACK` one is, which
    is where §25 left the table.

    Two things make this usable. The statement runs for real
    inside the transaction, so the count you read is the count you would be committing.
    And nothing outside the transaction sees any of it until the `COMMIT`.

    👉 **Try it:** run the same three statements with `COMMIT` in place of `ROLLBACK`,
    then put the table back with an `UPDATE` of your own.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    > **Advanced**

    ### Statements that belong together

    A transaction is also how you tell the database that several statements are one
    change. Finance moves 10,000 of budget from Training to Sales. An `UPDATE` adds it to
    Sales, and a second one takes it from Training. Training holds 7,500, so the second one
    breaks a `CHECK`.

    Without a `BEGIN TRANSACTION`, DuckDB keeps each statement the moment it succeeds.
    This is called **autocommit**, and most databases do the same by default. The cell
    below runs the move both ways, each time on a fresh copy of the two departments, and
    reads the total afterwards. The departments start with 55,500 between them.
    """)
    return


@app.cell
def _(db, duckdb):
    def transfer_funds(amount, in_one_transaction):
        # a fresh copy of the two departments on every call
        db.execute("""
            CREATE OR REPLACE TABLE department (
                name    VARCHAR       PRIMARY KEY,
                balance DECIMAL(10,2) NOT NULL CHECK (balance >= 0)
            )
        """)
        db.execute("INSERT INTO department VALUES ('Sales', 48000.00), ('Training', 7500.00)")
        if in_one_transaction:
            db.execute("BEGIN TRANSACTION")
        try:
            db.execute("UPDATE department SET balance = balance + ? WHERE name = 'Sales'", [amount])
            db.execute("UPDATE department SET balance = balance - ? WHERE name = 'Training'", [amount])
            if in_one_transaction:
                db.execute("COMMIT")
        except duckdb.ConstraintException:
            if in_one_transaction:
                db.execute("ROLLBACK")
        return db.sql("SELECT sum(balance) FROM department").fetchall()[0][0]

    _alone = transfer_funds(10000, in_one_transaction=False)
    _together = transfer_funds(10000, in_one_transaction=True)
    transfer_totals = db.sql(f"""
        SELECT {_alone}    AS total_without_a_transaction,
               {_together} AS total_inside_one_transaction
    """)
    transfer_totals
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Without a transaction the total came out at 65,500, which is 10,000 more than the
    departments had. Sales received the money, the `CHECK` refused the second `UPDATE`,
    and the first had already been kept. Inside one transaction the `ROLLBACK` undid both
    statements, and the total stayed at 55,500.

    The database has no way to know that two `UPDATE`s are one move until you put them in
    one transaction.

    👉 **Try it:** change both `10000`s to `5000` and predict both totals before you run
    it. Then state whether the transaction made any difference that time.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Databases name what a transaction promises with the letters **ACID**:

    - **Atomicity.** The statements in a transaction are kept together or not at all.
      The transfer above shows it.
    - **Consistency.** Every rule the database was told about, such as a `CHECK` or a
      foreign key, still holds when the transaction ends. A business rule that lives
      only in your code is yours to check.
    - **Isolation.** Transactions running at the same time do not see each other's
      uncommitted changes. How strict that separation is depends on the database and on
      a setting called the isolation level.
    - **Durability.** Once a `COMMIT` completes, the change survives a crash. That needs
      a database kept in a file, and the `db` in this chapter lives in memory, so it is
      gone when the notebook closes.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### ✔ Check your understanding

    1. `INSERT INTO project VALUES ('DELTA', 'Delta review', 900.00, DATE '2026-04-01', 'active')`
       runs today. Somebody adds a `client` column to `project` next week. What happens to
       that statement, and when do you find out?
    2. An `UPDATE` reports that it changed 47 rows and you expected 3. What do you do
       before running anything else?
    3. You want to remove one column's values from every row without removing the rows.
       Which of the three statements is that, and how would you write it?
    4. A colleague says a transaction is an undo button. Name one thing it undoes and one
       thing it does not.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 26. Loading a CSV into DuckDB

    A CSV is the file data arrives in. Getting the rows into a table takes one line. Two
    things decide whether the numbers are right afterwards: the type each column ended up
    with, and whether the count matches the file.

    Two CSV files are written to a temporary folder below and read from there. Nothing
    in the `data` folder is touched.

    📖 **Used in:** Building and querying your database
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Reading a CSV without loading it

    `read_csv_auto` reads a file and hands back rows. No table is created and nothing is
    stored, so this is the way to look at a file before deciding what to do with it.
    """)
    return


@app.cell
def _(Path, tempfile):
    csv_dir = Path(tempfile.mkdtemp())
    (csv_dir / "project.csv").write_text(
        "code,name,budget,started,status\n"
        "AURORA,Aurora rollout,48000.00,2026-01-12,active\n"
        "BEACON,Beacon audit,15250.50,2026-02-03,active\n"
        "CEDAR,Cedar migration,7500.00,2025-11-30,closed\n"
    )
    (csv_dir / "expense.csv").write_text(
        "project_code,spent_on,amount,category\n"
        "AURORA,2026-01-20,1250.00,travel\n"
        "AURORA,2026-02-11,340.75,software\n"
        "BEACON,2026-02-14,89.99,software\n"
        "CEDAR,2025-12-02,2100.00,contractor\n"
    )
    csv_dir
    return (csv_dir,)


@app.cell
def _(csv_dir, db):
    db.sql(f"SELECT * FROM read_csv_auto('{(csv_dir / 'project.csv').as_posix()}')")
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The header row became column names and every row below it became a row. `FROM
    'project.csv'` on its own does the same thing, because DuckDB recognizes the extension
    and calls the reader for you. That is also how the warehouse is opened at the top of
    this notebook, with `.parquet` in place of `.csv`.

    👉 **Try it:** run `DESCRIBE SELECT * FROM read_csv_auto('project.csv')` against the
    path above and write down the type it chose for each of the five columns. Keep the
    list; the next knowledge point is about one of them.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Making it a table

    The difference between the two is who decides the column types.

    `CREATE TABLE ... AS SELECT` takes whatever the reader guessed. It is one line, and
    the guess is often right.
    """)
    return


@app.cell
def _(csv_dir, db):
    db.execute(f"""
        CREATE OR REPLACE TABLE project_guessed AS
        SELECT * FROM read_csv_auto('{(csv_dir / 'project.csv').as_posix()}')
    """)
    guessed_types = db.sql("DESCRIBE project_guessed")
    guessed_types
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    `budget` came out `DOUBLE`, which is §24's `REAL` problem under another name. The
    reader saw digits and a decimal point and had no way to know they were dollars.

    `COPY ... FROM` is the other way. You write the `CREATE TABLE` first, with the types
    and the constraints you want, and `COPY` fills it. The file has to fit the table you
    declared.
    """)
    return


@app.cell
def _(csv_dir, db):
    db.execute("DROP TABLE IF EXISTS expense_loaded")
    db.execute("DROP TABLE IF EXISTS project_loaded")
    db.execute("CREATE OR REPLACE SEQUENCE seq_loaded_id START 1")
    db.execute("""
        CREATE TABLE project_loaded (
            code    VARCHAR       PRIMARY KEY,
            name    VARCHAR       NOT NULL,
            budget  DECIMAL(10,2) NOT NULL CHECK (budget > 0),
            started DATE          NOT NULL,
            status  VARCHAR       NOT NULL DEFAULT 'active'
        )
    """)
    db.execute("""
        CREATE TABLE expense_loaded (
            id           INTEGER       PRIMARY KEY DEFAULT nextval('seq_loaded_id'),
            project_code VARCHAR       NOT NULL REFERENCES project_loaded(code),
            spent_on     DATE          NOT NULL,
            amount       DECIMAL(10,2) NOT NULL CHECK (amount > 0),
            category     VARCHAR
        )
    """)
    db.execute(
        f"COPY project_loaded FROM '{(csv_dir / 'project.csv').as_posix()}' (HEADER)"
    )
    db.execute(
        "COPY expense_loaded (project_code, spent_on, amount, category) "
        f"FROM '{(csv_dir / 'expense.csv').as_posix()}' (HEADER)"
    )
    loaded_tables = db.sql("""
        SELECT (SELECT SUM(budget) FROM project_guessed) AS sum_from_the_guess,
               (SELECT SUM(budget) FROM project_loaded)  AS sum_from_the_declaration
    """)
    loaded_tables
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The same three budgets add to `70750.5` from the guessed table and `70750.50` from the
    declared one. On three rows the gap is cosmetic. On a quarter of invoices it is the
    difference between a total that reconciles and one that is off by a few cents with no
    explanation.

    Two details in that cell are worth naming. `expense.csv` has no `id` column, so the
    `COPY` lists the four columns it does supply and the sequence fills in the fifth.
    And `project_loaded` is filled before `expense_loaded`, which the next knowledge point
    is about.

    ### In Excel terms

    Opening a CSV in Excel is `CREATE TABLE ... AS SELECT`. Excel guesses the type of
    every column, tells you nothing, and is why a column of product codes sometimes turns
    into dates. `COPY ... FROM` is the version where you specify what each column is first and
    the file has to agree.

    👉 **Try it:** add a row to `project.csv` whose budget is `-5` and run the `COPY`
    again. Then run the `CREATE TABLE ... AS SELECT` on the same file and compare what
    each one did with that row.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Parents before children

    `expense_loaded.project_code` is a foreign key into `project_loaded.code`. Load the
    expenses first and every row is an orphan, so the database refuses the whole file:

    ```sql
    COPY expense_loaded (project_code, spent_on, amount, category)
    FROM 'expense.csv' (HEADER);
    ```

    ```
    Constraint Error: Violates foreign key constraint because key "code: AURORA" does not exist in the referenced table
    ```

    So a load runs down the diagram: every table with no foreign keys first, then the
    tables that point at those, and the intersection tables last. A drop runs the other
    way, which is why §24's foreign-key cell drops `ord` before `customer`.

    An ERD read top to bottom is the loading order.

    👉 **Try it:** take the ERD you drew for Chapter A's chapter challenge and write out
    the order its tables have to be loaded in. Name any table whose position you cannot
    decide from the diagram alone.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Count the rows and compare

    A load that raises no error is not a load that worked. The check is one query that
    counts the rows in the file and the rows in the table.
    """)
    return


@app.cell
def _(csv_dir, db):
    load_counts = db.sql(f"""
        SELECT (SELECT count(*)
                  FROM read_csv_auto('{(csv_dir / 'project.csv').as_posix()}'))
                 AS project_rows_in_the_file,
               (SELECT count(*) FROM project_loaded)  AS project_rows_in_the_table,
               (SELECT count(*)
                  FROM read_csv_auto('{(csv_dir / 'expense.csv').as_posix()}'))
                 AS expense_rows_in_the_file,
               (SELECT count(*) FROM expense_loaded)  AS expense_rows_in_the_table
    """)
    load_counts
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The counts match, three and three for projects and four and four for expenses. Run
    this after every load, on every table, and write the numbers down.

    The same move catches a join that duplicated rows, which is Part C §19, and it is what
    goes in the *How I know these numbers are right* section every deliverable in this
    course carries.

    👉 **Try it:** delete the header line from `project.csv`, load it again with
    `(HEADER)` still set, and see what the two counts say.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### When a load goes wrong

    Most of these stop the load and name themselves. The last is the one to watch,
    because it loads cleanly.

    **A foreign key with no parent.** The message and the fix are two knowledge points
    above.

    **A column count that does not match.** `COPY expense_loaded FROM 'expense.csv'` with
    no column list gives a table of five columns a file of four:

    ```
    Invalid Input Error: Error when sniffing file "expense.csv".
    ...
    * Columns are set as: "columns = { 'id' : 'INTEGER', 'project_code' : 'VARCHAR', ... }",
      and they contain: 5 columns. It does not match the number of columns found by the
      sniffer: 4.
    ```

    The fix is the column list, `COPY expense_loaded (project_code, spent_on, amount,
    category) FROM ...`.

    **A file that is not UTF-8.** Anything exported from an older Windows tool may be
    Latin-1, and one accented character is enough:

    ```
    Invalid Input Error: CSV Error on Line: 2
    Invalid unicode (byte sequence mismatch) detected. This file is not utf-8 encoded.
    ```

    The fix is `read_csv_auto('file.csv', encoding='latin-1')`, and the better fix is to
    ask whoever produced the file to export UTF-8.

    **A date in the other order.** This one is below.
    """)
    return


@app.cell
def _(csv_dir, db):
    (csv_dir / "us-dates.csv").write_text(
        "code,started\nAURORA,03/04/2026\nBEACON,07/11/2026\n"
    )
    _path = (csv_dir / "us-dates.csv").as_posix()
    us_dates = db.sql(f"""
        SELECT a.code,
               a.started AS read_with_the_default,
               b.started AS read_as_month_day
        FROM read_csv_auto('{_path}') a
        JOIN read_csv_auto('{_path}', dateformat = '%m/%d/%Y') b USING (code)
        ORDER BY a.code
    """)
    us_dates
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    That file came off a US machine, where `03/04/2026` is the fourth of March. DuckDB
    read it as the third of April. There was no error and no warning, the column came out
    as a `DATE`, and every date in it is wrong.

    Nothing in the file says which order it is in, so the reader picked day before month
    and both rows happened to be readable that way. A row with `04/13/2026` in it would
    have forced the other reading, and a file with no day above 12 gives the reader nothing
    to go on.

    The fix is to specify the order: `read_csv_auto('file.csv', dateformat = '%m/%d/%Y')`. The habit
    is to read five dates you can check against something else before believing the
    column.

    👉 **Try it:** add a row with `04/13/2026` to that file and read it again with the
    default. Then take a real export from a system you use, look at one date column, and
    state which order it is in and how you know.

    ### ✔ Check your understanding

    1. `CREATE TABLE ... AS SELECT` and `COPY ... FROM` both end with a table full of
       rows. What does each one decide that the other does not?
    2. A load of 40,000 rows raises no error. Name the one query you run next and state what
       two numbers you are comparing.
    3. You have four CSV files and an ERD. How do you work out the order to load them in?
    4. A date column loaded without an error. Give two reasons that is not enough.
    5. A colleague's file will not load because of an encoding error. Name the fix you can
       apply yourself and the fix you would rather have.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 27. Running SQL from a notebook

    Every query in this book runs from a notebook cell. This section covers what you type,
    what comes back, and how a value that lives in Python reaches a query.

    📖 **Used in:** every session that runs a query, from *From a CSV file to a dataframe* to the end of term.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### There is no server

    DuckDB is a Python library. `import duckdb` is the whole installation, and
    `duckdb.connect()` with no argument opens a database that lives in memory for as long
    as the notebook is running. There is no host name, no port, no user name, no password
    and nothing to be granted access to.

    The course warehouse is 13 parquet files sitting in this repository. Opening it is one
    `CREATE TABLE ... AS SELECT * FROM 'file.parquet'` per file, and §26's first knowledge
    point is that statement. The first cell of this notebook runs it for the three tables
    this chapter reads.

    If you have used a database at work, the arrangement here is a different one. A
    database there is a machine somebody else runs. Here the engine is a library inside
    your own process, an **in-process** or **embedded** database, and the data is a set of
    files you already have.
    """)
    return


@app.cell
def _(con):
    con.sql("SELECT database_name, path FROM duckdb_databases()")
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    `memory` is where the three Northwind tables were loaded, and its `path` is `NULL`
    because no file is behind it. A database at that level is a **catalog**, a catalog
    holds a **schema**, and a schema holds the tables, so `customers` written out in full
    is `memory.main.customers`. Give `duckdb.connect()` a filename and that column fills
    in, and the tables are still there tomorrow.

    ### In Excel terms

    Opening a workbook needs no login either. A spreadsheet is a file and an application,
    and this is a file and a library.

    👉 **Try it:** run `duckdb.connect("/tmp/scratch.duckdb")` in a cell, create one table
    in it, then open a second connection to the same path and read the table back.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md("""
    ### A `mo.sql` cell

    marimo has a SQL cell. Add one in the editor and the file gets this:

    ```python
    french_customers = mo.sql(
        "\""
        SELECT "CustomerID", "CompanyName", "City"
        FROM customers
        WHERE "Country" = 'France'
        "\"",
        engine=con,
    )
    ```

    On screen that is a query box with syntax coloring, a picker for which database to run
    against, and a field naming the Python variable the result is bound to.
    `french_customers` is an ordinary Python name holding a polars DataFrame, and every
    cell downstream can use it.

    Three things about it are worth knowing. It runs only inside marimo, so the same cell
    raises in a plain Python script and in Jupyter. What kind of frame it hands back is set
    at the top of the notebook file: every notebook in this course sets
    `sql_output="polars"`, and a notebook that sets `sql_output="pandas"` hands back pandas
    from the same query. And the only route a Python value has into the query is an
    f-string, which the last knowledge point in this section is about.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### `con.sql` runs anywhere

    `con.sql("...")` is the same query without the cell UI. It runs in a marimo cell, in a
    plain script and in Jupyter, so it is what every runnable cell in this book uses.
    """)
    return


@app.cell
def _(con):
    france = con.sql("""
        SELECT "CustomerID", "CompanyName", "City"
        FROM customers
        WHERE "Country" = 'France'
        ORDER BY "CustomerID"
    """)
    france
    return (france,)


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    There are 11 French customers. The last line of a cell is what marimo displays, so
    naming
    `france` on its own is what put the table on the page. Assigning it and stopping there
    would have run the query and shown nothing.

    👉 **Try it:** change `'France'` to `'Brazil'` and count the rows. Then delete the
    final `france` line and run the cell again.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### What comes back is a Python object

    `con.sql` hands back a **DuckDB relation object**, which is a description of a query
    that has not necessarily run yet. It carries the column names and types, and it turns
    into rows when something asks for them.
    """)
    return


@app.cell
def _(con, france):
    con.sql(f"""
        SELECT '{type(france).__name__}' AS python_type,
               {france.shape[0]}         AS rows,
               {france.shape[1]}         AS columns,
               '{france.columns[1]}'     AS second_column
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Because it is a Python variable, the next query can name it as though it were a table.
    DuckDB looks in the calling cell for any name it does not recognize.
    """)
    return


@app.cell
def _(con, france):
    france  # the query below finds this name in the cell
    con.sql("""
        SELECT "City", count(*) AS customers
        FROM france
        GROUP BY "City"
        ORDER BY customers DESC, "City"
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    More ways out of a relation, for when you want plain Python instead of a table on the
    page:

    | | gives you |
    |---|---|
    | `.fetchall()` | a list of tuples, one per row, so `.fetchall()[0][0]` is a single number |
    | `.pl()` | a polars DataFrame |
    | `.df()` | a pandas DataFrame |

    **Older code calls `.fetchone()` on a relation**, and so does much of what an agent
    writes. DuckDB 2.0 removed it, and the call stops with an `AttributeError`.
    `.fetchall()[0][0]` gives the same number in every version.
    """)
    return


@app.cell
def _(con, france, mo):
    france  # the query below finds this name in the cell
    n_french = con.sql("SELECT count(*) FROM france").fetchall()[0][0]
    first_row = con.sql("SELECT * FROM france LIMIT 1").fetchall()
    mo.md(
        f"`fetchall()[0][0]` gives a plain Python `{type(n_french).__name__}` worth "
        f"**{n_french}**, and `fetchall()` gives `{first_row}`."
    )
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    👉 **Try it:** hold `SELECT * FROM orders WHERE "ShippedDate" IS NULL` in a variable,
    then write a second query that names that variable and counts the rows by
    `"CustomerID"`. Then get the same count out with `.fetchall()[0][0]`.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Parameterized queries and the `?` placeholder

    A query is a string, so the obvious way to put a Python value into one is an f-string.
    It works until the value contains an apostrophe.

    ```python
    company = "La corne d'abondance"
    con.sql(f'''SELECT "CustomerID" FROM customers WHERE "CompanyName" = '{company}' ''')
    ```

    ```
    Parser Error: syntax error at or near "abondance"
    ```

    The apostrophe in the name closed the string early, and the parser tried to read
    `abondance` as SQL. The next cell counts how many names in this warehouse would do
    the same.
    """)
    return


@app.cell
def _(con):
    con.sql("""
        SELECT (SELECT count(*) FROM customers WHERE "CompanyName" LIKE '%''%')
                 AS customers_with_an_apostrophe,
               (SELECT count(*) FROM products  WHERE "ProductName" LIKE '%''%')
                 AS products_with_an_apostrophe
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Six customers and nine products carry one, among them `Bon app'`, `Chef Anton's Cajun
    Seasoning`, `Grandma's Boysenberry Spread` and `Sirop d'érable`. Any filter built by f-string
    breaks on all 15 of them.

    The fix is a **placeholder**, which makes this a **parameterized query** and the value
    a **bind parameter**. Write `?` where the value goes, hand the value to `con.execute`
    as a list, and the value travels beside the query instead of inside it.
    The parser never sees it, so an apostrophe is just a character.
    """)
    return


@app.cell
def _(con, mo):
    company = "La corne d'abondance"
    _rows = con.execute(
        """
        SELECT "CustomerID", "CompanyName", "Country"
        FROM customers
        WHERE "CompanyName" = ?
        """,
        [company],
    ).fetchall()
    mo.md(f"The placeholder form returns `{_rows}`.")
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    **The trap.** `mo.sql` does not take placeholders. The same `?` in a SQL cell gives:

    ```
    Binder Error: Unexpected prepared parameter. This type of statement can't be prepared!
    ```

    So a query whose value came out of data goes through `con.execute`. A `mo.sql` cell is
    for a query you wrote by hand.

    👉 **Try it:** run the f-string version and the placeholder version on `Bon app'` and
    read both outcomes. Then set `company` to `Alfreds Futterkiste` and confirm the
    f-string version works.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Input from somebody else

    A notebook that takes a value from whoever uses it, through a text box or a dropdown,
    puts that value into a query. Built with an f-string, whatever was typed becomes part
    of the SQL, so SQL typed into the box runs. This is called **SQL injection**.

    The cell below counts one customer's orders twice, once through an f-string and once
    through a placeholder. It does that for a real customer ID and for an input written to
    break out of the quotes.
    """)
    return


@app.cell
def _(con, mo):
    def count_orders(customer_id):
        # the f-string writes whatever was typed into the SQL itself
        return con.sql(
            f"""SELECT count(*) FROM orders WHERE "CustomerID" = '{customer_id}'"""
        ).fetchall()[0][0]

    def count_orders_safely(customer_id):
        return con.execute(
            """SELECT count(*) FROM orders WHERE "CustomerID" = ?""", [customer_id]
        ).fetchall()[0][0]

    _rows = [
        f"| `{_typed}` | {count_orders(_typed):,} | {count_orders_safely(_typed):,} |"
        for _typed in ["ALFKI", "x' OR '1'='1"]
    ]
    mo.md("| typed | f-string | placeholder |\n|---|---|---|\n" + "\n".join(_rows))
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    `ALFKI` has 163 orders, and both versions find them. The second input closes the quote
    early, and `OR '1'='1'` is true on every row, so the f-string version counts all 16,282
    orders in the warehouse. The placeholder version looks for a customer whose ID is that
    whole string, and there is none.

    The classic case is a login form. A username ending in `'--` turns the rest of the
    query, the password check included, into a comment. Any value that comes from outside
    your code goes into a query through `?`.

    👉 **Try it:** add `ALFKI' --` as a third input and predict both counts before you run
    it.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### ✔ Check your understanding

    1. Name one thing a `mo.sql` cell gives you that `con.sql` does not, and one thing
       `con.sql` gives you that `mo.sql` does not.
    2. `con.sql` returned something and the cell showed nothing. Name two different
       reasons.
    3. Your f-string query has worked on 87 customer names. What do you know about the
       other 6?
    4. Somebody asks where the database server is. What do you tell them?
    5. You need the count as a number to put in a sentence. Which method gets it out of
       the relation?
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 28. Views

    A **view** is a query with a name. Selecting from the view runs the query underneath
    and hands back the rows. No rows are stored, so a view costs almost nothing to create
    and is never out of date.

    📖 **Used in:** Building and querying your database
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### Creating one

    Chapter A's warehouse holds 16,282 orders, and one recurring question about them is
    how many shipped after the date they were required. As a view, that question gets a
    name every other query can use.
    """)
    return


@app.cell
def _(con):
    con.execute("""
        CREATE OR REPLACE VIEW late_orders AS
        SELECT "OrderID", "CustomerID", "OrderDate", "RequiredDate", "ShippedDate"
        FROM orders
        WHERE "ShippedDate" > "RequiredDate"
    """)
    late_orders = con.sql("""
        SELECT count(*) AS late,
               (SELECT count(*) FROM orders) AS all_orders,
               round(100.0 * count(*) / (SELECT count(*) FROM orders), 1) AS percent
        FROM late_orders
    """)
    late_orders
    return


@app.cell
def _(con):
    con.sql("""SELECT * FROM late_orders ORDER BY "OrderID" LIMIT 5""")
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The view holds 3,755 of 16,282 orders, which is 23.1 percent. `late_orders` now
    behaves like a table in every query in this notebook, including a join or a
    `GROUP BY`.

    👉 **Try it:** write `SELECT "CustomerID", count(*) FROM late_orders GROUP BY
    "CustomerID" ORDER BY 2 DESC LIMIT 5`. Then write the same query without the view and
    compare the two statements.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### A view re-runs every time

    Nothing is cached. The query underneath runs again on every select, so a view over a
    table that changes gives a different answer after the change.
    """)
    return


@app.cell
def _(db):
    db.execute("""
        CREATE OR REPLACE VIEW active_projects AS
        SELECT code, name, budget FROM project WHERE status = 'active'
    """)
    _before = db.sql("SELECT count(*) FROM active_projects").fetchall()[0][0]
    db.execute("UPDATE project SET status = 'closed' WHERE code = 'AURORA'")
    _after = db.sql("SELECT count(*) FROM active_projects").fetchall()[0][0]
    view_reruns = db.sql(f"""
        SELECT {_before} AS rows_before_the_update,
               {_after}  AS rows_after_the_update
    """)
    view_reruns
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    The view held one row before the `UPDATE` and none after, from the same three words
    of SQL. That is what a view is for. It is also why a view over a slow query is still slow.

    👉 **Try it:** set `AURORA` back to `active` and read the view again without touching
    the `CREATE VIEW` statement.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ### What the database keeps

    A view is stored as text. `duckdb_views()` shows what the database wrote down.
    """)
    return


@app.cell
def _(con):
    con.sql("SELECT view_name, sql FROM duckdb_views() WHERE NOT internal")
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    Editing a view means replacing that text with `CREATE OR REPLACE VIEW`, and removing
    it is `DROP VIEW late_orders`. Neither touches a single row.

    **The trap.** A view looks like a table until you write to it:

    ```sql
    INSERT INTO late_orders VALUES (99999, 'VINET', NULL, NULL, NULL);
    ```

    ```
    Catalog Error: late_orders is not an table
    ```

    Views in DuckDB are read-only. The rows live in the tables underneath, and that is
    where an `INSERT` or an `UPDATE` has to go.

    👉 **Try it:** create a view over `late_orders` itself, holding only the orders from
    2016, and confirm a view over a view works. Then drop `late_orders` and find out what
    happens to the second one.

    ### ✔ Check your understanding

    1. A view stores no rows. Name one thing that buys you and one thing it costs you.
    2. Somebody says a view will make a slow query fast. How do you answer?
    3. You have written the same 12-line `WHERE` clause into four queries. What does a
       view change about that, and what does it not change?
    4. A view is defined over a table and somebody drops the table. What do you expect to
       happen, and when?
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## Chapter challenge

    Nothing here is due. Chapter A had you design a database. This is building it.

    **Take the ERD you drew for Chapter A's chapter challenge and make it real.** Write the
    `CREATE TABLE` statements, invent 10 to 30 rows per table in a spreadsheet, export them
    as CSV, and load them.

    **Done looks like** a notebook that runs from top to bottom on an empty database and
    ends with a row-count check per table showing the file count and the table count side
    by side. Every table has a primary key. Every relationship on your ERD is a declared
    foreign key. Every money column is a `DECIMAL` and every date column is a `DATE`.

    **Five decisions are yours, and the notebook cannot make them.**

    1. Which columns get a `CHECK`, and what does each one forbid? Write the rule down in
       words first, then in SQL.
    2. Which ids do you invent and which do you let a sequence hand out? Chapter A §2 is
       the argument; this is where you commit to it.
    3. What order do the files load in, and what in the ERD told you?
    4. Which columns are `NOT NULL`? For each one you make optional, name the query that
       has to handle the `NULL`.
    5. What does your row-count check do when the numbers disagree? Printing them is not
       the same as noticing.

    **Going further.** Put a mistake in one of the CSV files on purpose, something that
    loads without an error, such as a date in the other order or a budget that lost its
    decimal point. Hand the notebook to somebody else in the course and ask them to find
    it using only queries. Write down how long it took and which query found it. Then add a
    view for each question your design was built to answer, and see whether the person who
    reads your views can tell what the business does without reading the tables.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## 🤖 Ask your agent

    Two different uses, and the second one is the one this course grades.

    ### Ask it about what this chapter left out

    - Ask what an **index** is, why a primary key usually gets one without being asked, and
      what having one costs on a table you write to often.
    - Ask how to add a column to a table that already holds a million rows, what
      `ALTER TABLE` can and cannot change, and what DuckDB does that SQLite does not.
    - Ask what an **upsert** is, and show it `INSERT ... ON CONFLICT DO UPDATE`. Ask when
      you would want that instead of deleting and reloading.
    - Ask what `read_csv` options like `ignore_errors`, `null_padding` and `sample_size`
      do, and for each one, ask what it hides.
    - Ask what a **materialized** view is, why it can be stale, and whether DuckDB has one.

    Test whatever comes back. Run it.

    ### Watch what it decides without telling you

    Give it this, exactly:

    > *"Write the CREATE TABLE statements for a small consultancy that bills clients for
    > projects. People book hours against projects and projects carry a budget."*

    Then run every statement it gives you against DuckDB and answer five questions.

    1. **How many statements failed to parse?** Read each message.
    2. **What type did it pick for the budget and for the hourly rate?** If either one is
       `REAL`, `FLOAT` or `DOUBLE`, ask it to add up 10 dimes.
    3. **Did it write `AUTOINCREMENT`?** That is the SQLite spelling, and it tells you
       which engine it assumed. Ask which engine it was writing for and see whether the
       answer matches what it wrote.
    4. **Did it declare foreign keys, and did it write `ON DELETE CASCADE`?** One of those
       is a parser error here.
    5. **What did it store a date as?** If it is `TEXT`, ask it to find every project that
       started in the last quarter.

    You asked for a schema and got a set of engine assumptions, and none of them was in
    your request.

    👉 **Try it:** send the same request again with one sentence added, *"target DuckDB"*,
    and put the two schemas side by side. Then send it the parser error from the first
    attempt and see whether the fix it offers changes anything else in the statement.
    """)
    return


@app.cell(hide_code=True)
def _(mo):
    mo.md(r"""
    ---
    ## Chapter D checklist

    - [ ] I can write a `CREATE TABLE` with a primary key, `NOT NULL`, `DEFAULT` and
          `CHECK`, and state what each one refuses
    - [ ] I can choose a type for money, for a date and for a code, and explain why `REAL` is
          the wrong answer for the first one
    - [ ] I can declare a foreign key, and I know which table has to be dropped first
    - [ ] I can give a table an id without `AUTOINCREMENT`
    - [ ] I can add, change and remove rows, and I write the `WHERE` before the `SET`
    - [ ] I can wrap a destructive statement so there is a way back
    - [ ] I can load a CSV two ways and state which one decided the types
    - [ ] I can work out the order a set of files has to be loaded in from an ERD
    - [ ] I check row counts after every load, and I know what I am comparing
    - [ ] I can spot a date column that loaded cleanly and is wrong
    - [ ] I can run a query from a notebook, hold the result in a variable and query that
          variable
    - [ ] I can get a Python value into a query without an f-string, and explain why that
          matters on this warehouse
    - [ ] I can create a view and explain what it stores

    **That is the last of the four content chapters.** Part A is what a database is, B is
    one table, C is many tables, and D is building one. The practice bank is indexed by
    section number, so anything here that needs more repetitions has more waiting.
    """)
    return


if __name__ == "__main__":
    app.run()