Continue the engine you built in parts one through three, invoked as:

SQL on stdin, results on stdout, exit 0. Re-declare the commands kept
in session memory: AGENTS.md (or README.md) must carry a run: line
with the exact command that starts the engine (e.g. run: sh mydb.sh,
run: python3 db.py) and a test: line with the command that runs
your test suite (e.g. test: sh test.sh). AGENTS.md wins when both
declare one.

The earlier parts are not optional prerequisites, they are the floor
this part stands on. One check replays them in a single breath:
CREATE TABLE, a handful of INSERTs, a process restart on the same
data directory, then a SELECT with WHERE and ORDER BY and a
SELECT COUNT(*). If persistence, filtering, ordering or aggregates
rusted since part three, fix them before reaching for joins.

The output contract is the one frozen in part one, unchanged.

Implement qualified column references: t.col names the column col
of table t, and works everywhere a bare column name works — the
select list, WHERE, ORDER BY.

On a single table SELECT t.name FROM t WHERE t.id > 5 ORDER BY t.name; must mean exactly what its unqualified twin means, and bare
and dotted references must mix freely in one statement. This is the
hinge the rest of the part swings on: once two tables sit in one
query, a.k and b.k are different columns and the dot is the only
thing telling them apart. Worth 20 points.

Implement SELECT a.x, b.y FROM a JOIN b ON a.k = b.k — the inner
join of two tables on column equality. A row appears in the output for
every pair whose keys match; rows without a partner on the other side
simply do not appear, and a join with no matching pairs at all is an
empty result set: the single line SELECT 0.

The graded query always carries an ORDER BY, because join output
order is unspecified — a nested loop, a hash and a merge are all
correct until the sort makes them agree. Worth 40 points.

A WHERE clause on a join filters the joined rows — the predicate may
reference either side by its qualified name: SELECT a.x, b.y FROM a JOIN b ON a.k = b.k WHERE a.k > 30, or ... WHERE b.y = 'ops'.

Whether you filter before joining or after is your engine's business;
the result must be the same set either way. A predicate nothing
survives leaves SELECT 0. The graded query carries an ORDER BY, as
every join query here does. Worth 40 points.

Chain the joins: SELECT a.x, c.z FROM a JOIN b ON a.k = b.k JOIN c ON b.m = c.m walks from a through b to c, and only rows whose
chain is complete at every link make it out. A chain broken at either
hop — a key b never heard of, or an m value c does not carry —
contributes nothing.

Two joins are not a special case of one; they are the same case,
applied twice. If your join produces a table, feeding it into the next
join is free. The graded query carries an ORDER BY. Worth 60 points.

Implement LEFT JOIN: every left row appears exactly once whether or
not it found a partner, and this is where a clause of the part-one
contract finally fires — NULL prints as the empty string. A left row
with no match prints its own values and nothing where the right
columns would be, delimiter included: 7|.

Right-only rows still stay home; LEFT JOIN widens the left table,
it does not rescue the right one. When every left key happens to
match, the output is indistinguishable from the inner join — that is
not a coincidence, it is the definition. The graded query carries an
ORDER BY. Worth 40 points.

Aggregate what the join produced: SELECT b.dept, COUNT(*) FROM a JOIN b ON a.k = b.k GROUP BY b.dept ORDER BY b.dept; counts joined rows
per group, not rows of either table alone. Rows that fell out of the
inner join are not counted anywhere, and a group only exists if at
least one joined row landed in it.

Part three built GROUP BY over one table; the only news here is that
its input is now a join result. If your pipeline is honest — join
first, group what comes out — this task is already done. Worth 40
points.

Everything at once, across a process boundary: one invocation creates
both tables and fills them, the process exits, and a second invocation
on the same data directory answers the join.

Nothing new to implement — part two made rows survive a restart, this
part made them joinable — but the combination is what the campaign is
building toward, and this check makes sure neither half quietly
depends on the other's in-memory leftovers. Worth 20 points.