Databases & SQL for Analysts

Branch: CS Foundations (quant-cs) · Quant-Code region · Node CS2.03 · ~20 focused hours · Gate: quiz ≥ 85% + code-pack ≥ 85% (hidden tests green on at least 85% of exercises)

Every analyst who has ever kept "final_v3_ACTUAL.xlsx" next to "final_v2_use_this_one.xlsx" already knows the problem this module solves, even if they have never heard the word for it: the moment the same fact (a company's name, a price, a filing) lives in more than one place, those copies will drift apart, and nobody will notice until a number in a memo is quietly wrong. A relational database is the discipline that fixes this at the root: every fact lives in exactly one place, related facts are linked by an explicit key instead of a repeated copy, and a query language (SQL) lets you ask precise questions across all of it at once without ever touching the underlying files by hand. This module teaches that language, and the handful of ideas beneath it (why the schema is shaped the way it is, how a query actually gets executed, what makes an index fast and a transaction safe), against one running database built for exactly this course: a small, synthetic, multi-table finance database (companies, prices, filings, transactions) with both an India-flavored and a US-flavored set of holdings inside it.

You will not memorize SQL as a list of keywords. You will build, one verb at a time, the actual mental model a working analyst uses at a keyboard: filter and sort (SELECT/WHERE/ORDER BY), combine tables without duplicating data (JOIN), collapse many rows into a summary (GROUP BY/HAVING), attach a per-row answer without collapsing anything (window functions), and structure a multi-step question into readable stages (CTEs, the WITH clause). Layered underneath: why the schema is normalized the way it is, what an index actually buys you, and why a system that records financial transactions cannot skip the ACID guarantees. Every one of these ideas becomes a real, graded SQL exercise in this module's code pack (pack id CS2.03, exercises CS2.03-e01 through CS2.03-e08), real SQL, executed live in your browser against the bundled database via sql.js, checked against hidden tests built from both an India-flavored and a US-flavored slice of the data, so a query that only works on the one example you happened to test by hand will be caught immediately.

Learning objectives

By the end you can:

1. Explain what a relational database is and why an analyst reaches for one instead of a folder of spreadsheets (rows, columns, tables, primary keys, and foreign keys) and read this module's four-table schema (companies, prices, filings, transactions) fluently, naming what each key relates to what.
2. Write SELECT queries that filter with WHERE, sort with ORDER BY (including multi-column and mixed ascending/descending), and page results with LIMIT/OFFSET.
3. Combine two or more tables with INNER JOIN and LEFT JOIN, state precisely how each one treats an unmatched row, and predict (before running anything) which join type a given question requires (and the classic bug: picking the wrong one silently drops or silently null-pads rows).
4. State SQL's logical execution order (FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT) and use it to explain, from first principles, why WHERE cannot filter on an aggregate but HAVING can.
5. Aggregate data with GROUP BY, HAVING, and the five core aggregate functions (COUNT, SUM, AVG, MIN, MAX), producing one summary row per group.
6. Use window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD, and framed aggregates via OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...)) to answer per-row "rank within group," "latest per group," and "moving average" questions that GROUP BY cannot express because it collapses rows instead of keeping them.
7. Write and chain Common Table Expressions (WITH ... AS (...)) to break a multi-step query into named, readable, individually-checkable stages, including the standard pattern of filtering on a window function's result in an outer query because you cannot filter on it directly in the same SELECT.
8. Describe database normalization at an applied, intuitive level (1NF: atomic values, no repeating groups; 2NF: no partial dependency on part of a composite key; 3NF: no non-key column depending on another non-key column) well enough to say whether a given table design is normalized or flattened, and state the concrete trade-off a team accepts when it deliberately denormalizes for reporting speed.
9. Explain what an index is, predict when a given query would benefit from one, and read a simple EXPLAIN QUERY PLAN output to tell whether a query used an index (SEARCH) or scanned the whole table (SCAN).
10. State the four ACID properties (Atomicity, Consistency, Isolation, Durability) and, for each one, give a concrete example of what goes wrong in a system recording financial transactions if that property is missing.
11. (Productivity objective: R10 duality.) Use an AI coding assistant to draft a SQL query under the Copilot Discipline, describe the question precisely yourself first, then let the assistant accelerate the draft, then always review every clause against the actual schema and run it against this module's tests, because a hallucinated column name or a silently wrong join reads exactly as confidently as a correct query.

The duality, stated once (R10). Objectives 1–10 are the understanding objective: the gate (quiz ≥ 85%, code-pack ≥ 85% with hidden tests green) rewards being able to read the schema, write the query, and explain why it is correct, by hand, on paper, and in the editor. Objective 11 is the productivity objective: knowing how a working analyst uses an AI copilot on top of that understanding, never instead of it. A copilot can produce a fluent-looking JOIN in one second. It cannot make that JOIN correct for your schema, and it cannot pass this module's hidden tests for you, only a query that is actually right, on data it has never seen, does that.

Prerequisites & connections

Builds on. CS1.01 and CS1.02 (Python I/II) are not required to start SQL (it is a different paradigm, declarative rather than step-by-step) but the habits they built (naming things precisely, distrusting your first draft, tracing a small example by hand before trusting a general rule) transfer directly, and this module leans on them from the first worked example. CS2.01 (Command Line, Environments & Reproducible Projects) is where you would run a real sqlite3 command-line session on your own machine; this module's own graded work runs the bundled database entirely in your browser via sql.js, so no local install is required to complete it, but CS2.01's terminal fluency makes exploring the database on your own far more comfortable. Beyond ordinary arithmetic (a SUM, a percentage, a running total), no math is assumed.

Feeds forward. CS2.04 (Systems & Data-Intensive Thinking) builds directly on the indexing and normalization intuition built here: row-oriented vs. columnar storage, Parquet, and a small analyst warehouse all start from "why would a database engine store or look up data this way," a question this module answers for the single-table, row-at-a-time case first. DA1.02 and DA1.03 (pandas I/II) teach the exact same four verbs, filter, join, aggregate, and a window/rolling operation, in pandas instead of SQL; DA1.03 explicitly cross-references this module's JOIN/GROUP BY/window-function material rather than re-deriving it (per the program's binding rule that a skill taught once here is referenced, not repeated, everywhere else it recurs), so the two together give you the same mental model in both languages and a clear sense of when to reach for which. CS2.06 (Software Engineering) later asks you to review AI-generated code for subtly wrong logic that still passes the happy path, this module's ai-augment-json callouts on reviewing a drafted JOIN or WHERE clause are the first, smallest-scale rehearsal of exactly that discipline. Further out, essentially every "query the data first" step in the Quant Developer branch: starting with QD1.01's point-in-time, survivorship-controlled data panels, assumes you can already join and aggregate relational data fluently before layering time-aware correctness rules on top of it.

Cross-branch companions (soft, not gates). None required. If you already have basic finance vocabulary (a company's ticker, a fiscal year, a buy/sell transaction) from the analyst-track phases, this module's running examples will feel immediately familiar; if you do not, every term is explained from scratch here, because this branch's day-one modules guarantee zero required prior coursework.

Core teaching content

1. Why an analyst reaches for a database instead of ten spreadsheets

Picture the alternative this module exists to replace: one spreadsheet listing companies, a second listing daily prices (with the company name retyped on every single row), a third listing quarterly filings (company name retyped again), and a fourth listing every buy/sell transaction (company name retyped a third time). The moment a company's ticker changes, or someone fat-fingers "Nilam Software" as "Nilam Softwear" on row 4,812 of the prices sheet, you now have a silent, undetected inconsistency, and no single place to fix it. This failure mode has a name, an update anomaly, and it is not a hypothetical: it is the single most common way "the numbers don't tie out" actually happens in real analyst work, and it is entirely structural, not a matter of anyone being careless.

A relational database fixes this by storing each fact exactly once, in the table that "owns" it, and linking related facts by an ID instead of a copy. A company's name lives in exactly one row of a companies table; every price, filing, and transaction that concerns that company stores only its numeric company_id and looks the name up through that link whenever it's needed. Rename the company once, in one row, and every join that touches it is instantly, automatically correct, because nothing else ever held a private copy to go stale. This single idea, store each fact once, link everything else to it by key, is the seed that the rest of this module, and the formal theory of normalization in §9, will grow into a precise, checkable discipline.

The tool you use to ask questions across these linked tables is SQL (Structured Query Language, usually pronounced "sequel" or spelled out "S-Q-L", either is fine, and you will hear both from working analysts). SQL is declarative: you state what result you want, not how, step by step, to produce it, a sharp contrast with the Python you wrote in CS1.01/CS1.02, where you spelled out every loop and comparison yourself. SELECT ticker FROM companies WHERE country = 'IN' says "give me the tickers of India-listed companies", it does not say "open the file, read each row, check a condition, collect matches" the way an equivalent Python function would. The database engine (in this module, SQLite, running in your browser through sql.js) figures out how to satisfy that request as efficiently as it can, which is exactly why §8–9 (normalization and indexing) matter: they are about giving the engine a schema and structure it can satisfy your declarative requests against quickly, not about you personally optimizing a loop.

2. The schema you'll query: companies, prices, filings, transactions

Every exercise, worked example, and practice problem in this module queries the same four-table schema, bundled as finance-db.sqlite. Learn it once, here, and treat it as a fixed reference for the rest of the module:

CREATE TABLE companies (
  company_id   INTEGER PRIMARY KEY,
  ticker       TEXT NOT NULL UNIQUE,
  name         TEXT NOT NULL,
  country      TEXT NOT NULL CHECK (country IN ('IN','US')),
  sector       TEXT NOT NULL,
  exchange     TEXT NOT NULL,
  currency     TEXT NOT NULL CHECK (currency IN ('INR','USD'))
);

CREATE TABLE prices (
  price_id     INTEGER PRIMARY KEY,
  company_id   INTEGER NOT NULL REFERENCES companies(company_id),
  trade_date   TEXT NOT NULL,          -- 'YYYY-MM-DD'
  open         REAL NOT NULL,
  high         REAL NOT NULL,
  low          REAL NOT NULL,
  close        REAL NOT NULL,
  volume       INTEGER NOT NULL,
  UNIQUE (company_id, trade_date)      -- one row per company per trading day
);

CREATE TABLE filings (
  filing_id         INTEGER PRIMARY KEY,
  company_id        INTEGER NOT NULL REFERENCES companies(company_id),
  fiscal_year       INTEGER NOT NULL,
  period            TEXT NOT NULL CHECK (period IN ('FY','Q1','Q2','Q3','Q4')),
  filing_type       TEXT NOT NULL,     -- e.g. 'Annual Report', '10-K', '10-Q'
  revenue           REAL NOT NULL,
  net_income        REAL NOT NULL,
  total_assets      REAL NOT NULL,
  total_liabilities REAL NOT NULL,
  filed_date        TEXT NOT NULL
);

CREATE TABLE transactions (
  transaction_id INTEGER PRIMARY KEY,
  account_id     INTEGER NOT NULL,
  company_id     INTEGER NOT NULL REFERENCES companies(company_id),
  txn_date       TEXT NOT NULL,
  side           TEXT NOT NULL CHECK (side IN ('BUY','SELL')),
  quantity       INTEGER NOT NULL,
  price          REAL NOT NULL,
  fees           REAL NOT NULL
);

Read the relationships out loud, because they are the whole game: prices.company_id, filings.company_id, and transactions.company_id each reference companies.company_id, this is a foreign key, a column whose job is to point at another table's primary key (the column, company_id, that uniquely identifies one row of companies and can never repeat or be empty). Nothing about a company's name, sector, or currency is ever copied into prices, filings, or transactions, those three tables only ever store the numeric company_id, and every query that needs the human-readable name gets it by joining back to companies (§4). This is exactly the "store each fact once" discipline from §1, made concrete.

Here is a small, illustrative slice of the actual data you will query throughout this module, eight companies, four India-listed (NSE, ₹) and four US-listed (NASDAQ/NYSE, $), across four matched sectors:

Every company name, ticker, price, filing figure, and transaction in this module (including the table above) is entirely fictional and synthetic (seeded, illustrative: R3), generated for teaching purposes. None of it is a real historical quote or a real company's actual reported data; any resemblance of a ticker to a real one is coincidental. Two matched price rows for NILAM and BRPEAK, so you can see the shape end to end: (price_id=1, company_id=1, trade_date='2026-01-05', open=1180.00, high=1195.00, low=1175.00, close=1190.00, volume=320000) and (price_id=3, company_id=5, trade_date='2026-01-05', open=86.00, high=88.00, low=85.50, close=87.40, volume=4800000), note the currency is implied by which company you joined to, never restated in prices itself.

3. SELECT, WHERE, ORDER BY, LIMIT: filtering and shaping a result set

The skeleton of almost every query you will write is:

SELECT <columns>
FROM <table_name>
WHERE <condition>
ORDER BY <columns> [ASC|DESC]
LIMIT <n>;

SELECT ticker, name FROM companies returns every row's ticker and name: nothing filters it yet. WHERE country = 'IN' keeps only rows where that condition is true; SQL's comparison operators (=, != or <>, <, >, <=, >=) and logical operators (AND, OR, NOT) work exactly the way you would expect from CS1.02, with one sharp trap: AND binds tighter than OR, so WHERE country = 'IN' OR country = 'US' AND sector = 'Banking' does not mean "(India or US) and Banking", it means "India, or (US and Banking)", an India-listed Energy company would still match. When a condition mixes AND and OR, add parentheses every time; they cost nothing and remove all ambiguity, exactly the CS1.01 habit of "when in doubt, parenthesize" applied to a new language.

ORDER BY sector, ticker sorts ascending by sector first, and within each identical sector value, ascending by ticker, this is a genuine two-key sort, not two separate sorts; append DESC to any column to reverse just that one (ORDER BY sector ASC, close DESC is completely valid). A result set has no guaranteed order unless you ORDER BY it, full stop. A query engine is free to return rows in whatever order is cheapest for it internally, and that order can silently change between runs, after a schema change, or after the engine gains a new index, code that "happens to look sorted" during testing and ships without an explicit ORDER BY is a bug waiting for a data change to expose it.

LIMIT 5 caps the result to the first 5 rows after sorting, "top 5 by X" is always ORDER BY X DESC LIMIT 5, never the reverse; LIMIT without ORDER BY gives you 5 arbitrary rows, not the 5 largest or smallest anything. LIMIT 5 OFFSET 10 skips the first 10 matching rows and returns the next 5, the standard mechanism for paging through a large result a screen at a time.

This module's exercise CS2.03-e01 is exactly this section: filter companies to one country, sort by two columns.

4. JOINs: combining tables without duplicating data

A JOIN is how you answer a question that needs columns from more than one table at once, "show me each company's name next to its closing price" needs companies and prices together, and the link between them is exactly the foreign key from §2:

SELECT c.ticker, p.trade_date, p.close
FROM prices p
JOIN companies c ON c.company_id = p.company_id
WHERE p.trade_date = '2026-01-06'
ORDER BY c.ticker;

p and c are table aliases: short names you assign right after the table name, used everywhere after to say which table a column comes from (c.ticker vs. p.close), essential the moment two tables share a column name (both tables here have company_id) and good practice even when they don't, because it makes every query self-documenting. JOIN ... ON ... (equivalently INNER JOIN, the two are identical in SQLite) keeps only the rows where the ON condition is satisfiable on both sides, a prices row with no matching companies row (which should never happen here, given the foreign key) would simply vanish from the result, and, more importantly for real analyst work, a companies row with no matching prices row at all vanishes too.

That last sentence is the single most consequential fact in this section, so make it concrete. Suppose a portfolio holds five companies, and on a given day two of them (say a recent listing and one that was halted) have no prices row at all for that date:

-- INNER JOIN: silently drops the two holdings with no row that day
SELECT c.ticker, p.close
FROM companies c
JOIN prices p ON p.company_id = c.company_id AND p.trade_date = '2026-06-15'
ORDER BY c.ticker;
-- returns 3 rows: KAVERI, NILAM, SAGAR -- VASANT and PIPPAL are simply GONE, not flagged

-- LEFT JOIN: keeps every one of the five, with NULL where prices has nothing to offer
SELECT c.ticker, p.close
FROM companies c
LEFT JOIN prices p ON p.company_id = c.company_id AND p.trade_date = '2026-06-15'
ORDER BY c.ticker;
-- returns all 5 rows: KAVERI, NILAM, PIPPAL (close=NULL), SAGAR, VASANT (close=NULL)

A LEFT JOIN keeps every row from the table named on the left (companies, here) regardless of whether a match exists on the right, filling every right-hand column with NULL when it doesn't. This is the correct choice the instant your question is really about the left-hand table's complete membership ("show me all my holdings," "which companies have never filed") rather than only the rows that happen to have a match, and it is exactly this module's exercise CS2.03-e04 (find companies with zero filings, using LEFT JOIN ... WHERE f.filing_id IS NULL) and Worked Example 1, below. Choosing INNER when you meant LEFT is a silent bug, nothing crashes, nothing warns you, rows just quietly disappear from a report, which is precisely why it is item 1 in this module's Common Mistakes.

(SQLite also supports RIGHT JOIN (mirror image of LEFT, keep every row of the right-hand table) and FULL OUTER JOIN, keep every row from both sides; this module's exercises stick to INNER/LEFT because reordering which table you write first makes a RIGHT JOIN avoidable in every case you'll meet here, but recognize both if you see them in the wild.) CS2.03-e02 is exactly the first query above: an INNER JOIN filtered to a single date.

5. The order SQL actually runs in (and why HAVING exists)

You write a query top to bottom (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY) but the engine does not execute it in that order. The actual logical execution order is:

FROM / JOIN  →  WHERE  →  GROUP BY  →  HAVING  →  SELECT  →  ORDER BY  →  LIMIT

This single fact resolves more "why won't SQL let me do this" confusion than any other rule in the language, so hold it precisely. FROM/JOIN assembles the rows you're working with; WHERE filters individual rows, before any grouping has happened, at that stage, groups and aggregates don't exist yet, so WHERE can never reference an aggregate function (WHERE COUNT(*) > 1 is simply invalid: COUNT(*) has nothing to count against yet). GROUP BY then collapses the surviving rows into groups; HAVING filters those groups, after aggregation has happened, which is exactly why HAVING SUM(quantity * price) > 200000 is legal while the equivalent WHERE is not. Only after both filtering stages does SELECT compute the actual output columns (including any aliases you gave them, which is also why you generally cannot reference a SELECT-list alias inside the same query's WHERE, though ORDER BY and HAVING are more forgiving about it in SQLite specifically). ORDER BY sorts the final rows, and LIMIT trims the sorted result last of all.

One more consequence of this order matters for §7: window functions are computed at roughly the SELECT stage: after WHERE has already run. This is exactly why you cannot write SELECT ..., ROW_NUMBER() OVER (...) AS rn FROM ... WHERE rn = 1, rn does not exist yet when WHERE is evaluated. The fix, covered fully in §7, is to compute the window function in a WITH clause (§8) and filter on it in an outer query, where it is now an ordinary column.

6. Aggregation: GROUP BY and the five workhorse functions

GROUP BY collapses many rows into one summary row per distinct value (or combination of values) you group by, and the five aggregate functions are what you compute per group: COUNT(*) (how many rows), SUM(x) (total), AVG(x) (mean), MIN(x), MAX(x). SELECT country, COUNT(*) FROM companies GROUP BY country returns exactly one row per distinct country value, each paired with how many companies share it, this module's reference roster gives IN, 4 and US, 4.

Combine GROUP BY with a JOIN and HAVING for real analyst questions:

SELECT c.ticker, ROUND(SUM(t.quantity * t.price), 2) AS gross_buy_value
FROM transactions t
JOIN companies c ON c.company_id = t.company_id
WHERE t.side = 'BUY'
GROUP BY c.ticker
HAVING SUM(t.quantity * t.price) > 200000
ORDER BY gross_buy_value DESC;

Per §5's execution order: WHERE t.side = 'BUY' throws out SELL rows before any grouping happens (so they never enter the sum at all); GROUP BY c.ticker then collapses the remaining rows to one per company; HAVING keeps only the companies whose group total clears the threshold; ORDER BY sorts what's left. This module's exercise CS2.03-e03 is exactly this query. One sharp trap worth naming here, because SQLite is unusually permissive about it: standard SQL (and engines like PostgreSQL) will reject a SELECT column that is neither aggregated nor listed in GROUP BY. SQLite instead silently allows it and picks an arbitrary row's value from within the group, with no guarantee of which one. SELECT grp, tag, SUM(val) FROM t GROUP BY grp runs without complaint in SQLite and hands back some row's tag for each group, useful to know so you never rely on it, and Common Mistakes item 4 drills exactly this.

7. Window functions: a value per row, not one row per group

GROUP BY collapses rows, you lose the individual rows and keep only the summary. A window function does the opposite: it keeps every row and attaches a computed value to each one, based on a "window" of related rows you define with OVER (...). This is the tool the instant your question is "rank/latest/running-total, but still show me every row" rather than "give me one row per group."

ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY trade_date DESC) numbers each company's price rows 1, 2, 3, ..., restarting at 1 for every distinct company_id (that's what PARTITION BY does, it is GROUP BY's cousin, except nothing collapses), ordered so the most recent date gets rank 1. To get "each company's single latest close," per §5 you cannot filter on rn in the same SELECT's WHERE, window functions are computed after WHERE runs, so you compute it in a WITH clause and filter in the outer query (full pattern in §8):

WITH ranked AS (
  SELECT c.ticker, p.trade_date, p.close,
         ROW_NUMBER() OVER (PARTITION BY p.company_id ORDER BY p.trade_date DESC) AS rn
  FROM prices p
  JOIN companies c ON c.company_id = p.company_id
)
SELECT ticker, trade_date, close FROM ranked WHERE rn = 1 ORDER BY ticker;

This is CS2.03-e05. RANK() and DENSE_RANK() are close cousins of ROW_NUMBER() that matter the moment ties are possible: if three rows tie for the top value, RANK() gives all three rank 1 and the next distinct row rank 4 (leaving a gap for the tied rows it skipped past), DENSE_RANK() gives all three rank 1 and the next distinct row rank 2 (no gap), while ROW_NUMBER() arbitrarily breaks the tie and assigns 1, 2, 3 to the tied rows in whatever order the window's ORDER BY leaves them (add a tiebreaker column to ORDER BY: e.g., ticker after close DESC, the moment ties matter to your result). LAG(x)/LEAD(x) fetch the previous/next row's value within the same partition, LAG(close) OVER (ORDER BY trade_date) puts yesterday's close on today's row, the standard building block for a day-over-day change column.

A frame clause narrows the window to specific neighboring rows rather than the whole partition: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means "this row plus the two immediately before it," the exact building block of a trailing moving average:

SELECT c.ticker, p.trade_date, p.close,
       ROUND(AVG(p.close) OVER (
         PARTITION BY p.company_id ORDER BY p.trade_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS moving_avg_3d
FROM prices p JOIN companies c ON c.company_id = p.company_id
ORDER BY c.ticker, p.trade_date;

This is CS2.03-e06. Near the start of each company's history, fewer than 2 prior rows exist. SQLite simply averages over however many rows the frame actually has available (1, then 2, then 3 from the third row onward), never errors and never returns NULL for those early rows, which is worth confirming by tracing a short example by hand (Practice P9 does exactly this).

8. CTEs: the WITH clause, and structuring a multi-step query

A Common Table Expression (CTE), written WITH name AS (SELECT ...), gives a subquery a name you can reference later in the same statement, exactly as if it were a temporary table that exists only for the duration of this one query. Two reasons to reach for one: readability (a multi-step calculation reads top to bottom as named stages instead of nested parentheses) and (the pattern you've now seen twice) filtering on a window function's result, which is otherwise impossible in the same SELECT per §5's execution order.

You can chain more than one CTE, each able to reference the ones defined before it:

WITH co_avg AS (
  SELECT company_id, AVG(price) AS avg_buy_price
  FROM transactions WHERE side = 'BUY' GROUP BY company_id
),
overall AS (
  SELECT AVG(price) AS overall_avg_buy_price
  FROM transactions WHERE side = 'BUY'
)
SELECT c.ticker, ROUND(co_avg.avg_buy_price, 2) AS avg_buy_price
FROM co_avg
JOIN companies c ON c.company_id = co_avg.company_id
CROSS JOIN overall
WHERE co_avg.avg_buy_price > overall.overall_avg_buy_price
ORDER BY avg_buy_price DESC;

co_avg computes each company's own average BUY price; overall computes a single number, the average across every BUY transaction, from every company, with no GROUP BY at all (a bare aggregate query returns exactly one row). CROSS JOIN overall attaches that one number onto every row of co_avg so each company's own average can be compared against it in the WHERE clause, this is CS2.03-e08, this module's capstone exercise, and note the strict >: a company whose average exactly ties the overall mean must not appear, which is exactly the kind of off-by-a-comparison-operator bug (> vs. >=) a hidden test with an engineered tie is built to catch (Common Mistakes and the code pack both drill this precisely). A CTE built purely for pivoting is CS2.03-e07's core move, turning one row-per-fiscal-year into one row per company with a column per year:

WITH fy AS (
  SELECT company_id,
         SUM(CASE WHEN fiscal_year = 2024 THEN revenue END) AS rev_2024,
         SUM(CASE WHEN fiscal_year = 2025 THEN revenue END) AS rev_2025
  FROM filings WHERE period = 'FY' GROUP BY company_id
)
SELECT c.ticker, fy.rev_2024, fy.rev_2025,
       ROUND((fy.rev_2025 - fy.rev_2024) * 100.0 / fy.rev_2024, 2) AS growth_pct
FROM fy JOIN companies c ON c.company_id = fy.company_id
WHERE fy.rev_2024 IS NOT NULL AND fy.rev_2025 IS NOT NULL
ORDER BY growth_pct DESC;

CASE WHEN fiscal_year = 2024 THEN revenue END evaluates to revenue on a matching row and to NULL on every other row; wrapping it in SUM(...) inside a GROUP BY company_id collapses each company's several filings rows into one, with SUM quietly ignoring the NULLs and keeping only the one real value per year, the standard SQL "pivot" idiom, worth recognizing on sight since you will see it constantly in real reporting code. The final WHERE fy.rev_2024 IS NOT NULL AND fy.rev_2025 IS NOT NULL matters precisely because a company with only one of the two years on file would otherwise divide a real number by NULL (or NULL by a real number), and any arithmetic involving NULL in SQL produces NULL, not an error and not zero, silently dropping that company from the output is correct here (you cannot compute a two-year growth rate from one year of data), but it must be done deliberately, not left to chance.

{
  "skill": "Drafting a JOIN/GROUP BY/CTE query with an AI assistant, without trusting a hallucinated schema",
  "use": "Once you can state the question precisely in your own words against a schema you already understand (which tables, which join key, which filter, which aggregate), use an AI coding assistant to draft the SQL — then treat the draft exactly like a plausible-sounding answer from any other source: unverified until you check it against the real schema and the real data.",
  "tools": ["AI coding assistants — Claude, ChatGPT, GitHub Copilot, Cursor", "This module's code pack's own visible tests, as your first line of defense", "The bundled schema reference in this module (§2) as your ground truth"],
  "prompt": "Given this exact schema: companies(company_id, ticker, name, country, sector, exchange, currency), prices(price_id, company_id, trade_date, open, high, low, close, volume), filings(filing_id, company_id, fiscal_year, period, filing_type, revenue, net_income, total_assets, total_liabilities, filed_date), transactions(transaction_id, account_id, company_id, txn_date, side, quantity, price, fees) -- write a single SQL query that <YOUR PRECISE QUESTION>. Use only the columns listed above; do not invent a column or table that isn't in this list.",
  "verify": "Check every column and table name the assistant used against §2's schema block, by eye, before running anything -- a model can confidently reference a plausible-sounding column (e.g. companies.market_cap) that simply does not exist in this schema, and SQLite's error for that is sometimes just as terse as a typo's. Then actually RUN the query against this module's code pack visible tests, and separately hand-trace the expected answer on a tiny example yourself (as every Worked Example in this module does) before trusting the result on data you haven't checked by hand.",
  "diy": "You must already be able to state which tables, which join key, which filter, and which aggregate function the question needs, in your own words, before asking a copilot to draft the SQL -- that is exactly §4-§6 of this section. If you cannot describe the query's shape yourself, you are not ready to accelerate yet.",
  "market": "US"
}

9. Normalization: why the schema looks the way it does (1NF, 2NF, 3NF)

Normalization is the formal name for the "store each fact once" discipline from §1, expressed as a sequence of increasingly strict rules, each one fixing a specific kind of duplication risk. You do not need to memorize a textbook proof; you need to be able to look at a table and say which rule it violates, and why that violation is dangerous.

First normal form (1NF): every value is atomic, no repeating groups. A transactions row with a column literally named buy_dates holding the text "2026-01-05,2026-01-07,2026-02-02" violates 1NF, you cannot filter, sort, or join on an individual date buried inside a comma-joined string without first parsing it back apart in application code, throwing away everything a real database engine is good at. This module's schema is 1NF throughout: transactions.txn_date holds exactly one date per row; a company's three purchase dates are three separate rows, not one row with three dates crammed in.

Second normal form (2NF): no column depends on only part of a composite key. This rule only bites when a table's primary key is more than one column. Imagine a hypothetical transaction_items(transaction_id, company_id, ticker, quantity) table keyed on (transaction_id, company_id) together, ticker depends only on company_id (the other half of the key), not on the pair, so storing it here duplicates it across every transaction for the same company and risks it drifting out of sync with the real companies.ticker. The fix is exactly this module's schema: ticker lives once, in companies, and transactions stores only company_id.

Third normal form (3NF): no non-key column depends on another non-key column instead of the key itself. If filings carried a sector column copied from the company's sector, sector would depend on company_id (itself a non-key column of filings, since filings' own key is filing_id) rather than on filing_id directly, a transitive dependency. Change a company's sector classification and you would need to find and update every historical filing row for that company, or accept that your data is now silently inconsistent. This module's four tables are, by design, in 3NF: every non-key column in prices, filings, and transactions depends only on that table's own primary key (and, through company_id, on the correct company), never on some other non-key column that happens to sit in the same row.

Normalization is not free, though, and knowing the trade-off is as important as knowing the rule. A fully normalized schema means a report needs a JOIN every time it wants a human-readable name instead of a bare company_id, for a handful of joins on modest data, this costs nothing worth worrying about; at genuinely large scale, a team will sometimes deliberately denormalize a reporting copy of the data, for instance, a flattened table that pre-joins ticker directly onto every prices row, trading a small, controlled duplication risk (now confined to one clearly-labeled reporting table, refreshed on a schedule) for faster reads with zero joins at query time. The rule of thumb: normalize the system of record (where facts are written and must never drift), and denormalize only in a clearly-labeled, regularly-rebuilt reporting copy, never the other way around. CS2.04 picks this exact trade-off back up at real scale, with columnar storage and a small analyst warehouse.

10. Indexes: what they are, when they help, and how to check

An index is a separate, sorted lookup structure the database engine maintains alongside a table, so that finding rows matching a condition doesn't require checking every single row in order (a full table scan). Picture the index at the back of a textbook: instead of reading every page to find "duration," you jump straight to the indexed page. CREATE INDEX idx_prices_trade_date ON prices(trade_date); builds exactly this kind of structure over the trade_date column.

You can ask the engine what it actually did, with EXPLAIN QUERY PLAN, and the difference is unambiguous. On a prices table with no index over trade_date:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM prices WHERE trade_date = '2026-01-06';
QUERY PLAN
`--SCAN prices

SCAN prices means exactly what it says: every row of prices was checked, one at a time. After CREATE INDEX idx_prices_trade_date ON prices(trade_date); and re-running the identical query:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM prices WHERE trade_date = '2026-01-06';
QUERY PLAN
`--SEARCH prices USING INDEX idx_prices_trade_date (trade_date=?)

SEARCH ... USING INDEX means the engine jumped straight to the matching rows via the sorted index instead of checking every row. On a table with a handful of rows the difference is invisible to a human; on a table with millions, SCAN vs. SEARCH is the difference between a query that returns instantly and one that visibly grinds.

Indexes are not a free "always add more" lever, for two reasons worth holding onto. First, an index must be updated every time you insert, update, or delete a row in the indexed table, so an index that never gets used by a read still costs write-time overhead forever; index the columns you actually filter, join, or sort on, not every column defensively. Second, an index only helps when the column is selective, narrows the search down a lot, and the table is large enough for a scan to matter; indexing companies.sector on a 50-row companies table buys essentially nothing, because scanning 50 rows is already instant, while the same index on prices.company_id (potentially millions of rows) is exactly where the payoff lives. One more fact worth knowing before you go adding indexes by hand: a UNIQUE constraint or a PRIMARY KEY, like this module's UNIQUE (company_id, trade_date) on prices, automatically creates its own index to enforce that uniqueness; hand-creating a second index over the identical columns duplicates work the engine is already doing for you.

11. ACID: why a financial system won't let you skip this

transactions is exactly the kind of table where the four ACID properties stop being an abstract computer-science acronym and start being the difference between a system you can trust with money and one you cannot.

Atomicity: a group of writes either all happen or none of them do; there is no observable halfway state. Booking a trade often means two writes at once, insert the transactions row and update a cash or position balance elsewhere: wrapped together as BEGIN; ...; COMMIT;. If the process crashes after the first write but before the second, atomicity guarantees the whole transaction rolls back on restart, so the trade record never exists without its matching balance update, or vice versa; you verified exactly this rollback behavior: BEGIN; INSERT ...; ROLLBACK; leaves the table exactly as it was before, as if the INSERT never ran, earlier in this module's hands-on exploration. Consistency, every write leaves the database obeying its declared rules; this module's CHECK (side IN ('BUY','SELL')) and CHECK (country IN ('IN','US')) constraints are consistency guarantees enforced by the engine itself, not by hoping every application remembers to validate input correctly. Isolation, concurrent operations don't see each other's half-finished work; while one process is midway through inserting ten new transactions rows inside a single transaction, a reporting query running at the same moment should see either all ten or none of them, never three, never a dirty read of a write that might still be rolled back. Durability, once a COMMIT returns successfully, that write survives a crash or power loss immediately afterward; the engine has physically written it to durable storage (via a write-ahead log), not left it sitting only in memory where a crash would erase it.

Skip any one of these four and a financial record-keeping system develops a specific, predictable failure: skip atomicity and a crash mid-transfer can silently debit one account without crediting the other; skip consistency and a stray typo (side = 'B' instead of 'BUY') can silently corrupt downstream reports that assume only two valid values exist; skip isolation and a report generated during a busy write window can show numbers that never actually existed as a real, complete state; skip durability and a "successful" trade can simply vanish the moment the machine loses power. This is precisely why every production financial system is built on a database engine that gives you all four by construction, and why "just write to a plain text file": tempting as a CS1.01-level shortcut, is never the right answer once real money is on the line.

12. Where this leaves you: and the code pack

You can now read a normalized schema and say what each key relates to what; filter, sort, and page a result with SELECT/WHERE/ORDER BY/LIMIT; combine tables correctly with INNER/LEFT JOIN, naming precisely what each does to an unmatched row; state SQL's real execution order and use it to explain WHERE vs. HAVING; aggregate with GROUP BY and the five workhorse functions; reach for a window function the moment a question needs "per row, not per group"; structure a multi-step question into named CTEs, including the "filter on a window function in an outer query" pattern; recognize whether a schema is normalized or deliberately flattened, and why; read an EXPLAIN QUERY PLAN to tell SCAN from SEARCH; and state exactly what each ACID letter buys a system that records money moving.

{
  "skill": "Reviewing an AI-drafted query for the specific failure modes that pass the happy path silently",
  "use": "Once your own query is working on the visible example, ask an AI assistant to review it specifically for the failure modes this module names -- the wrong join type, a WHERE that should be a HAVING, a missing tie-breaker in ORDER BY, a strict > that should arguably be >= (or shouldn't) -- as a structured second pair of eyes, not as the author of the query.",
  "tools": ["AI coding assistants -- Claude, ChatGPT, GitHub Copilot", "This module's code pack's hidden tests, as the actual arbiter"],
  "prompt": "Here is my SQL query and the schema it runs against: <YOUR QUERY + §2's schema>. Specifically check: (1) is INNER JOIN dropping rows I actually want kept -- should any of these be a LEFT JOIN? (2) is there a WHERE clause trying to filter on an aggregate that should be a HAVING instead? (3) does every multi-row result have an explicit, fully-deterministic ORDER BY (a tiebreaker column if ties are possible)? (4) are any comparison operators at a boundary (>, >=) doing what I actually intend? Do not just say it looks fine -- check each of the four explicitly.",
  "verify": "Run the reviewed query against this module's code pack -- both the visible tests AND, if you're checking your own extension of an exercise, a hidden-test-style variant with different data than what you eyeballed. An AI review can miss the exact same class of bug it's being asked to check for; the tests, not the review, are the actual verification (Guardrail-style: verify, then trust).",
  "diy": "You must already be able to name, yourself, what INNER vs LEFT JOIN does to an unmatched row and why WHERE can't filter an aggregate (§4-§5) before a review checklist like this means anything to you -- otherwise you cannot tell a correct 'looks fine' from an incorrect one.",
  "market": "IN"
}

Every one of those ideas is a real, individually graded exercise in this module's code pack (pack id CS2.03, 8 exercises, sql-result grading against the bundled finance-db.sqlite):

Open the pack, write each query exactly as you reasoned it out above, run it against the bundled database, and watch real SQL (not a trace on paper) confirm you got it right. Every exercise ships hidden tests built from both an India-flavored and a US-flavored slice of data (tagged accordingly in the pack), specifically so a query that only works on the one example you tested by hand gets caught immediately, the same anti-hardcoding discipline every code pack in this program enforces.

Common mistakes & how experts think differently

1. Reaching for INNER JOIN by default. INNER JOIN silently drops any row on either side with no match, no error, no warning, just fewer rows in your output than you expected. The expert's first question before writing any join is "do I need every row from one side regardless of a match?", if yes, that side needs LEFT JOIN, decided before writing the query, not discovered by noticing a report is short a few rows.
2. Trusting row order without an explicit ORDER BY. A result set's order is unspecified unless you sort it, code that "happens to look sorted" during testing can silently return a different order after a data change, a new index, or an engine upgrade. The expert habit: any query whose result order matters to a human or to downstream code carries an explicit ORDER BY, always, with a tiebreaker column whenever ties are possible.
3. Trying to filter on an aggregate with WHERE. WHERE SUM(x) > 100 is invalid, because per §5 WHERE runs before grouping and aggregation exist, the fix is always HAVING, which runs after. The expert reflex: the moment a condition mentions COUNT, SUM, AVG, MIN, or MAX, it belongs in HAVING, never WHERE.
4. Trusting SQLite's lenient GROUP BY. Standard SQL requires every non-aggregated SELECT column to appear in GROUP BY; SQLite instead silently allows a "loose" column and hands back an arbitrary row's value for it, with no guarantee which. The expert habit: every column you SELECT alongside a GROUP BY is either itself a grouping column or wrapped in an aggregate function, never both un-grouped and un-aggregated, regardless of what SQLite happens to tolerate.
5. The = NULL trap, and forgetting NULL propagates through arithmetic. WHERE x = NULL matches zero rows, always: NULL represents "unknown," and nothing, not even another NULL, is considered equal to an unknown; the correct test is IS NULL (or IS NOT NULL). The same silent propagation bites arithmetic: any expression involving a NULL operand evaluates to NULL, not zero and not an error, which is exactly why CS2.03-e07's growth calculation explicitly excludes rows missing either year rather than letting a NULL silently vanish into the output.
6. Expecting a window function to reduce the row count. A GROUP BY query returns fewer rows than it started with; a window function (OVER (...)) returns the same number of rows it started with, each one carrying a new computed column. Expecting ROW_NUMBER() OVER (...) to behave like a GROUP BY, and being surprised the result still has every row, is a sign the GROUP BY/window distinction from §7 hasn't fully landed yet; re-read it before writing another window-function query.
7. Comparing or summing raw monetary figures across two currencies. ORDER BY total_value DESC across a mix of ₹ and $ rows silently ranks numbers that are not measuring the same thing, a ₹500,000 total and a $500,000 total are wildly different in real economic size, and SQL will happily sort them as if they were comparable, because it has no concept of currency at all; that concept lives entirely in your head and your schema design. The expert habit: filter to a single currency (or convert to one first) before ranking, summing, or comparing any monetary total across a mixed-currency table, precisely the caveat CS2.03-e03's visible test calls out explicitly.
8. "More indexes always make queries faster." Every index speeds up the specific reads it serves and slows down every write to that table (the index itself must be maintained on every insert/update/delete), and an index on a small table or a low-selectivity column can cost more to maintain than it will ever save on reads. The expert habit: index the large tables' selective, frequently-filtered-or-joined columns deliberately, verify the benefit with EXPLAIN QUERY PLAN (SCAN → SEARCH), and don't index defensively "just in case."
9. Writing a multi-statement financial update with no explicit transaction. Two related writes issued as separate, unwrapped statements can be interrupted between them by a crash, leaving the database in a state neither write nor the world ever intended (money debited with no matching credit). The expert habit for any related group of writes: wrap them in BEGIN ... COMMIT, so atomicity guarantees they land together or not at all, exactly as demonstrated in §11.

Worked examples

A note on the data: every company name, ticker, price, filing figure, and transaction figure in every worked example below is entirely fictional and synthetic (seeded, illustrative (R3)) generated for this module, run against the actual bundled schema from §2, and cross-checked against real SQLite output before publication. None of it is a real historical quote for any real company; nothing here should ever be cited as a real-world fact.

Worked example 1: The INNER JOIN that silently dropped two holdings (India, ₹)

Meera maintains a five-company India-focused watchlist and wants "each holding's closing price on 2026-06-15" for a morning note. She writes an INNER JOIN between companies and prices, filtered to that date, and gets back three rows. She pastes them into the note without a second thought, three holdings, three prices, looks complete.

Solution. Step 1: name what actually happened. The watchlist has five companies: NILAM, KAVERI, SAGAR, VASANT, and PIPPAL. On 2026-06-15, only NILAM, KAVERI, and SAGAR have a row in prices at all: VASANT and PIPPAL simply never traded that day (a listing gap, a trading halt, illustrative reasons only). INNER JOIN keeps only rows where both sides match, so a companies row with zero matching prices rows for that date vanishes from the output entirely, not marked missing, not flagged, just absent.

Step 2, see the two queries side by side.

-- INNER JOIN: 3 rows -- VASANT and PIPPAL are gone, with no signal that anything is missing
SELECT c.ticker, p.close FROM companies c
JOIN prices p ON p.company_id = c.company_id AND p.trade_date = '2026-06-15'
ORDER BY c.ticker;
-- KAVERI 648.0 | NILAM 1310.0 | SAGAR 263.0

-- LEFT JOIN: all 5 rows -- VASANT and PIPPAL appear with close = NULL, an honest signal
SELECT c.ticker, p.close FROM companies c
LEFT JOIN prices p ON p.company_id = c.company_id AND p.trade_date = '2026-06-15'
ORDER BY c.ticker;
-- KAVERI 648.0 | NILAM 1310.0 | PIPPAL NULL | SAGAR 263.0 | VASANT NULL

Step 3, decide which is right for the actual question. Meera's real question was "each holding's price," not "only the holdings that happen to have traded", the moment the question is about complete membership of the left-hand table (every holding, every company, every account), LEFT JOIN is correct, and the NULL rows are not noise; they are the honest, correct answer to "did this holding have a price today," which is itself a fact worth putting in the note ("VASANT and PIPPAL did not trade today").

Read it. Nothing here raised an error. A note that silently reported "3 holdings, all priced" instead of "5 holdings, 2 of which didn't trade" is not technically false in what it shows, it is dangerously incomplete in what it omits, and the omission is invisible unless you already know to count. This is precisely why Common Mistakes item 1 puts "reaching for INNER JOIN by default" first: it is the single most common way a correct-looking query produces an incomplete report.

Worked example 2: Ranking each sector's largest position with a window function (US, $)

An analyst wants, within each sector, which company has traded the most total BUY-side quantity, "the biggest mover per sector," not just the single biggest mover overall.

Solution. Step 1, the aggregate part first. SUM(quantity) per company, grouped, gives each company's total BUY quantity: BRPEAK (IT Services) totals 7,000 shares (5,000 + 2,000 across two buys), HALIFX (IT Services) totals 4,000; CRSTNE (Banking) totals 3,000; PEBBLE (Banking) totals 6,000.

Step 2, rank within sector, not across the whole table. A plain ORDER BY total_qty DESC would rank all four together (BRPEAK, PEBBLE, HALIFX, CRSTNE), useful, but not "the leader per sector." RANK() OVER (PARTITION BY sector ORDER BY total_qty DESC) restarts the ranking at 1 for every distinct sector:

WITH totals AS (
  SELECT c.ticker, c.sector, SUM(t.quantity) AS total_qty
  FROM transactions t JOIN companies c ON c.company_id = t.company_id
  WHERE t.side = 'BUY'
  GROUP BY c.ticker, c.sector
)
SELECT sector, ticker, total_qty,
       RANK() OVER (PARTITION BY sector ORDER BY total_qty DESC) AS sector_rank
FROM totals ORDER BY sector, sector_rank;

Step 3, read the result. Banking: PEBBLE (6,000, rank 1), CRSTNE (3,000, rank 2). IT Services: BRPEAK (7,000, rank 1), HALIFX (4,000, rank 2), each sector's ranking restarts independently, which is exactly PARTITION BY's job (§7): it is GROUP BY's "restart per group" behavior, borrowed for a window function that still returns every row.

Read it. Notice the query needed a GROUP BY (to get each company's total quantity) and a window function (to rank those totals within sector), these are not competing tools, they are two different jobs performed in sequence via a CTE: collapse first (aggregate), then attach a per-row rank to what's left (window). Confusing "I need a summary number per company" with "I need a rank per company within its group" is exactly what leads to reaching for the wrong one alone.

Worked example 3: GROUP BY vs. HAVING: total trade activity per account (India, ₹)

A compliance analyst wants to flag any trading account whose total activity (both buys and sells, unsigned) across the book exceeds ₹500,000, to prioritize which accounts get reviewed first.

Solution. Step 1, the naive-but-wrong instinct. A learner's first draft is often SELECT account_id, SUM(quantity*price) FROM transactions WHERE SUM(quantity*price) > 500000 GROUP BY account_id, and SQLite rejects it, because per §5, WHERE runs before GROUP BY/aggregation exists; there is no SUM yet for WHERE to compare against.

Step 2, the fix, and the data. Move the condition to HAVING, which runs after aggregation:

SELECT account_id, ROUND(SUM(quantity*price),2) AS total_activity
FROM transactions
GROUP BY account_id
HAVING SUM(quantity*price) > 500000
ORDER BY total_activity DESC;

Three accounts appear in the raw data: account 701 (a ₹240,000 BUY and a ₹125,000 SELL, totaling ₹365,000), account 702 (a ₹366,000 BUY in KAVERI plus a further ₹184,500 BUY in NILAM, totaling ₹550,500), and account 703 (a single ₹48,000 BUY).

Step 3, read the filtered result. Only account 702 clears the ₹500,000 bar and appears in the output: (702, 550500.0). Accounts 701 (₹365,000) and 703 (₹48,000) are correctly excluded, not because anything about them is wrong, but because HAVING is doing exactly its job of filtering groups by their aggregated total, after the totals have actually been computed.

Read it. The bug in Step 1 is not a typo to fix and forget, it is direct evidence of the execution-order model in §5. Anyone who has genuinely internalized "aggregates don't exist until after GROUP BY has run" will never write that WHERE in the first place; anyone who hasn't will keep tripping on it in every new query shape until the model, not the specific fix, is what sticks.

Worked example 4: A three-stage CTE for quarter-over-quarter margin change (US, $)

A desk wants each company's net margin (net income ÷ revenue) for Q1 and Q2 of the same fiscal year, and the change between them in percentage points, a genuinely multi-step calculation, a natural CTE chain.

Solution. Step 1, pivot the two quarters (first CTE). Exactly like CS2.03-e07's year pivot, but for quarters: SUM(CASE WHEN period='Q1' THEN net_income END) and its revenue/Q2 siblings, grouped by company_id, give one row per company with four raw figures: ni_q1, rev_q1, ni_q2, rev_q2.

Step 2, compute each quarter's margin (second CTE, built on the first). ROUND(100.0 * ni_q1 / rev_q1, 2) and the Q2 equivalent turn the four raw figures into two clean percentages, margin_q1 and margin_q2, deliberately kept as its own named stage rather than folded into the final SELECT, purely for readability: anyone reading the query top to bottom sees "pivot the raw numbers," then "turn them into margins," then "compute the change," as three legible stages instead of one dense expression.

Step 3, the final comparison.

WITH q AS ( /* Step 1's pivot */ ... ),
     m AS ( /* Step 2's margins */ ... )
SELECT c.ticker, m.margin_q1, m.margin_q2, ROUND(m.margin_q2 - m.margin_q1, 2) AS margin_change_pp
FROM m JOIN companies c ON c.company_id = m.company_id
ORDER BY margin_change_pp DESC;

For BRPEAK: Q1 margin = 18,000 / 120,000 × 100 = 15.00%; Q2 margin = 14,300 / 130,000 × 100 = 11.00%; change = −4.00 percentage points, margin compressed even though revenue grew, because net income fell faster. For CRSTNE: Q1 = 19,000 / 95,000 × 100 = 20.00%; Q2 = 21,210 / 101,000 × 100 = 21.00%; change = +1.00 percentage point. Ordered by change descending: CRSTNE (+1.00) ahead of BRPEAK (−4.00).

Read it. Notice the phrase "percentage points," used deliberately instead of "percent," throughout Step 3, a margin moving from 15.00% to 11.00% is a change of −4 percentage points, not "−4%" (which would ambiguously suggest either −4pp or a −26.7% relative decline in the margin itself, 4/15). This distinction: points vs. percent, absolute vs. relative change, is a real, common source of miscommunicated analyst work, and naming it precisely every time you compute a change in an already-percentage quantity is the expert habit worth building here.

Worked example 5: Normalization vs. a flattened reporting table: the actual trade-off

A newer analyst proposes: "queries would be so much simpler if prices just had a ticker column directly, then I wouldn't need to join to companies every time." Is this a good idea?

Solution. Step 1: name what would actually happen. Adding ticker directly to prices means the same ticker string is now copied onto every one of that company's price rows: potentially thousands of them. This is precisely the 2NF/3NF violation from §9: ticker depends on company_id, not on prices' own key (price_id), so storing it here is a transitive dependency waiting to drift.

Step 2: trace the failure mode precisely. Suppose a company undergoes a ticker change (a real, if infrequent, corporate event). In the normalized schema, you update exactly one row: companies.ticker, and every join automatically reflects the new ticker everywhere, instantly and correctly. In the flattened version, you must find and update every historical prices row for that company, and if even one is missed (a batch job that ran partway, a manual UPDATE with an off-by-one WHERE clause), your data is now silently inconsistent, some prices rows show the old ticker, some show the new one, and nothing about the schema itself will ever tell you that happened.

Step 3, grant the real motivation, and give it its proper home. The proposal isn't wrong to want fewer joins, a large reporting query with several joins genuinely costs more to execute than a query against one flat table, at real scale. The correct answer is not "never denormalize," it is "denormalize in a clearly-labeled, regularly-rebuilt reporting copy, never in the system of record": build a separate price_report table that pre-joins ticker in, refresh it on a schedule (nightly, hourly, whatever the reporting need requires), and treat every row in it as disposable and regenerable from the real, normalized prices/companies tables at any time. The duplication risk is now confined to one table everyone knows is a derived copy, not smeared across the system that other processes trust as ground truth.

Read it. "Should I normalize or denormalize" is very rarely the right question, the right question is "which one, for this specific table: a system of record that must never silently drift, or a disposable, regularly-rebuilt reporting copy built for speed." CS2.04 returns to exactly this trade-off at real production scale.

Worked example 6: Reading an EXPLAIN QUERY PLAN: does this filter use an index? (US, $)

A quant developer notices a report that filters prices by trade_date alone feels slow once the table grows large, and wants to know, precisely, whether the engine is scanning every row or using an index, before deciding whether adding one is even worth doing.

Solution. Step 1, ask the engine directly, before the index exists.

sqlite> EXPLAIN QUERY PLAN SELECT * FROM prices WHERE trade_date = '2026-01-06';
QUERY PLAN
`--SCAN prices

SCAN prices confirms it: every row of prices is being checked, one at a time, to find the ones matching trade_date. On a small table this costs nothing noticeable; on a multi-million-row table, it is exactly the kind of report that "feels slow."

Step 2, add the index, and ask again.

CREATE INDEX idx_prices_trade_date ON prices(trade_date);

sqlite> EXPLAIN QUERY PLAN SELECT * FROM prices WHERE trade_date = '2026-01-06';
QUERY PLAN
`--SEARCH prices USING INDEX idx_prices_trade_date (trade_date=?)

The identical query now reads SEARCH ... USING INDEX, the engine jumped straight to matching rows via the sorted index instead of checking every row.

Step 3, a subtlety worth naming, because it is easy to get backwards. This module's prices table already carries UNIQUE (company_id, trade_date), and a UNIQUE constraint automatically creates its own index (§10), but that automatic index is built for looking things up by company_id first, then trade_date; it does not help a query that filters on trade_date alone, with no company_id in the WHERE clause at all, which is exactly why Step 1's EXPLAIN QUERY PLAN still showed a full SCAN despite that other index already existing. An index only serves a query whose filter actually matches the index's own leading column(s), a fact worth confirming with EXPLAIN QUERY PLAN rather than assuming from "well, some index exists on this table."

Read it. The entire decision ("is this worth an index") was answered with evidence, not a guess: check the plan before, add the index, check the plan after, and confirm SCAN genuinely became SEARCH for the specific query you care about. Adding an index and simply hoping it helped, without checking, is exactly the "more indexes always faster" mistake from Common Mistakes item 8, and, as Step 3 shows, an index that exists on a table doesn't automatically mean it serves any particular query against that table.

Worked example 7: ACID in practice: what a crash reveals about atomicity (India, ₹)

A back-office system needs to book a trade: insert a new transactions row and, in the same breath, update a separate running-balance figure it maintains elsewhere. What actually protects the firm if the process crashes at the worst possible moment, after the first write, before the second?

Solution. Step 1, state the danger precisely, before naming the fix. Without any explicit transaction, the two writes are two independent, unrelated events as far as the database is concerned. A crash between them leaves exactly one of the two committed: the transactions row exists, but the balance was never updated to reflect it (or the reverse), and nothing about the database itself flags this as broken, a report generated the next morning would silently work from an inconsistent state, showing a trade that isn't reflected in the balance it should have moved.

Step 2, the fix, and what it actually guarantees. Wrap both writes in one explicit transaction: BEGIN; INSERT INTO transactions (...) VALUES (...); UPDATE balances SET ... ; COMMIT;. Atomicity guarantees that if the process crashes at any point before COMMIT finishes, the database, upon restart, rolls the entire transaction back, as if neither write had ever been attempted. This is not a hopeful design goal; it is a mechanical guarantee, verified directly and simply: BEGIN; INSERT INTO t VALUES (2,'second'); ROLLBACK; leaves a table with only its original row, the attempted insert genuinely never happened, from the database's point of view, the instant ROLLBACK (or an unrecovered crash before COMMIT) occurs.

Step 3, name the other three properties riding along. Consistency is why transactions.side can only ever be 'BUY' or 'SELL', the CHECK constraint from §2 refuses a typo'd third value at the moment of the write, rather than letting it silently corrupt every downstream report that assumes exactly two possibilities. Isolation is why a reporting query running concurrently with this booking transaction sees either the pre-trade state or the fully-committed post-trade state, never a "half-written" row it should never have been able to observe. Durability is why, the instant COMMIT returns successfully, the firm can treat the trade as permanently recorded, safe even against a power failure one second later, because the engine has already written it to durable storage, not merely held it in memory.

Read it. All four letters answer the same underlying question from a different angle: "what, precisely, can I assume is true about my data after something goes wrong at the worst possible moment?" A system that cannot answer that question with a mechanical guarantee (not a hope, not a "we've never seen it fail") has no business recording anyone's money, which is exactly why this module treats ACID as a fact to know cold, not a term to recognize vaguely.

Practice set

Work every problem fully (write your query or trace on paper) before reading its solution. Every problem uses data distinct from the Worked examples, so you cannot simply recall an answer instead of reasoning it out.

P1 (guided). Filter and sort. From companies, write a query returning ticker and currency for every company on the NYSE exchange, ordered by ticker.

Solution.

SELECT ticker, currency FROM companies WHERE exchange = 'NYSE' ORDER BY ticker;

Against this module's reference roster (§2), the NYSE companies are CRSTNE, AMBERF, and HARBRL, ordered alphabetically by ticker: AMBERF, CRSTNE, HARBRL, each with currency = 'USD'.

P2 (guided). ORDER BY + LIMIT. NILAM has exactly two prices rows, on 2026-01-05 (close 1190.00) and 2026-01-06 (close 1200.00). Write a query returning its two most recent rows, most recent first, and state the result.

Solution.

SELECT trade_date, close FROM prices
WHERE company_id = (SELECT company_id FROM companies WHERE ticker = 'NILAM')
ORDER BY trade_date DESC LIMIT 2;

Result: (2026-01-06, 1200.00), (2026-01-05, 1190.00): both existing rows, most-recent-first; with only two rows total, LIMIT 2 returns everything, which is worth noticing precisely (asking for "the top N" never errors if fewer than N rows exist, you simply get what's there).

P3. Operator-precedence bug. A learner writes WHERE country = 'IN' OR country = 'US' AND sector = 'Banking', intending "India or US, but only Banking companies." Explain the bug and rewrite it correctly.

Solution. AND binds tighter than OR, so this parses as country = 'IN' OR (country = 'US' AND sector = 'Banking'), every India-listed company matches regardless of sector, which is not what was intended. The fix adds explicit parentheses around the intended grouping: WHERE (country = 'IN' OR country = 'US') AND sector = 'Banking', now every clause reads exactly as the parentheses group it, with zero ambiguity for the next person (including future-you) reading the query.

P4. INNER vs. LEFT JOIN, counted. Using Worked Example 1's five-holding scenario (three of five have a prices row on the date in question), how many rows does an INNER JOIN return? A LEFT JOIN? Which is correct for "show me every holding's price, or blank if none"?

Solution. INNER JOIN returns 3 rows (only the holdings with a matching price row). LEFT JOIN returns 5 rows (every holding, with NULL filled in for the two missing a price). LEFT JOIN is correct here, the question is explicitly about every holding's status, including the ones with nothing to show, which only LEFT JOIN preserves.

P5. GROUP BY + COUNT. Write a query returning country and the count of companies per country from companies, ordered by country.

Solution.

SELECT country, COUNT(*) AS n FROM companies GROUP BY country ORDER BY country;

Against the reference roster: IN, 4 and US, 4, four companies of each, ordered alphabetically by the (only two-valued) country column.

P6. WHERE vs. HAVING, diagnosed. A learner writes SELECT sector, COUNT(*) FROM companies WHERE COUNT(*) > 1 GROUP BY sector. Explain precisely why this fails, and give the fix.

Solution. Per §5's execution order, WHERE runs before GROUP BY/aggregation, at that stage no groups and no COUNT(*) exist yet for WHERE to compare against, so the engine rejects the query outright (or, in engines that don't reject it, the behavior is undefined/nonsensical). The fix moves the condition to HAVING, which runs after aggregation: SELECT sector, COUNT(*) FROM companies GROUP BY sector HAVING COUNT(*) > 1.

P7. Net margin per company. Using filings, write a query returning each company's ticker and its FY2025 net margin (net_income / revenue, as a percentage rounded to 2 decimals), for period = 'FY' filings only, ordered by ticker.

Solution.

SELECT c.ticker, ROUND(100.0 * f.net_income / f.revenue, 2) AS net_margin_pct
FROM filings f JOIN companies c ON c.company_id = f.company_id
WHERE f.fiscal_year = 2025 AND f.period = 'FY'
ORDER BY c.ticker;

For NILAM (revenue 480,000, net income 62,000): 62,000/480,000 × 100 = 12.92%. For BRPEAK (revenue 510,000, net income 58,000): 58,000/510,000 × 100 = 11.37%. Ordered by ticker: BRPEAK 11.37, NILAM 12.92.

P8. RANK() vs. DENSE_RANK() vs. ROW_NUMBER() on a tie. Three transactions tie for the largest single-trade quantity that day, at 500 shares each; the next-largest distinct trade is 300 shares. Using RANK(), what rank do the three tied rows get, and what rank does the 300-share row get? Contrast with DENSE_RANK() and ROW_NUMBER().

Solution. RANK(): all three tied rows get rank 1; the 300-share row gets rank 4 (the rank "skips" 2 and 3 to account for the three rows that tied for 1st). DENSE_RANK(): all three tied rows still get rank 1, but the 300-share row gets rank 2 (no gap, it counts distinct rank values, not row positions). ROW_NUMBER(): the three tied rows arbitrarily receive 1, 2, 3 in whatever order the window's ORDER BY leaves them (add a tiebreaker column if which one gets "1" actually matters), and the 300-share row gets 4.

P9. Moving average by hand. NILAM's close prices across four consecutive days are 1190.00, 1200.00, 1208.50, 1213.00. Compute the 3-day trailing moving average (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) for the 4th day by hand.

Solution. The 4th day's trailing window is days 2, 3, and 4 (the current row plus the two immediately preceding): (1200.00 + 1208.50 + 1213.00) / 3 = 3621.50 / 3 = 1207.1666..., which rounds to 1207.17, matching exactly what this module's code pack computes and grades for the identical scenario.

P10. Why a CTE, not a subquery, here. SELECT ticker, trade_date, close FROM (SELECT ..., ROW_NUMBER() OVER (...) AS rn FROM ...) WHERE rn = 1 and the CTE version from §7/§8 compute the identical result. Why does this module prefer the CTE form?

Solution. Both are functionally identical, a CTE is, under the hood, a named subquery. The CTE form is preferred purely for readability and structure: WITH ranked AS (...) names the intermediate stage, so a query with several steps (pivot, then rank, then filter, then join) reads top to bottom as a sequence of labeled stages instead of a wall of nested parentheses that must be read from the inside out. For a single-step query the difference is cosmetic; the moment you chain two or more stages (as in Worked Example 4 and CS2.03-e08), the CTE form's readability advantage becomes real.

P11. Filtering on a window function, why it fails, and the fix. Explain, referencing SQL's logical execution order, why SELECT ticker, ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY trade_date DESC) AS rn FROM prices WHERE rn = 1 is invalid, and state the fix.

Solution. Per §5, WHERE is evaluated before the SELECT list is computed, and window functions are computed at roughly the SELECT-list stage, so rn does not exist yet at the point WHERE runs; there is nothing for rn = 1 to check against. The fix: compute the window function inside a CTE (or subquery), then filter on it in an outer query, where it is now an ordinary computed column available to WHERE (exactly CS2.03-e05's structure).

P12. Spot the normalization violation. A single holdings_report table has columns account_id, account_name, ticker, company_name, sector, quantity, price. Identify the problem and describe how you would split it, referencing this module's schema.

Solution. account_name depends only on account_id (not on the full grain of the table, which is really "one row per account per holding"), and company_name/sector depend only on ticker/company_id, neither is a fact that belongs to the holdings_report row itself, and storing them here duplicates each across every row for the same account or company, exactly the 2NF/3NF risk from §9. The fix: split into a companies-style table (owning ticker, company_name, sector once each) and a fact table storing only account_id, company_id, quantity, and price, precisely this module's companies/transactions split.

P13. When denormalizing is the right call. Following Worked Example 5's reasoning, state the specific circumstance under which deliberately copying ticker into a prices-like table is the correct engineering decision, not a mistake.

Solution. It is correct in a read-heavy, regularly-rebuilt reporting copy, a table explicitly understood by everyone who touches it to be a derived, disposable snapshot (refreshed nightly, hourly, or on demand from the real, normalized system of record), where the value of avoiding a join on every read genuinely outweighs the controlled, well-understood duplication risk. It is the wrong call the moment it happens inside the system of record itself (the tables other processes trust as ground truth and write to directly) because that is where an update anomaly can silently corrupt data nobody is watching for drift.

P14. Indexing priorities. You can index exactly one of these three columns first: (a) companies.sector (8 rows), (b) prices.company_id (2 million rows), (c) prices.trade_date (2 million rows, frequently filtered on alone). Rank them by expected value and justify.

Solution. (c) first, (b) close behind, (a) essentially never. An 8-row table is already faster to fully scan than any index lookup could meaningfully improve on: indexing companies.sector buys nothing worth the write-side upkeep. Both prices columns sit on a 2-million-row table, where a full scan is genuinely expensive; between them, index whichever column is actually filtered on alone in your real queries (per Worked Example 6, an index only serves a query whose filter matches its leading column), here, trade_date is named as frequently filtered alone, making it the higher-value first index, with a composite (company_id, trade_date) index (or the existing UNIQUE constraint's automatic index) covering the other common access pattern.

P15. Reading EXPLAIN QUERY PLAN. You run EXPLAIN QUERY PLAN on a report query and see SCAN prices in the output. What does this tell you, and what is your very next step before deciding to add an index?

Solution. SCAN prices means the engine checked every row of prices to satisfy the query, no index served this particular filter. The next step is not "immediately add an index", it is to check whether the table is actually large enough, and the filtered column selective enough, for an index to matter (per Common Mistakes item 8 and Worked Example 6); if both hold, create the index on the specific column(s) the query filters or joins on, then re-run EXPLAIN QUERY PLAN on the same query to confirm it now reads SEARCH ... USING INDEX before concluding the change actually helped.

P16. Atomicity, traced. A payment process crashes exactly between debiting Account A and crediting Account B, both intended as one logical transfer. Which ACID property, correctly implemented, guarantees the money is not simply lost, and by what mechanism?

Solution. Atomicity. If both writes were wrapped in one transaction (BEGIN; UPDATE ... debit A ...; UPDATE ... credit B ...; COMMIT;), a crash before COMMIT completes means the database, on restart, treats the entire transaction as if it never began, rolling back any partial write (exactly as directly demonstrated with BEGIN; INSERT ...; ROLLBACK; leaving a table untouched). Account A's debit never "sticks" without Account B's matching credit; either the whole transfer lands, or none of it does, never a state where money vanished from A without appearing at B.

P17. LAG() for a day-over-day change. Using NILAM's three-day close series (1190.00, 1200.00, 1208.50, in chronological order), write a query using LAG() to show each day's close alongside the prior day's close and the day-over-day change, and state the result.

Solution.

SELECT trade_date, close,
       LAG(close) OVER (ORDER BY trade_date) AS prior_close,
       ROUND(close - LAG(close) OVER (ORDER BY trade_date), 2) AS day_change
FROM prices WHERE company_id = <NILAM's id> ORDER BY trade_date;

Result: day 1: close 1190.00, prior_close NULL (no earlier row exists: LAG() on the very first row of a partition always returns NULL, not zero and not an error), day_change NULL. Day 2: close 1200.00, prior 1190.00, change +10.00. Day 3: close 1208.50, prior 1200.00, change +8.50.

P18. AI-drafted query, reviewed. An AI assistant, asked for "each company's total transaction value," returns SELECT ticker, SUM(quantity * price) FROM transactions GROUP BY ticker. Run this module's schema-check discipline (the closing ai-augment-json callout) against it. What's wrong?

Solution. It references transactions.ticker, which does not exist, per §2's schema, ticker lives only in companies; transactions stores company_id. The draft never joins to companies at all, so it will fail with a "no such column" error the moment it runs, a relatively kind failure mode, since it does not silently produce a wrong number, but exactly the class of mistake the callout's verification step exists to catch before running it: check every column name against the real schema by eye first. The fix joins transactions to companies on company_id and groups (and reports) by c.ticker: SELECT c.ticker, SUM(t.quantity * t.price) FROM transactions t JOIN companies c ON c.company_id = t.company_id GROUP BY c.ticker.

Applied mini-project

Write a four-query analyst brief against the bundled finance database.

Using this module's code pack (CS2.03) opened in the Practice tab, which loads the full bundled finance-db.sqlite (companies, prices, filings, transactions) via sql.js, write and run four queries that together form a short analyst brief:

1. Coverage check. Which companies have zero filings rows at all (a LEFT JOIN + IS NULL query, exactly CS2.03-e04's shape), flag them as "uncovered" before anyone relies on ratios that don't exist for them.
2. Latest snapshot. Each company's single most recent close price (a ROW_NUMBER()-in-a-CTE query, exactly CS2.03-e05's shape).
3. Activity ranking. Total BUY-side value per company, restricted to companies above a threshold you choose and justify in one sentence, ordered descending (GROUP BY/HAVING, exactly CS2.03-e03's shape), and a written note flagging which currency each company's figure is actually in, since you must never rank raw ₹ and $ totals together without saying so out loud.
4. One comparison of your own design, using at least one CTE and either a window function or a second CTE, that answers a genuine "who's ahead of whom, and by how much" question against this schema (year-over-year growth, above/below the overall average, a sector-relative rank, anything genuinely built on §6–§8, not a copy of an existing exercise's exact question).

For each of the four, write the query, run it against the live database, and (before trusting the result) hand-verify at least one row's arithmetic yourself (a SUM, a percentage, a rank) exactly the way every Worked Example in this module did.

Self-check rubric, score yourself out of 10, pass at 8/10 with no zero on any item:

• (2 pts) All four queries run successfully against the live bundled database and return a sensible, non-empty (or correctly empty, if that's the honest answer) result.
• (2 pts) Query 1 correctly uses LEFT JOIN (not INNER) and the uncovered companies are named explicitly in your write-up, not just implied by an empty-looking table.
• (2 pts) Query 2's window function correctly isolates one row per company (verified: the row count of the result equals the number of distinct companies with at least one price row).
• (2 pts) Query 3's threshold choice is justified in one sentence, and the currency of every figure is stated explicitly, no mixed-currency ranking presented without a caveat.
• (2 pts) Query 4 is genuinely your own design (not a restatement of an existing worked example or code-pack exercise) and at least one of its rows is hand-verified in your write-up, showing the arithmetic, not just "I ran it and it looked right."

Lab: open the CS2.03 pack in this node's Practice tab and run all 8 graded exercises (CS2.03-e01 through CS2.03-e08) to green (hidden tests included) before moving on; this mini-project's four queries deliberately reuse exactly those exercises' shapes, so passing the pack first means every piece of this project is a genuine application, not a first attempt.

Reading & resources

Everything below supports what this module teaches; none of it substitutes for actually writing and running queries against the bundled database yourself. Free unless marked; tags show the ramp from first contact to depth.

• SQLite's own official documentation (sqlite.org, especially the Query Language and EXPLAIN QUERY PLAN pages), the authoritative, exact reference for every syntax rule and execution-plan detail in this module, since SQLite is the actual engine this module's code pack runs. [Free] [Beginner–Advanced]
• SQLBolt and Select Star SQL: short, interactive, browser-based SQL lessons covering SELECT through JOINs and aggregation, an excellent hands-on companion to §3–§6. [Free] [Beginner]
• Mode Analytics' SQL Tutorial: a clear, analyst-oriented walkthrough of window functions and CTEs specifically, going one level deeper than this module's §7–§8 with more worked business examples. [Free] [Beginner–Intermediate]
• PostgreSQL's official documentation on EXPLAIN: even though this module uses SQLite, PostgreSQL's EXPLAIN documentation is unusually thorough on reading query plans and is a strong second reference once §10's SQLite-specific SCAN/SEARCH output feels comfortable. [Free] [Intermediate]
• Markus Winand, "Use The Index, Luke!" (use-the-index-luke.com), the single best deep dive on indexing specifically: what an index actually is, when it helps, and the common ways queries accidentally defeat one. [Free] [Intermediate–Advanced]
• Martin Kleppmann, _Designing Data-Intensive Applications_: the standard deep reference on how real database engines are built and the trade-offs (including normalization, indexing, and transaction isolation) at genuine production scale; the natural next stop after this module, and CS2.04's own anchor text. [Paid] [Advanced]: (a CS2.04-level anchor, named here so you know where this module's foundations lead next.)
• pgexercises.com and SQLZoo: free, browser-based SQL practice sites with instant feedback, an excellent supplement to this module's own code pack for extra repetition on JOIN/GROUP BY/window-function patterns. [Free] [Beginner–Intermediate]

Do this, not just read. Open this module's own code pack, pick any exercise you've already solved, and deliberately break it three ways: change an INNER JOIN to a LEFT JOIN (or the reverse) and see exactly which rows appear or vanish; move a HAVING condition into a WHERE and read the error; delete an ORDER BY from a multi-row result and run it twice to see whether the row order visibly changes. Watching a query fail, or silently return something different, for a reason you predicted before running it is worth more than reading ten more paragraphs about the rule in the abstract.

Teach it back & journal

Feynman prompt. Explain to someone who has only ever used spreadsheets why LEFT JOIN companies TO filings, WHERE filing_id IS NULL is a genuinely better way to find "which companies haven't filed anything yet" than INNER JOIN-ing the same two tables and eyeballing which names are missing from the result. Use, in your own words, the idea that INNER JOIN silently drops unmatched rows while LEFT JOIN keeps them and marks the gap with NULL, an honest signal instead of an invisible omission. Then explain, separately, why WHERE SUM(x) > 100 is invalid SQL but HAVING SUM(x) > 100 is fine, using SQL's actual execution order rather than "that's just the rule." If your listener can, unprompted, predict that a query missing an explicit ORDER BY might return rows in a different order tomorrow than it does today, you have installed this module's central discipline.

Journal prompt. Over the next few days, keep a short log of every SQL query you write against this module's code pack that didn't work on the first try: what you expected, what actually came back, and which single word or clause: INNER vs. LEFT, WHERE vs. HAVING, a missing ORDER BY, a strict > vs. >=, turned out to be the actual cause once you traced it. How many of those were errors that stopped the query outright, versus silent wrong answers that ran without complaint and simply returned the wrong rows? Which failure mode took you longer to catch, and why? Then write a short paragraph: which single habit from this module, hand-tracing the expected result on a small example before trusting a query, checking INNER vs. LEFT deliberately before writing a join, or reading EXPLAIN QUERY PLAN before assuming an index helped, would most change how confidently you'd hand a report built on this database to someone else to rely on, and what will you do differently on your very next query because of it?