Directions

For this assignment you should record your answers in an R Markdown file and submit the compiled output as a pdf.

Homework #8 is due April 18 by 10:00pm \(~\)

\(~\)

Question #1

Create a SQL database called “HW8DB” and add the 4 tables from the nycflights13 library to it. Demonstrate that you have done this correctly by printing out the 5 table names and their lengths in SQL.

## [1] "airlines" "airports" "flights"  "planes"   "weather"
## [1] "Flights length: 336776"

Hint: look up the dbWriteTable command

\(~\)

Question #2

Using R, without saving intermediate tables, create a table containing 6 columns: the plane model, the airline name, hour, temperature, origin airport name, and destination airport name for all flights on the day October 9, 2013.

This should be done within a series of pipes, do not save intermediate tables in your final solution.

I have printed out the first 5 rows and the dimensions of the expected data below

##      model                airline timeOfDepart temperature       originAirport
## 1 A321-231        US Airways Inc.            5       51.08 Newark Liberty Intl
## 2  757-222  United Air Lines Inc.            5       51.08 Newark Liberty Intl
## 3  737-724  United Air Lines Inc.            5       55.94          La Guardia
## 4 A320-232        JetBlue Airways            5       53.06 John F Kennedy Intl
## 5     <NA> American Airlines Inc.            5       53.06 John F Kennedy Intl
##             destinationAirport
## 1       Charlotte Douglas Intl
## 2 George Bush Intercontinental
## 3 George Bush Intercontinental
## 4                         <NA>
## 5                   Miami Intl
## [1] 974   6

\(~\)

Question #3

Using SQL, repeat Question 2. This should be done within a single SQL query.

For example:

print("Differences other than NAs")
## [1] "Differences other than NAs"
sum((Q2data==Q3data)==FALSE,na.rm=TRUE)
## [1] 0

\(~\)

Question #4

Using either dataset above: recreate the following figure as best as you are able.