Goal and Background:
The goal of this exercise was to showcase my skills in composing and implementing query expressions to extract data components from a database.
Methods: Part 1:
Using U.S. county data, I wrote a multiple criteria query that returned counties with population between 3000 and 4000 people in 2010 and also all counties in 2010 that had a population density of at least 1000 persons per square mile. In the end it looked as follows:
POP2010 >= 3000 AND POP2010 <= 4000 OR POP10_SQMI >= 1000
Next I wrote a multiple criteria query that returned records for counties in Wisconsin, Texas, New York, Minnesota, and California where male population is greater than female population and also for these states the number of seniors (age 65 and above) is over 6500. In the end it looked as follows:
STATE_NAME
IN ( 'Wisconsin', 'Texas', 'New York', 'Minnesota', 'California') AND MALES
> FEMALES AND AGE_65_UP >6500
I then modified that query to add all other seniors in Washington, Maryland, Illinois, Nebraska, District of Columbia and Michigan who reside in counties that have more than 30,000 housing units to the result obtained in query 2. In the end it looked as follows:
STATE_NAME
IN ( 'Wisconsin','Texas', 'New York', 'Minnesota', 'California') AND MALES >
FEMALES AND AGE_65_UP >6500 OR STATE_NAME IN ( 'Washington', 'Maryland', 'Illinois',
'Nebraska', 'District of Columbia', 'Michigan') AND AGE_65_UP >0 AND
HSE_UNITS >30000
Part 2:
Using Wisconsin data, I developed a query that returned cities in Wisconsin, in 2007, with a population between 15,000 and 20,000 people, area of the city is at least 5 square miles in land area, and also female population is greater than males, and also the cities are within 2 miles of a lake. In the end it looked as follows:
(
"POP2007" >=15000 AND "POP2007" <=20000) AND
"AREALAND" >=5 AND "FEMALES" > "MALES"
I also developed a multiple criteria query to calculate the total length of the following rivers in Wisconsin: CHIPPEWA R, EAU CLAIRE R, 'EMBARRASS R, FISHER R, HUNTING R, KINNICKINNIC R, MAUNESHA R, MILWAUKEE R, MOOSE R, NAMEKAGON R, PELICAN R, PLATTE R, and POTATO R. In the end it looked as follows:
"PNAME"
IN ('CHIPPEWA R', 'EAU CLAIRE R', 'EMBARRASS R', 'FISHER R', 'HUNTING R',
'KINNICKINNIC R', 'MAUNESHA R', 'MILWAUKEE R', 'MOOSE R', 'NAMEKAGON R',
'PELICAN R', 'PLATTE R', 'POTATO R')
Results:
Figure 1. This map demonstrates the results of my first query, determining counties with population between 3000 and 4000 people in 2010 and also all counties in 2010 that had a population density of at least 1000 persons per square mile.
Figure 2. This map demonstrates the results of my second query, demonstrating counties in Wisconsin, Texas, New York, Minnesota, and California where male population is greater than female population and also for these states the number of seniors (age 65 and above) is over 6500.
Figure 3. This map demonstrates the results of my third query, demonstrating modifications to the second query to include all other seniors in Washington, Maryland, Illinois, Nebraska, District of Columbia and Michigan who reside in counties that have more than 30,000 housing units.
Figure 4. This map demonstrates the results of my fourth query, demonstrating cities in Wisconsin, in 2007, with a population between 15,000 and 20,000 people, area of the city is at least 5 square miles in land area, and also female population is greater than males, and also the cities are within 2 miles of a lake.
Figure 5. This map demonstrates the results of my fifth query, demonstrating the following rivers in Wisconsin: CHIPPEWA R, EAU CLAIRE R, 'EMBARRASS R, FISHER R, HUNTING R, KINNICKINNIC R, MAUNESHA R, MILWAUKEE R, MOOSE R, NAMEKAGON R, PELICAN R, PLATTE R, and POTATO R used to calculate their total length.
