Databases & SQL for Analysts

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.