Write a SQL statement to list flight numbers and Airplane models for flights out
ID: 3588055 • Letter: W
Question
Write a SQL statement to list flight numbers and Airplane models for flights out of Philadelphia on Nov. 6, 2015.
Grand Travel Airlines has to keep track of its flight and airplane history.
A flight is uniquely identified by the combination of a flight number and a date;
Every passenger who has flown on Grand Travel has a unique passenger number;
For a particular passenger who has taken a particular flight, the company wants to keep track of the fare that she paid for it and the date that she made the reservation for it;
Clearly, a passenger may have taken many flights and every flight has had many passengers on it;
A pilot is identified by a unique pilot number;
A flight on a particular date has exactly one pilot. Each pilot has typically flown many flights;
Each airplane has a unique serial number. A flight on a particular date used one airplane.
The tables are as follows.
PILOT
Column Name
Key Type
Null/ unique
FK Table
FK Column
Data type
Max length
PilotNum
NN, U
INT
9
FirstName
NN
Varchar
50
LastName
NN
Varchar
50
DateOfBirth
NN
DateOfHire
NN
Date
FLIGHT
Column Name
Key Type
Null/ unique
FK Table
FK Column
Data type
Max length
FlightNum
PK
NN
INT
9
DepartureDate
PK
NN
Date
Origin
NN
Char
3
Destination
NN
Char
3
PilotNum
FK
NN
PILOT
PilotNum
INT
9
AirplaneNum
FK
NN
AIRPLANE
AirplaneNum
INT
9
PASSENGER
Column Name
Key Type
Null/ unique
FK Table
FK Column
Data type
Max length
PassengerNum
PK
NN, U
INT
9
FirstName
NN
Varchar
50
LastName
NN
Varchar
50
StreetAddress
NN
Varchar
100
City
NN
Varchar
50
State
NN
Char
2
PhoneNum
NN
Varchar
20
RESERVATION
Column Name
Key Type
Null/ unique
FK Table
FK Column
Data type
Max length
FlightNum
PK,FK
NN
FLIGHT
FlightNum
INT
9
Date
PK
NN
Date
PassengerNum
PK,FK
NN
PASSENGER
PassengerNum
Varchar
50
Fare
NN
Decimal
9,2
ReservationDate
NN
Date
AIRPLANE
Column Name
Key Type
Null/ unique
FK Table
FK Column
Data type
Max length
AirplaneNum
PK
NN,U
INT
9
Model
NN
Varchar
25
PassengerCapacity
NN
INT
4
YearBuilt
NN
Date
Manufacturer
NN
Varchar
0
Column Name
Key Type
Null/ unique
FK Table
FK Column
Data type
Max length
PilotNum
PKNN, U
INT
9
FirstName
NN
Varchar
50
LastName
NN
Varchar
50
DateOfBirth
NN
DateDateOfHire
NN
Date
Explanation / Answer
select F.FlightNum , A.Model from FLIGHT F, AIRPLANE A
where F.AirplaneNum = A.AirplaneNum and
F.Origin = 'Philadelphia' and F.DepartureDate = 'Nov. 6, 2015' ;
Here flights out of Philadelphia means origin is Philadelphia.
on on Nov. 6, 2015 means Departure Date is Nov. 6, 2015.
Here we can get data of flights from FLIGHT table hence
we can take flight number from FLIGHT table.
Airplane model is only present in AIRPLANE table
hence we need to join both FLIGHT and AIRPLANE tables
based on there common column AirplaneNum.
This is done up to here
select F.FlightNum , A.Model from FLIGHT F, AIRPLANE A
where F.AirplaneNum = A.AirplaneNum
Now we need origin as Philadelphia and date as Nov. 6, 2015
This is done here
F.Origin = 'Philadelphia' and F.DepartureDate = 'Nov. 6, 2015' ;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.