-- CSC296 Database Systems - 2012 -- Homework 4 -- 1. How many people live in Rochester, NY? select 1 as question, feature_name as name, state_alpha as state, pop_2000 as population from cities where feature_name = 'Rochester' and state_alpha = 'NY' ; question | name | state | population ----------+-----------+-------+------------ 1 | Rochester | NY | 219773 (1 row) -- 2. Where is Rochester, NY located? (county and geo coordinates) select 2 as question, feature_name as name, county_name as county, state_alpha as state, prim_lat_dms as latitude, prim_long_dms as longitude from features where feature_name = 'Rochester' and state_alpha = 'NY' ; question | name | county | state | latitude | longitude ----------+-----------+--------+-------+----------+----------- 2 | Rochester | Monroe | NY | 430917N | 0773656W (1 row) -- 3. How many places are there in Texas? select '3a' as question, count(*) as nr_of_cities_in_Texas from cities where state_alpha = 'TX' ; question | nr_of_cities_in_texas ----------+----------------------- 3a | 2325 (1 row) select '3b' as question, count(*) as nr_of_features_in_Texas from features where state_alpha = 'TX' ; question | nr_of_features_in_texas ----------+------------------------- 3b | 10177 (1 row) -- 4. How many places are there with populations of 1,000,000 or more? select 4 as question, count(*) as nr_of_cities_with_populations_of_millions from cities where pop_2000 >= 1000000 ; question | nr_of_cities_with_populations_of_millions ----------+------------------------------------------- 4 | 9 (1 row) -- 5. What is the state with the highest population, and what is that population? select 5 as question, state_alpha as state, sum(pop_2000) as population from cities where pop_2000 > 0 group by state_alpha order by sum(pop_2000) desc limit 1 ; question | state | population ----------+-------+------------ 5 | CA | 27380349 (1 row) -- 6. How many people live at an altitude of 10,000 feet or higher? select 6 as question, sum(c.pop_2000) as people_living_above_10000ft from cities c, features f where c.pop_2000 > 0 and c.feature_name = f.feature_name and c.state_alpha = f.state_alpha and f.elev_in_ft >= 10000 ; question | people_living_above_10000ft ----------+----------------------------- 6 | 4396 (1 row) -- 7. What is the distance in miles between Rochester, NH and Rochester, NY? select 7 as question, from_name, from_state, to_name, to_state, round(ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS(lon2-lon1))*3959) as distance from ( select a.feature_name as from_name, a.state_alpha as from_state, b.feature_name as to_name, b.state_alpha as to_state, radians(a.prim_lat_dec) as lat1, radians(b.prim_lat_dec) as lat2, radians(a.prim_long_dec) as lon1, radians(b.prim_long_dec) as lon2 from features a, features b where a.feature_name = 'Rochester' and a.state_alpha = 'NY' and b.feature_name = 'Rochester' and b.state_alpha = 'NH' ) R ; question | from_name | from_state | to_name | to_state | distance ----------+-----------+------------+-----------+----------+---------- 7 | Rochester | NY | Rochester | NH | 334 (1 row) -- 8. (!) What is the greatest distance between any two large cities (with population >= 100,000) in the DB, and which two cities are they? select 8 as question, from_name, from_state, to_name, to_state, round(ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS(lon2-lon1))*3959) as distance from ( select a.feature_name as from_name, a.state_alpha as from_state, b.feature_name as to_name, b.state_alpha as to_state, radians(a.prim_lat_dec) as lat1, radians(b.prim_lat_dec) as lat2, radians(a.prim_long_dec) as lon1, radians(b.prim_long_dec) as lon2 from features a, features b, cities c, cities d where a.prim_long_dec < b.prim_long_dec and a.feature_name = c.feature_name and a.state_alpha = c.state_alpha and b.feature_name = d.feature_name and b.state_alpha = d.state_alpha and c.pop_2000 >= 100000 and d.pop_2000 >= 100000 ) R order by distance desc limit 1 ; question | from_name | from_state | to_name | to_state | distance ----------+-----------+------------+---------+----------+---------- 8 | Honolulu | HI | Boston | MA | 5082 (1 row) -- 9. (!!) How many people live within a radius of 100 miles from Rochester, NY? -- select Name, Population, Distance select 9 as question, sum(population) as population_within_100_miles_of_rochester_ny from ( select name, population, round(ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS(lon2-lon1))*3959) as distance from ( select c.pop_2000 as population, c.feature_name as name, radians(a.prim_lat_dec) as lat1, radians(b.prim_lat_dec) as lat2, radians(a.prim_long_dec) as lon1, radians(b.prim_long_dec) as lon2 from features a, features b, cities c where a.feature_name = 'Rochester' and a.state_alpha = 'NY' and a.feature_name <> b.feature_name and c.pop_2000 > 0 and b.feature_name = c.feature_name and b.state_alpha = c.state_alpha ) R1 ) R2 where distance <= 100 ; question | population_within_100_miles_of_rochester_ny ----------+--------------------------------------------- 9 | 1474801 (1 row) -- 10. (!!) With a single query determine, for each of the following P's, how many places exist with a population of P, and what is the total population in all those places: -- P < 10 -- 10 <= P < 100 -- 100 <= P < 1,000 -- 1,000 <= P < 10,000 -- 10,000 <= P < 100,000 -- etc. select 10 as question, count(*) as nr_of_places, power(10, 1+floor(log(pop_2000))) as up_to_size, sum(pop_2000) as total_population, round(avg(pop_2000)) as avg_population from cities where pop_2000 > 0 group by floor(log(pop_2000)) order by floor(log(pop_2000)) ; question | nr_of_places | up_to_size | total_population | avg_population ----------+--------------+------------+------------------+---------------- 10 | 23 | 10 | 130 | 6 10 | 997 | 100 | 62768 | 63 10 | 8262 | 1000 | 3708878 | 449 10 | 7344 | 10000 | 24784551 | 3375 10 | 2416 | 100000 | 69355347 | 28707 10 | 223 | 1000000 | 50048871 | 224434 10 | 9 | 10000000 | 22947966 | 2549774 (7 rows)