Query-Writing Practice Exercises

Query-Writing Practice Exercises

To help you get oriented to writing SQL queries on the pgAdmin command line, try your hand at the following exercises. Recall that the 2008 population data, soft drink data, and geometries are in the states table, and that the 2010 data are in the census2010 table.

1. Select the states with a 2008 population over 10 million.
2. Select the state capitals.
3. Select the states whose names begin with the word "New".
4. Select the cities whose names contain the letter "z".
5. Sort the states by their 2008 population from high to low.
6. Sort the states first by soft drink name then by state name.
7. Select the states with a 2008 population over 10 million and where the majority of the population refers to soft drinks as pop.
8. Select cities in the states of NY, NJ, and PA (using the stateabb column).
9. For each state, compute the percentage of the 2010 population that is white. Give this output column an alias of pctwhite. Besides pctwhite, include only the name of the state in the output. Note: the columns involved in this calculation are defined as integers, which means the resulting value will be rounded to the nearest integer (0). To avoid this rounding and obtain the desired percentages, add '::double precision' after the white column in the calculation. This will convert the integer values to double precision values prior to the calculation. It is only necessary to perform this conversion for one of the columns involved in the calculation.
10. Sum the 2008 state populations across the soft drink categories (i.e., What is the population of the 'soda' states? Of the 'pop' states? Of the 'coke' states?).
11. Bring together data from the states and census2010 tables, outputting the name from the states table, total population from the census2010 table and geom from the states table.
12. Calculate the average 2010 male population across the soft drink categories.

Solutions (This link takes you to the bottom of the Lesson.)