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	**block**を一意に識別する15桁のコードです。例: 360050001009000
popn_total	block内の人の合計
popn_white	block内で「白人」を自認する人の数
popn_black	block内で「黒人」を自認する人の数
popn_nativ	block内で「ネイティブアメリカン」と自認する人の数
popn_asian	block内で「アジア人」と自認する人の数
popn_other	blockで他のカテゴリを自認する人の数
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	ニューヨーク市の行政区名。マンハッタン区、ブロンクス区、ブルックリン区、スタテンアイランド区、クイーンズ区
geom	blockのポリゴン

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
  

  注釈

  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. 関数リスト

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 集約関数です。