From the database below, write SQL queries to answer the following questions. Th
ID: 3717269 • Letter: F
Question
From the database below, write SQL queries to answer the following questions. The entries to the left of the tables (e.g. A3, L1) are solely for row identification purposes in the questions below and are not part of the logical data of the table.
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
Airport_T Table
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
Airline_T Table
Runway_Name
(key)
Airport_Initials
(key)
Length
(Feet)
Year_Built
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
Runway_T Table
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
Airplane_T Table
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
Service_T table
Write SQL codes to answer the following questions using the table above
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.
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
Explanation / Answer
Solution:
Note: The first four subparts have been answered as per Chegg guidelines, please repost others.
List the number of flight per day for each airline and then rank them in ascending order of total flight per day.
SELECT COUNT(Flights_per_Day) FROM Service_T table GROUP BY Airline_Initials ORDER BY Flights_per_Day ASC
What is the oldest runway in Memphis?
SELECT MAX(Year_Built) FROM Runway_T WHERE Airport_Initials= 'MEM'
Which aircraft manufacturer might have sold their airplane to American Airline?
SELECT Manufacturer FROM Airplane_T WHERE Airline_Initials= 'AA'
Which airport has the highest traffic in year 1955? (Traffic is measured in a form of total flight per day).
SELECT Airport_Initials FROM Service_T table WHERE MAX(Flights_per_Day) AND First_Year<1955
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.