get_us_city_population.sql

-- get_us_city_population.sql
-- Clean city names and state codes.

SELECT
		CASE 
			WHEN City	= 'Aims'			THEN 'Ames'
			WHEN City	= 'Amerillo'		THEN 'Amarillo'
			WHEN City	= 'Austen'			THEN 'Austin'
			WHEN City	= 'Boyse'			THEN 'Boise'
			WHEN City	= 'Charlestown'		THEN 'Charleston'
			WHEN City	= 'Checago'			THEN 'Chicago'
			WHEN City	= 'DesMoines'		THEN 'Des Moines'
			WHEN City	= 'Honnollullu'		THEN 'Honolulu'
			WHEN City	= 'Rchester'		THEN 'Rochester'
			WHEN City	= 'San Fransisco'	THEN 'San Francisco'
			ELSE City
		END AS City
	,	CASE
			WHEN State_Code	= 'MP' AND City = 'Minneapolis' THEN 'MN'
			ELSE State_Code
		END AS State_Code
	,	[Population]
FROM	City_Population
ORDER BY
		State_Code
	,	City;