SQL 101 · Questions a table can answer

PANDAS 101 read a dirty file, repaired it, and answered questions about groups with a frame. This course asks the same questions in the language every database speaks, over the same lot: the 24 used car listings and the four row brand table, written back out clean, with the prices as numbers. The tool is DuckDB, a database that lives in a Python package, needs no server, and reads a CSV file by its name as if it were a table. What is left of the dirt is what SQL is about: two empty odometers, four missing seller ratings, one missing price, a 1971 collector car at 145000, and a brand table that has never heard of Torres.

Nine lessons take a query from SELECT to a window function, and the last one answers a buyer's question in one query with every trap handled and checks it by a second route in pandas. Every number in the prose was produced by the cell above it.

How this course teaches

Every lesson is the same twenty six cells, and nine of them are yours.

• A prediction you commit to before the cell runs. It is graded on the reasoning, not the guess, and being wrong here is the point.
• Warmups: a one line blank or a two to four line exercise under the theory it practices, each with a four rung hint ladder behind it, where the last rung explains and still does not hand over the code.
• An exercise that is broken when you open it.
• A diagnose cell: a query that runs, returns a real row or a real number, and answers a different question from the one asked. Something below it refuses the answer by computing the same thing a second way, so nothing is taken on trust.
• A challenge that ends in a sentence you write. The tutor grades the sentence, which means a green tick you earned for the wrong reason can be taken back.

No cell in this course passes in the state it ships. That is deliberate, and it is checked mechanically before the course is published.

The particular danger of this subject is a query that runs cleanly and answers a different question. LIMIT 1 returns the first row on disk and gets called the cheapest. A comparison with a missing value is neither true nor false, so <> 5.0 keeps 19 of 24 and subtracting the below count from the table credits the four unrated sellers to the good side. AVG divides by 20 while COUNT(*) says 24, and a sum over the row count rates four sellers zero. Ranking brands by damaged count names the right brand for the wrong reason, and relisting one brand's cars changes hands. An inner join drops seven cars and raises the lot's average by four thousand without a word. A row number minus one is not the number of cars ahead. The same file is a different table in pandas, where NaN != 5 is True. Every diagnose cell in the course is one of those, and every cross check is the second route that refuses it: MIN where LIMIT was trusted, a direct count where a subtraction was, AVG against a by-hand division, a relisting a rate has to survive, the lot's own average against the join's, a count of strictly newer years, and the other reader.

What you will be able to do

• Read a file as a table, pick and compute columns, find out what types the reader chose, and know that a row from LIMIT is a row and not a winner.
• Filter rows with any combination of conditions, know that every comparison drops the missing values from both of its sides, and ask about them with the one test that sees them.
• Sort by several keys, take the top n and the nth page, put the missing values where you mean them to be, and tell a top three from a top one per group.
• Summarise a column in one line, say exactly which rows the number covers, keep an average honest about its missing values, and put a median beside it when an outlier owns it.
• Group by one column or two, aggregate per group, filter the groups, and refuse a ranking by count when the question was about a share.
• Join two tables the way the question needs, find what a lookup table is missing with one query, and catch a join that quietly changed the population a number is about.
• Rank within a group, run a total, put a group's number beside each row, keep a top n per group, and pick the ranking function by the question.
• Query a frame in SQL, bring a result back, pick the tool a question belongs to, and catch a missing value that means one thing on one side of the bridge and another on the other.
• Take a question in plain words, turn it into one query with every trap handled, check it by a second route, and read the answer for what it left out.

The lessons

1. SELECT. A file name in quotes is a table. Columns, expressions with AS, COUNT(*), the relation's three readers, and DESCRIBE, which shows that DuckDB typed damage as BOOLEAN from yes and no. The diagnose is LIMIT 1 called the cheapest: A-101 at 18500 against a minimum of 8300.

2. WHERE. AND, OR, IN, BETWEEN, LIKE, and the third truth value: <> 5.0 keeps 19 of 24, = NULL keeps none, IS NULL sees the four. Ten rated 4.5 or better, ten below, NOT of the first is still ten. The diagnose subtracts the below count from 24 and reports 14 where the direct count says 10.

3. ORDER BY and DISTINCT. Sort, tie-break, LIMIT, OFFSET, and the unpriced car that sorts last in both directions until NULLS FIRST. DISTINCT for three brands, six models and four gearbox and damage pairs. The diagnose is the three newest cars called the newest per brand, with no Torres; DISTINCT ON (brand) is the fix.

4. Aggregates. COUNT(*) 24 against COUNT(seller_rating) 20; SUM, AVG, MIN, MAX, MEDIAN; the collector car moving the average price by 5300 and the median by 200; a share as AVG of a CASE. The diagnose divides a sum of twenty ratings by twenty four rows: 3.696 against AVG's 4.435.

5. GROUP BY and HAVING. One row per brand, two grouping columns, HAVING where an aggregate may sit, and the engine's own refusal of an aggregate in WHERE. Worst brand by count and by rate both say Ravon, and Torres relisting its seven cars moves the count and not the rate. The diagnose ranks by SUM and is refused by the relisting.

6. JOIN. Inner 17 rows, left 24, right listing every brand in the table, full 26. The inner join's average price is 31724 against the lot's 27926, because the seven it dropped were the cheap ones. COUNT(*) on a right join counts an unsold brand as one. COALESCE names the unknown segment.

7. Window functions. ROW_NUMBER, RANK and DENSE_RANK on three cars sharing 2021: positions 2, 3, 4; ranks 2, 2, 2; the first 2020 car is row 5, rank 5, dense rank 3. PARTITION BY brand with QUALIFY for a top n per group, running totals, a brand's average beside each car. The diagnose reads a row number minus one as the count of newer cars and is off by one.

8. SQL and pandas together. FROM cars_df queries the frame in scope, .df() brings a result back. pandas kept damage as text and counts 23 sellers not rated 5.0 where SQL counts 19, because NaN != 5 is True. pandas count is COUNT(price), merge is inner by default, and a pivot is one call. The diagnose trusts the 23.

9. One real question. The cheapest undamaged car per segment from a seller rated 4.5 or better, with Torres kept as unknown: a left join with a named gap, three conditions with an explicit IS NOT NULL, a partitioned rank with QUALIFY, nine candidates and three picks, A-101, A-110 and A-122. The pandas route agrees. The diagnose is the inner join's two segments, and the cheapest pick of all was the one it dropped.

What you need

• A Python kernel, 3.9 or later, with duckdb and pandas: pip install duckdb pandas. No server, no network.
• About 495 minutes across the nine lessons, at 50 to 60 each.
• PANDAS 101, for what a table, a column and a missing value are.