Assignment 3 – Intro to SQL DDL

Due Date: Feb 21, 2017

Introduction

  • In this exercise you’ll be working in the nyc and nys schemas within the gep664 database.
  • This exercise is worth 6 points. Each question is work 1/2 a point.
  • Be sure to follow the proper conventions for SQL statements and the guidelines for submitting assignments, as posted on the course website. Failure to do so will result in point deductions.

For the following, provide the SQL statement that answers each question; provide the row count only if asked. There may be more than one way to answer certain questions (just choose one).

Part I – Weather Stations table (nyc schema)

In order to select weather stations by state, we currently have to use the LIKE statement to do pattern searching. This is extremely inefficient; let’s add a dedicated state column that will allow for more efficient queries.

1. Alter the weather stations table by adding a column called state that will hold the two-letter state code (i.e. NY, NJ).

2. Update the new state column by adding the two letter state code that indicates where the station is located. You can do this using either one or two statements; you may not insert the codes manually (i.e. by typing each one in individually).

Part II – NYC Borough Data (nyc schema)

You’ll get some practice in table creation by adding a couple of tables with data for the five boroughs of NYC. Each of the boroughs is also considered a county; a borough is a local designation for subdivisions of the City of New York, while counties are a federal designation for subdivisions of states.

3. Create a table called nyc.borough_area to hold the following data: codes and names for the boroughs, and their area in water and land in square miles (for column names, use the names in question 4). Designate a primary key, specify that borough names should not be null, and add a check constraint that land area must be greater than zero.

4. Insert the data into the new table

bcode,bname,area_wtr,area_lnd
bx,Bronx,15.38,42.10
bk,Brooklyn,26.10,70.82
mn,Manhattan,10.76,22.83
qn,Queens,69.68,108.53
si,Staten Island,43.92,58.37

5. Create a view called nyc.borough_sum that has the borough name and calculated fields that have the total land area, the percent total that is water, and the percent total that is land. Optionally – you can round the percentages to whole numbers.

6. Create a table called nyc.counties_pop to hold the county code, county name, borough code, and population for 2010 (for column names, use the names in question 7). Make county code the primary key and borough code the foreign key.

7. Insert the data into the new table

ccode,cname,bcode,pop2010
36005,Bronx County,bx,1385108
36047,Kings County,bk,2504700
36061,New York County,mn,1585873
36081,Queens County,qn,2230722
36085,Richmond County,si,468730

8. Create a new table called nyc.boroughs_pop from the borough area and county population tables that contains: the borough code as primary key, borough name, total population, and population density (number of people per sq mile of land).

Part III – Import Labor Force Table (nys schema)

We’ll import a table from the Census 2010-2014 American Community Survey that indicates whether workers work in the same municipality in which they live. There is one record for each ZCTA in New York State, and like the previous ACS tables we’ve worked with each estimate column has a margin of error column associated with it.

Go to the Data tab on the course website and download the CSV file for place of work by MCD of residence. This CSV has a header row and uses commas for delimiters.

9. Create a table called nys.acs2014_mcdwork that will accommodate all of the data in the CSV file. Make sure to designate a primary key.

10. Use the \copy command to insert the data from the CSV file into the table. Once it’s imported, write a select statement to verify the results. Provide the copy command, the select statement, and the row count (returned by pgAdmin) in your answer.

Part IV – Written Responses

Please answer the following questions in two to four sentences.

11. What is the difference between a primary key and a foreign key?

12. What is a view and how is it different from a table? For what purpose would you create a view?

Leave a Reply

Your email address will not be published. Required fields are marked *