Section 7: Simple SQL Exercises

Using the nyc_census_blocks table, answer the following questions (don't peak at the answers!).

Here is some helpful information to get started. Recall from the :ref:`About Our Data <about_data>` section our nyc_census_blocks table definition.

And, here are some common SQL aggregation functions you might find useful:

• avg() - the average (mean) of the values in a set of records
• sum() - the sum of the values in a set of records
• count() - the number of records in a set of records

Now the questions:

• "What is the population of the City of New York?"

  SELECT Sum(popn_total) AS population
    FROM nyc_census_blocks;
  

  8008278
  

  Note

  What is this AS? You can give a table or a column another name by using an alias. Aliases can make queries easier to both write and to read. So instead of our outputted column name as sum we write it AS the more readable population.

• "What is the population of the Bronx?"

  SELECT Sum(popn_total) AS population
    FROM nyc_census_blocks
    WHERE boroname = 'The Bronx';
  

  1332650
  

• "What is the average number of people living in each housing unit in New York City?"

  SELECT Sum(popn_total)/Sum(hous_total) AS popn_per_house
    FROM nyc_census_blocks;
  

  2.6503540522400804
  

• "For each borough, what percentage of the population is white?"

  SELECT
      boroname,
      100 * Sum(popn_white)/Sum(popn_total) AS white_pct
    FROM nyc_census_blocks
    GROUP BY boroname;
  

     boroname    |      white_pct
  ---------------+---------------------
   Brooklyn      | 41.2005552206888663
   The Bronx     | 29.8655310846808990
   Manhattan     | 54.3594013771837665
   Queens        | 44.0806610271290794
   Staten Island | 77.5968611401579346