Examples — towns & counties

Worked SQL examples on the Pacific Northwest pnw_towns, pnw_counties, and fips tables. Each section has runnable examples with starter editors — click Show solution to populate one, then Run to see the result. You can also try your own version first and use Check answer to confirm.

Every section also has scroll-driven diagrams that show what a command does to a table, step by step. The scrollytelling gallery lists all of them.

ER diagram · towns & counties

Text description of this diagram

Three tables and two helper views.

pnw_towns — one row per town. Columns: town_id (primary key), town, state, primary_county, secondary_county, tertiary_county, primary_county_id, secondary_county_id, tertiary_county_id (foreign keys), population_2020_census, population_2010_census, land_area_sq_mi.
pnw_counties — one row per county. Columns: county_id (primary key), county, state, fips_short, county_seat, county_seat_town_id (foreign key; NULL if the seat is not in pnw_towns), year_established, origin, etymology, population_2022, land_area_sq_mi.
fips — FIPS codes for every US state and county (3,195 rows). Columns: fips (state code × 1000 + county code, so 41001 is Baker County, Oregon), name (OREGON, Baker County), state (the abbreviation, such as OR; blank on the state rows).

Relationships: a town's primary_county_id (and optionally secondary_county_id/tertiary_county_id) matches a county_id in pnw_counties; the name columns (primary_county and so on) hold the same county by name. A county's county_seat_town_id matches a town_id in pnw_towns. fips has no key column linking it to the other two tables: its state holds abbreviations (OR) where theirs holds full names (Oregon), so the diagram shows it on its own. A county matches its FIPS row when fips equals the state code times 1000 plus fips_short.

Helper views: towns_with_county and towns_with_counties both project (town, primary_county) from pnw_towns.