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.
Text description of this diagram
Three tables and two helper views.
pnw_towns— one row per town. Columns:town,state,primary_county,secondary_county,tertiary_county,population_2020_census,population_2010_census,land_area_sq_mi.pnw_counties— one row per county. Columns:county,state,fips_short,county_seat,year_established,origin,etymology,population_2022,land_area_sq_mi.fips— FIPS state codes. Columns:fips,name,state.
Relationships: a town's primary_county (and optionally secondary_county/tertiary_county) corresponds to a county in pnw_counties. Both tables share a state column that can join to fips.state.
Helper views: towns_with_county and towns_with_counties both project (town, primary_county) from pnw_towns.