Chapter 4: Aggregation
Every query so far returns individual rows. Most business questions don't work that way. "How many products do we carry?" "What is our average order value?" "Which category sells the most?" These questions summarize across many rows. That is aggregation.
Chapter 5: Subqueries
A subquery is a query nested inside another SQL statement. The outer statement can be SELECT, INSERT, UPDATE, or DELETE. The inner query runs first, produces a result, and the outer query uses that result.
Chapter 6: Common Table Expressions (CTEs)
The subquery that closed Chapter 5 worked — two nested levels, a derived table inside a scalar subquery inside a WHERE clause. It was correct. It was also difficult to read. You had to trace inward to understand the outermost result. That kind of query is how bugs stay hidden: something changes in one subquery level and the developer above it assumes the old behaviour.