Airport_Initials (key) Airport_Name City State
A1 DFW Dallas-Ft. Worth Dallas TX
A2 JFK Kennedy Intl New York NY
A3 LAX Los Angeles Intl Los Angeles CA
A4 MEM Memphis Memphis TN
A5 MIA Miami Intl Miami FL
Airline_Initials (key) Airline_Name HQ_City HQ_State
L1 AA American Airline Dallas TX
L2 DL Delta Atlanta GA
L3 NW Northwest Minneapolis MN
L4 TW Trans World St. Louis MO
L5 UN United Chicago IL
R1 4 Left MIA 1200 1958
R2 5 Right DFW 1500 1984
R3 5 Right LAX 1500 1984
R4 5 Right MEM 1000 1962
R5 7 Left JFK 1700 1960
R6 7 Left MEM 1500 1989
R7 8 Right LAX 1500 1984
Serial _Number (key) Type Capacity Airline_Initials Manufacturer
P1 01754 747 250 TW Boeing
P2 04970 727 130 AA Boeing
P3 17594 DC-10 180 AA McDonnell-Douglas
P4 18113 MD-11 230 NW McDonnell-Douglas
P5 26040 MD-11 230 UN McDonnell-Douglas
P6 35891 A320 130 DL Airbus Intl
P7 48645 747 220 DL Boeing
Airline_Initials (key) Airport_Initials (key) Flights_per_Day First_Year
S1 AA DFW 160 1978
S2 AA LAX 35 1935
S3 DL DFW 80 1978
S4 DL JFK 40 1952
S5 NW LAX 20 1954
S6 NW MEM 125 1948
S7 TW JFK 45 1957
S8 UN LAX 35 1930
How to write SQL queries to answer the following questions?
- List the number of flight per day for each airline and then rank them in ascending order of total flight per day.
- What is the oldest runway in Memphis?
- Which aircraft manufacturer might have sold their airplane to American Airline?
- Which airport has the highest traffic in year 1955? (Traffic is measured in a form of total flight per day).
- Which airline has the highest number of airplane option (airplane type) to choose from?
- Fnd serial numbers of airplanes that belongs to Delta Airline. (Do this question by using JOIN not subquery)
- Convert the code in the previous problem to subquery format.
- Convert the code in the previous problem to subquery format with Exists.
- What is the total number of seats that American Airline has for their planes purchased from Boeing. Use Join for this question.