SQL & Databases

Ask where the world's data actually lives, and the honest answer is in relational databases, organized into tables and queried with SQL. The language is over fifty years old, it is remarkably stable, and it runs everything from a phone app's local storage to a bank's core systems to the cloud warehouses behind modern analytics. Learn it once and it pays off for a career, because every tool in this part, Python, R, and the commercial packages, ultimately pulls its data with SQL.

A relational database stores data in tables, each row an record and each column a field. The insight that makes it powerful is to split data across tables to avoid repeating it, and to link the tables with keys. A primary key uniquely identifies each row of a table; a foreign key in another table points back to it. Store each customer once, each product once, and let the orders reference them by key.

Many database systems speak SQL, and the core language is portable across them. SQLite is a tiny file-based engine built into your phone and into Python (this chapter uses it). PostgreSQL and MySQL are the popular open-source servers. SQL Server and Oracle run large enterprises. And cloud warehouses like BigQuery, Snowflake, and Redshift answer SQL over enormous datasets. The dialects differ at the edges; the SELECT you learn here works in all of them.

Almost every SQL query is one sentence with up to six clauses, and they are always written in the same order. You will recognize the logic immediately, because it is the same describe-filter-group-sort you have done throughout this book.

SELECT   columns, and aggregates
FROM     a table (or joined tables)
WHERE    row conditions
GROUP BY columns to summarize by
HAVING   conditions on the groups
ORDER BY a sort key
LIMIT    how many rows to return

There is one surprise worth learning early, and it prevents a lot of confusion: SQL does not run in the order you write it. It reads FROM first to get the rows, filters with WHERE, forms groups, filters those with HAVING, and only then evaluates the SELECT list, sorts, and limits. That is why you can filter groups in HAVING but not yet refer to a SELECT alias there.

Aggregation is the workhorse. COUNT, SUM, AVG, MIN, and MAX collapse many rows to one number, and GROUP BY computes that number for each group. If this feels familiar, it should: SELECT category, SUM(revenue) FROM ... GROUP BY category is the exact SQL twin of df.groupby("category").revenue.sum() from the Python chapter.

Splitting data across tables only pays off because you can put it back together. A JOIN matches rows from two tables on a shared key. The revenue-by-region question is impossible from any single table, but a join from line items to orders to customers makes it a few lines of SQL.

SELECT   c.region, ROUND(SUM(oi.revenue), 2) AS revenue
FROM     order_items oi
JOIN     orders    o  ON oi.order_id   = o.order_id
JOIN     customers c  ON o.customer_id = c.customer_id
GROUP BY c.region
ORDER BY revenue DESC;

A join is the SQL counterpart of a pandas merge and an R dplyr join: same idea, same shared key, different words. Join to reassemble, then GROUP BY to summarize, and you can answer almost any cross-table business question. The notebooks run this exact query and several more.

A GROUP BY reduces each group to a single row, which is exactly what you want for a summary and exactly wrong when you need to keep every row and add a group-wise number beside it. That is what a window function does: it computes an aggregate over a window of rows defined by an OVER clause, but returns a value for every row.

Window functions are what let SQL answer questions a spreadsheet user reaches for constantly: rank the products, show each region's top customer, compute a running total, find month-over-month growth. In the notebook these are one clause each. They are also, not coincidentally, the SQL that analysts are most often asked about in interviews.

Work through these in order. Each loads the shop database into SQLite and runs real SQL, building from a single SELECT up to window functions and the handoff to pandas.

Every notebook runs on pandas, the built-in sqlite3, and (notebook 5) matplotlib, all available on Colab with nothing to install.

SQL is not a rival to Python and R; it is the stage before them. The professional pattern is to push the heavy joining, filtering, and aggregating into the database, where the data already lives and the engine is optimized for it, then pull a compact, tidy result into pandas or R for modeling and charts. Because you know the pandas verbs, the SQL ones need no new concepts.

For machine learning, this matters more than it first appears. Real models are trained on features that are almost always engineered in SQL: a customer's total spend, their order count in the last 90 days, their days since last purchase. Computing those at the source and reading the result into a training frame is the daily reality of applied ML. And the modern data stack has only deepened SQL's role: cloud warehouses run SQL over petabytes, and tools like dbt let analysts build tested, version-controlled transformation pipelines in pure SQL, a discipline now called analytics engineering.

Five exercises, one per notebook area, on the shop database. Full solutions are in the companion solutions notebook.

Eight questions on the relational model, SELECT, joins, aggregation, and window functions. Answer them, hit Check Answers, and keep refining until you score 100%. Your progress is saved.