Chapter 7: Window Functions
A window function performs a calculation across a set of rows related to the current row. Unlike aggregate functions, window functions do not collapse rows into a single result. Every row keeps its identity and receives a computed value based on its window.
Chapter 8: Pivots
A pivot transforms rows into columns. Data stored as one row per category becomes one column per category. This is the difference between a normalized database format and a spreadsheet-style cross-tab report that analysts and dashboards typically need.
Chapter 9: Data Modification and Schema Design
All queries so far have been read-only. This chapter covers the other side: writing data with INSERT, UPDATE, DELETE, and MERGE, and designing the tables that hold it with CREATE TABLE, constraints, and schema organization.
Chapter 10: Stored Procedures
A stored procedure is a named collection of SQL statements saved in the database and executed on demand. Instead of sending the same complex query from an application every time, you store the logic once and call it by name. The database compiles the procedure on first execution and caches the execution plan, which makes repeated calls faster than submitting raw SQL each time.
Chapter 11: Indexes
A customer calls Baldwin Bikes, the BikeStores shop in New York, about an order. The clerk types her surname into the support screen and waits. With 1,445 customers the answer comes back instantly. With 14 million, after a few years of growth and two acquisitions, the same screen takes forty seconds, and the nightly sales pipeline that joins orders to customers misses its window.
Chapter 12: ROLLUP, CUBE and GROUPING SETS
The BikeStores sales manager wants one report for the board: revenue for each store in each year, a subtotal for every store, and the total for the whole company at the bottom. One table, read top to bottom, the way a spreadsheet with subtotals reads.