8. Simple SQL Exercises

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

Here is some helpful information to get started. Recall from the About Our Data section our nyc_census_blocks table definition.

blkid

A 15-digit code that uniquely identifies every census block. («360050001009000»)

popn_total

Número total de personas en el bloque del censo

popn_white

Number of people self-identifying as «white» in the block

popn_black

Number of people self-identifying as «black» in the block

popn_nativ

Number of people self-identifying as «native american» in the block

popn_asian

Number of people self-identifying as «asias» in the block

popn_other

Número de personas que se auto-identifican como otras categorías

hous_total

Number of housing units in the block

hous_own

Number of owner-occupied housing units in the block

hous_rent

Number of renter-occupied housing units in the block

boroname

Nombre del distrito de Nueva York New York. Manhattan, The Bronx, Brooklyn, Staten Island, Queens

geom

Límite del bloque

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:

  • How many records are in the nyc_streets table?

    SELECT Count(*)
    FROM nyc_streets;
    
    19091
    
  • How many streets in NYC start with ‘B’?

    SELECT Count(*)
      FROM nyc_streets
      WHERE name LIKE 'B%';
    
    1282
    
  • What is the population of the City of New York?

    SELECT Sum(popn_total) AS population
      FROM nyc_census_blocks;
    
    8175032
    

    Nota

    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';
    
    1385108
    
  • How many «neighborhoods» are in each borough?

    SELECT boroname, count(*)
      FROM nyc_neighborhoods
      GROUP BY boroname;
    
       boroname    | count
    ---------------+-------
     Queens        |    30
     Brooklyn      |    23
     Staten Island |    24
     The Bronx     |    24
     Manhattan     |    28
    
  • For each borough, what percentage of the population is white?

    SELECT
      boroname,
      100.0 * Sum(popn_white)/Sum(popn_total) AS white_pct
    FROM nyc_census_blocks
    GROUP BY boroname;
    
       boroname    |    white_pct
    ---------------+------------------
     Brooklyn      | 42.8011737932687
     Manhattan     | 57.4493039480463
     The Bronx     | 27.9037446899448
     Queens        |  39.722077394591
     Staten Island | 72.8942034860154
    

8.1. Lista de funciones

avg(expression): PostgreSQL aggregate function that returns the average value of a numeric column.

count(expression): PostgreSQL aggregate function that returns the number of records in a set of records.

sum(expression): PostgreSQL aggregate function that returns the sum of records in a set of records.