SQL Tutorial

Transforming Techniques

📚 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

Transformations create new calculated columns or categorize data based on conditions.

CASE WHEN tests run in the order you write them, and the first one that passes decides the answer. Scroll through four towns to see why that order matters.

CASE WHEN: the first test that passes wins

CASE
  WHEN pop >= 100000 THEN 'Large City'
  WHEN pop >= 25000  THEN 'Medium City'
  WHEN pop >= 5000   THEN 'Small City'
  ELSE 'Town'
END AS size_category

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

townpopPortland650000Bend99000Astoria10000Granite30same tests, smallest threshold first1. pop >= 5000✓✓✓✗2. pop >= 25000not checkednot checkednot checked✗3. pop >= 100000not checkednot checkednot checked✗size_categorySmall CitySmall CitySmall CityTowntests run top to bottom, one row at a time1. pop >= 100000✓2. pop >= 25000not checked3. pop >= 5000not checkedsize_categoryLarge Citytests run top to bottom, one row at a time1. pop >= 100000✓✗2. pop >= 25000not checked✓3. pop >= 5000not checkednot checkedsize_categoryLarge CityMedium Citytests run top to bottom, one row at a time1. pop >= 100000✓✗✗✗2. pop >= 25000not checked✓✗✗3. pop >= 5000not checkednot checked✓✗size_categoryLarge CityMedium CitySmall CityTown
Step 1 · a new column from rules CASE builds a column by running each row through a list of WHEN tests. The tests are tried in the order you wrote them.
Step 2 · first match wins Portland passes test 1, so it becomes 'Large City' and CASE stops there. Tests 2 and 3 are never checked for this row, even though Portland would pass them as well.
Step 3 · fall through Bend fails test 1 and falls through to test 2, which it passes: 'Medium City'. Reaching test 2 already tells you the row is under 100000, so no upper bound is needed.
Step 4 · ELSE catches the rest Astoria gets as far as test 3. Granite fails every test and lands on ELSE. Leave ELSE out and Granite's category would be NULL.
Step 5 · why the order matters Put the smallest threshold first and Portland passes it immediately, so every city is labeled 'Small City'. SQL raises no error. With overlapping ranges, write the most restrictive test first.
Text version of this diagram
  1. Step 1 · a new column from rules. CASE builds a column by running each row through a list of WHEN tests. The tests are tried in the order you wrote them.
  2. Step 2 · first match wins. Portland passes test 1, so it becomes 'Large City' and CASE stops there. Tests 2 and 3 are never checked for this row, even though Portland would pass them as well.
  3. Step 3 · fall through. Bend fails test 1 and falls through to test 2, which it passes: 'Medium City'. Reaching test 2 already tells you the row is under 100000, so no upper bound is needed.
  4. Step 4 · ELSE catches the rest. Astoria gets as far as test 3. Granite fails every test and lands on ELSE. Leave ELSE out and Granite's category would be NULL.
  5. Step 5 · why the order matters. Put the smallest threshold first and Portland passes it immediately, so every city is labeled 'Small City'. SQL raises no error. With overlapping ranges, write the most restrictive test first.

Transforming 1: Classify towns by size category

Scenario: Create size tiers for a stratified analysis. CASE WHEN acts like an IF-THEN statement. Show town, state, population_2020_census, and a size_category labeling each place ‘Large City’ (100,000 or more), ‘Medium City’ (25,000 or more), ‘Small City’ (5,000 or more), or ‘Town’ otherwise.

The next solution multiplies by 100.0 instead of 100. Scroll to see what that decimal point changes.

A computed column, and why the query says 100.0

SELECT town,
       pop_2020 - pop_2010 AS change,
       ROUND((pop_2020 - pop_2010) * 100.0 / pop_2010, 1) AS pct_change
  FROM towns;

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

townstownpop_2010pop_2020Sisters10001150Joseph10001234Fossil1000950Ridgefield10002500change150234-501500change / pop_20100001change * 100.0 / pop_201015.023.4-5.0150.0
Step 1 · two census columns Each town has a 2010 and a 2020 population. The table stores no growth figure, so the query has to compute one.
Step 2 · arithmetic runs per row pop_2020 - pop_2010 is worked out once for every row. The new change column exists only in the result, and the table is not modified.
Step 3 · the division trap Dividing one whole number by another gives a whole number in SQLite and several other databases: the fraction is thrown away. 150 / 1000 becomes 0, and Ridgefield's 1500 / 1000 becomes 1, not 1.5. There is no error message, only wrong numbers.
Step 4 · 100.0 fixes it Multiply by 100.0 before dividing. The decimal point makes the whole calculation use real numbers, giving 15.0, 23.4, -5.0, and 150.0. ROUND(..., 1) then trims it for display.
Text version of this diagram
  1. Step 1 · two census columns. Each town has a 2010 and a 2020 population. The table stores no growth figure, so the query has to compute one.
  2. Step 2 · arithmetic runs per row. pop_2020 - pop_2010 is worked out once for every row. The new change column exists only in the result, and the table is not modified.
  3. Step 3 · the division trap. Dividing one whole number by another gives a whole number in SQLite and several other databases: the fraction is thrown away. 150 / 1000 becomes 0, and Ridgefield's 1500 / 1000 becomes 1, not 1.5. There is no error message, only wrong numbers.
  4. Step 4 · 100.0 fixes it. Multiply by 100.0 before dividing. The decimal point makes the whole calculation use real numbers, giving 15.0, 23.4, -5.0, and 150.0. ROUND(..., 1) then trims it for display.

Transforming 2: Calculate population change from 2010 to 2020

Scenario: Identify growing and shrinking towns. Show town, state, the census figures as pop_2010 and pop_2020, the difference as pop_change, and the percentage change (1 decimal) as pct_change.

Transforming 3: Identify fastest-growing and declining towns

Scenario: Combine CASE WHEN with calculations to flag growth status. Show town, state, pop_2010, pop_2020, and a growth_status labeled ‘Rapid Growth (>20%)’ for growth above 20%, ‘Growing’, ‘Stable’, ‘Declining’ for declines up to 20%, or ‘Rapid Decline (>20%)’.

Transforming 4: Calculate and categorize population density

Scenario: Classify towns as urban, suburban, or rural based on density. Show town, state, population_2020_census, land_area_sq_mi, the people per square mile (rounded to a whole number) as density, and a density_class of ‘Urban’ (over 5,000), ‘Suburban’ (over 1,000), or ‘Rural’.

Transforming 5: Flag counties by era of establishment

Scenario: Categorize counties by historical period. Show county, state, year_established, and a historical_era of ‘Pioneer Era’ (before 1850), ‘Settlement Era’ (before 1890), ‘Progressive Era’ (before 1920), or ‘Modern Era’.