#
source:
trunk/workshop-foss4g/simple_sql_exercises.rst
@
1

Revision 1, 3.7 KB checked in by djay, 12 years ago (diff) |
---|

Rev | Line | |
---|---|---|

[1] | 1 | .. _simple_sql_exercises: |

2 | ||

3 | Section 7: Simple SQL Exercises | |

4 | =============================== | |

5 | ||

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

7 | ||

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

9 | ||

10 | .. list-table:: | |

11 | :widths: 20 80 | |

12 | ||

13 | * - **blkid** | |

14 | - A 15-digit code that uniquely identifies every census **block**. Eg: 360050001009000 | |

15 | * - **popn_total** | |

16 | - Total number of people in the census block | |

17 | * - **popn_white** | |

18 | - Number of people self-identifying as "white" in the block | |

19 | * - **popn_black** | |

20 | - Number of people self-identifying as "black" in the block | |

21 | * - **popn_nativ** | |

22 | - Number of people self-identifying as "native american" in the block | |

23 | * - **popn_asian** | |

24 | - Number of people self-identifying as "asias" in the block | |

25 | * - **popn_other** | |

26 | - Number of people self-identifying with other categories in the block | |

27 | * - **hous_total** | |

28 | - Number of housing units in the block | |

29 | * - **hous_own** | |

30 | - Number of owner-occupied housing units in the block | |

31 | * - **hous_rent** | |

32 | - Number of renter-occupied housing units in the block | |

33 | * - **boroname** | |

34 | - Name of the New York borough. Manhattan, The Bronx, Brooklyn, Staten Island, Queens | |

35 | * - **the_geom** | |

36 | - Polygon boundary of the block | |

37 | ||

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

39 | ||

40 | * avg() - the average (mean) of the values in a set of records | |

41 | * sum() - the sum of the values in a set of records | |

42 | * count() - the number of records in a set of records | |

43 | ||

44 | Now the questions: | |

45 | ||

46 | * **"What is the population of the City of New York?"** | |

47 | ||

48 | .. code-block:: sql | |

49 | ||

50 | SELECT Sum(popn_total) AS population | |

51 | FROM nyc_census_blocks; | |

52 | ||

53 | :: | |

54 | ||

55 | 8008278 | |

56 | ||

57 | .. note:: | |

58 | ||

59 | 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``. | |

60 | ||

61 | * **"What is the population of the Bronx?"** | |

62 | ||

63 | .. code-block:: sql | |

64 | ||

65 | SELECT Sum(popn_total) AS population | |

66 | FROM nyc_census_blocks | |

67 | WHERE boroname = 'The Bronx'; | |

68 | ||

69 | :: | |

70 | ||

71 | 1332650 | |

72 | ||

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

74 | ||

75 | .. code-block:: sql | |

76 | ||

77 | SELECT Sum(popn_total)/Sum(hous_total) AS popn_per_house | |

78 | FROM nyc_census_blocks; | |

79 | ||

80 | :: | |

81 | ||

82 | 2.6503540522400804 | |

83 | ||

84 | * **"For each borough, what percentage of the population is white?"** | |

85 | ||

86 | .. code-block:: sql | |

87 | ||

88 | SELECT | |

89 | boroname, | |

90 | 100 * Sum(popn_white)/Sum(popn_total) AS white_pct | |

91 | FROM nyc_census_blocks | |

92 | GROUP BY boroname; | |

93 | ||

94 | :: | |

95 | ||

96 | boroname | white_pct | |

97 | ---------------+--------------------- | |

98 | Brooklyn | 41.2005552206888663 | |

99 | The Bronx | 29.8655310846808990 | |

100 | Manhattan | 54.3594013771837665 | |

101 | Queens | 44.0806610271290794 | |

102 | Staten Island | 77.5968611401579346 | |

103 | ||

104 | Function List | |

105 | ------------- | |

106 | ||

107 | `avg(expression) <http://www.postgresql.org/docs/8.2/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE>`_: PostgreSQL aggregate function that returns the average value of a numeric column. | |

108 | ||

109 | `count(expression) <http://www.postgresql.org/docs/8.2/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE>`_: PostgreSQL aggregate function that returns the number of records in a set of records. | |

110 | ||

111 | `sum(expression) <http://www.postgresql.org/docs/8.2/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE>`_: PostgreSQL aggregate function that returns the sum of records in a set of records. |

**Note:**See TracBrowser for help on using the repository browser.