Practise · A sanity query doubles the rate total

Opening your saved work…

The reading

Build it twice

The desktop kit holds five files: schema.sql, load.sql, verify.sql, items.csv and a README. In the extracted folder, open sqlite3 with a database file that does not exist yet, then run .read schema.sql, .read load.sql and .read verify.sql, and .quit. Then do it again into a second new file, rehearsal-again.db, without deleting the first.

-- In a terminal, inside the extracted kit folder
sqlite3 rehearsal.db
.read schema.sql
.read load.sql
.read verify.sql
.quit

Desktop only. Then the same lines again with rehearsal-again.db.

Two databases built from the same scripts and the same file should agree row for row. That is what reproducible means on the project checklist: anyone who runs your scripts gets your database. Anything you typed only into the shell is missing from the second build, which is how the rebuild catches it.

.bail on, near the top of each script, stops the script at the first error. It is not a rollback: whatever ran before the error stays done, and the file is half built. Start again in a new file rather than rerunning into it. The scripts expect an empty database on purpose; run load.sql into one that is already loaded and its first insert is refused on the duplicate gear type, so nothing is appended twice.

Rule of thumb. A database is reproducible when two fresh builds from the same scripts agree row for row.

Going deeper (optional, not on the exam)

.read, .bail and .quit are shell commands, like .import: they belong to sqlite3 and never run in the browser. The browser exercises read a build that has already happened; the builds themselves are a desktop skill.

A matching total is the weakest check

verify.sql ends with a sanity join: each gear type with its count of items and the sum of their daily rates. Trail gear has two items and 18, Water gear one item and 12. When both builds print 18 and 12, that is reassuring, and not much more.

SELECT g.name AS gear_type, COUNT(i.id) AS items, SUM(i.daily_rate) AS rate_sum
FROM gear_types g
LEFT JOIN items i ON i.type_id = g.id
GROUP BY g.id, g.name
ORDER BY g.id;

Two gear types: two items and 18, one item and 12. Sums of listed rates, not revenue. Open in the playground

A teammate's version of the join pairs the tables ON 1 = 1. Every item meets every type, each type reports 30, and the total across both types doubles from 30 to 60. Run it in both builds and they agree perfectly on the wrong numbers. A sanity query that agrees with itself proves nothing; the ON must pair items.type_id with gear_types.id, the key and not a coincidence.

And 18 is a sum of listed daily rates, not revenue. There are no rentals in this database, so no query on it can say what Trail Gear earned. Say what a number is before you compare it.

Compare the strongest evidence first: the ordered rows of items, column by column; the count in every table; the notes that are NULL (two) against the notes that are empty strings (none); PRAGMA foreign_key_check, which should return no rows, and PRAGMA integrity_check, which should say ok; and only then the totals. Then, on a disposable copy, try the inserts that must fail, a duplicate serial, a negative rate and a type with no parent, and paste the exact errors.

Rule of thumb. A matching total is the weakest evidence. Compare the rows, then the counts, then the totals.

Going deeper (optional, not on the exam)

The multiplication trap from chapter 7 is the same mistake with a real key: a one-to-many join repeats the one side, and a sum taken after it grows. ON 1 = 1 is the extreme case, every row paired with every row.

A sanity query doubles the rate total · two fresh builds, verify.sql, what a matching total proves · level 4 of 6

The sanity join

Level 4: Write it yourself · Exercise 1 of 1

Return gear_type and rate_sum for each gear type that has loaded items, in gear type id order, where rate_sum is the sum of those items' daily rates.

Finish line: 2 rows · columns gear_type, rate_sum · in the order the question gives

Data: Trail Gear: build rehearsal.

I would like help with this exercise

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Open in the playground

Query results

Run your query to see the result here.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.