Going Further

📚 Schema reference · towns & counties 3 tables

fips

fips
name
state

pnw_counties

county_id
county
state
fips_short
county_seat
county_seat_town_id
year_established
origin
etymology
population_2022
land_area_sq_mi

pnw_towns

town_id
town
state
primary_county
secondary_county
tertiary_county
primary_county_id
secondary_county_id
tertiary_county_id
population_2020_census
population_2010_census
land_area_sq_mi

These examples go one step past a first session. Each one needs an idea the main sections do not teach: a query nested inside another query, or a join combined with grouping. Come back to them once the six main sections feel comfortable.

Subqueries

MIN and MAX return a number, not the row that number came from. To get the row, compute the number in an inner query and compare against it in the outer one. Scroll to see the order the two queries run in.

A subquery runs first, then the outer query uses its answer

SELECT town, state, pop
  FROM towns
 WHERE pop = (SELECT MIN(pop) FROM towns);

Scroll to build the diagram, one step at a time.

Step 1 · two questions in one "Which town is the smallest?" hides two questions: what is the smallest population, and which row has it. MIN(pop) alone answers the first and loses the town's name.

Step 2 · the inner query runs first The SELECT inside the parentheses runs on its own, first. It scans every pop and returns a single value: 30. Two towns hold that value (ringed in blue), but MIN still returns just the one number.

Step 3 · its answer is dropped in That value takes the place of the parentheses. From here on, the outer query behaves exactly as if you had typed WHERE pop = 30, except that you never had to know the number.

Step 4 · the outer query filters Now an ordinary WHERE checks each row against 30. Granite and Greenhorn both pass.

Step 5 · the whole row comes back The result has the town, state, and population, not only the number, and it has both tied towns. ORDER BY pop LIMIT 1 would have returned only one of them. Writing WHERE pop = MIN(pop) is an error: WHERE tests one row at a time, before any aggregate exists. The subquery computes the aggregate separately and hands back a plain value.

Text version of this diagram

Step 1 · two questions in one. "Which town is the smallest?" hides two questions: what is the smallest population, and which row has it. MIN(pop) alone answers the first and loses the town's name.
Step 2 · the inner query runs first. The SELECT inside the parentheses runs on its own, first. It scans every pop and returns a single value: 30. Two towns hold that value (ringed in blue), but MIN still returns just the one number.
Step 3 · its answer is dropped in. That value takes the place of the parentheses. From here on, the outer query behaves exactly as if you had typed WHERE pop = 30, except that you never had to know the number.
Step 4 · the outer query filters. Now an ordinary WHERE checks each row against 30. Granite and Greenhorn both pass.
Step 5 · the whole row comes back. The result has the town, state, and population, not only the number, and it has both tied towns. ORDER BY pop LIMIT 1 would have returned only one of them. Writing WHERE pop = MIN(pop) is an error: WHERE tests one row at a time, before any aggregate exists. The subquery computes the aggregate separately and hands back a plain value.

Going Further 1: Find the smallest incorporated town

Scenario: A journalist wants to profile the smallest town in the Pacific Northwest. Show the town with its state and population_2020_census.

Going Further 2: Find the largest city

Scenario: Identify the region’s largest metropolitan center. Show the town with its state and population_2020_census.

Going Further 3: Find the actual earliest and newest counties

Scenario: Get the county names, not just the years. Show each county with its state and year_established.

Joins combined with grouping

Real-world queries often combine joins with filtering, grouping, and transformations. The first example counts with COUNT(t.town), not COUNT(*). After a LEFT JOIN those two give different answers for a county with no towns. Scroll to see why.

Counting after a LEFT JOIN: COUNT(*) versus COUNT(t.town)

SELECT c.county, COUNT(t.town) AS num_towns
  FROM counties AS c
  LEFT JOIN towns AS t ON c.county = t.county
 GROUP BY c.county;

Scroll to build the diagram, one step at a time.

Step 1 · the joined rows A LEFT JOIN keeps every county. Wheeler has no towns in the table, so it still gets one row, with NULL where the town would be.

Step 2 · group by county GROUP BY c.county makes three groups. Wheeler's group holds exactly one row: the placeholder the join created.

Step 3 · COUNT(*) overcounts COUNT(*) counts rows, and Wheeler's placeholder is a row. The report says Wheeler has 1 town (ringed in red). It has none.

Step 4 · count the right-side column COUNT(t.town) skips NULL, so the placeholder adds nothing and Wheeler correctly shows 0. After a LEFT JOIN, count a column from the right-hand table, not *.

Text version of this diagram

Step 1 · the joined rows. A LEFT JOIN keeps every county. Wheeler has no towns in the table, so it still gets one row, with NULL where the town would be.
Step 2 · group by county. GROUP BY c.county makes three groups. Wheeler's group holds exactly one row: the placeholder the join created.
Step 3 · COUNT(*) overcounts. COUNT(*) counts rows, and Wheeler's placeholder is a row. The report says Wheeler has 1 town (ringed in red). It has none.
Step 4 · count the right-side column. COUNT(t.town) skips NULL, so the placeholder adds nothing and Wheeler correctly shows 0. After a LEFT JOIN, count a column from the right-hand table, not *.

Going Further 4: Count towns per county (including counties with zero towns)

Scenario: Some small counties might have no incorporated towns in our database. LEFT JOIN preserves them. Show county, state, the county’s population as county_pop, the town count as num_towns, and the summed town population as total_town_pop (0 when a county has no towns).

Going Further 5: Comprehensive county analysis

Scenario: Create a county dashboard showing each county and state with its year_established, an era of ‘Pioneer’ (established before 1860), ‘Settlement’ (before 1900), or ‘Modern’, the county’s population as county_pop, the town count as num_towns, the summed town population as urban_pop, and the population of each county’s largest town as largest_town_pop.