library(tidyverse)
library(mdsr)

# install.packages("nycflights13")
library(nycflights13)
## creating tbl_sql objects 'flights' and 'carriers' (NOT data frames anymore)
db <- dbConnect_scidb("airlines")
flights <- tbl(db, "flights")
carriers <- tbl(db, "carriers")
data(flights)
data(airports)

# Problem 3, Chapter 15 exercises:

#install.packages("sqldf")
library(sqldf)

data(flights)
data(airlines)

sqldf("SELECT
COUNT(*) AS N
FROM flights
WHERE year = 2013 AND month = 5 AND day = 4
AND dest = 'DFW'
LIMIT 0, 6
")

# Problem 5, Chapter 15 exercises:

sqldf("SELECT
dest,
COUNT(*) AS numFlights
FROM flights
WHERE year = 2013 AND origin = 'JFK'
GROUP BY dest
ORDER BY numFlights DESC
LIMIT 0, 10
")

# Problem 6, Chapter 15 exercises:

sqldf("SELECT
dest,
AVG(arr_delay) AS DelayMean
FROM flights
WHERE year = 2013
GROUP BY dest
ORDER BY DelayMean DESC
LIMIT 0, 10
")

data(airports)

sqldf("
SELECT
dest,
airports.name AS dest_name,
AVG(arr_delay) AS DelayMean
FROM flights
JOIN airports ON flights.dest = airports.faa
WHERE year = 2013
GROUP BY dest
ORDER BY DelayMean DESC
LIMIT 0, 10
")

# Problem 7, Chapter 15 exercises:

sqldf("SELECT
COUNT(*) AS numFlights
FROM flights
WHERE year = 2013 AND (origin = 'JFK' or dest = 'JFK')
LIMIT 0, 10
")

# Problem 8, Chapter 15 exercises:

sqldf("SELECT
carrier, flight
FROM flights
WHERE year = 2013 AND month = 9 and day = 26 AND (origin = 'JFK' AND dest = 'LAX')
")