# 1. Which airplanes fly LGA to XNA
q1_table <- flights %>%
  inner_join(planes, by = "tailnum") %>%
  filter(origin == "LGA", dest == "XNA") %>%
  select(tailnum, manufacturer, model) %>%
  distinct()

knitr::kable(head(q1_table))
tailnum manufacturer model
N711MQ GULFSTREAM AEROSPACE G1159B
N737MQ CESSNA 172N
N840MQ CANADAIR LTD CF-5D
N713EV BOMBARDIER INC CL-600-2C10 
# 2. Add the airline name to the flights table
q2_table <- flights %>%
  left_join(airlines, by = "carrier") %>%
  select(flight, carrier, name, everything())

knitr::kable(head(q2_table))
flight carrier name year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time arr_delay tailnum origin dest air_time distance hour minute time_hour
1545 UA United Air Lines Inc. 2013 1 1 517 515 2 830 819 11 N14228 EWR IAH 227 1400 5 15 2013-01-01 05:00:00
1714 UA United Air Lines Inc. 2013 1 1 533 529 4 850 830 20 N24211 LGA IAH 227 1416 5 29 2013-01-01 05:00:00
1141 AA American Airlines Inc. 2013 1 1 542 540 2 923 850 33 N619AA JFK MIA 160 1089 5 40 2013-01-01 05:00:00
725 B6 JetBlue Airways 2013 1 1 544 545 -1 1004 1022 -18 N804JB JFK BQN 183 1576 5 45 2013-01-01 05:00:00
461 DL Delta Air Lines Inc. 2013 1 1 554 600 -6 812 837 -25 N668DN LGA ATL 116 762 6 0 2013-01-01 06:00:00
1696 UA United Air Lines Inc. 2013 1 1 554 558 -4 740 728 12 N39463 EWR ORD 150 719 5 58 2013-01-01 05:00:00 
# 3. Which airports have no commercial flights
q3_table <- airports %>%
  left_join(flights, by = c("faa" = "origin")) %>%
  filter(is.na(flight)) %>%
  select(name, faa) %>%
  distinct()

knitr::kable(head(q3_table))
name faa
Lansdowne Airport 04G
Moton Field Municipal Airport 06A
Schaumburg Regional 06C
Randall Airport 06N
Jekyll Island Airport 09J
Elizabethton Municipal Airport 0A9 
# 4. EXTRA CREDIT - Airports with most winds (wind_speed > 30)
q4_table <- weather %>%
  filter(wind_speed > 30) %>%
  group_by(origin) %>%
  summarise() %>%
  left_join(airports, by = c("origin" = "faa")) %>%
  select(name) %>%
  distinct()

knitr::kable(head(q4_table))
name
Newark Liberty Intl
John F Kennedy Intl
La Guardia