Question: Write the SQL command to answer the following question: What are the d
ID: 3587801 • Letter: Q
Question
Question:
Write the SQL command to answer the following question:
What are the details of the largest airplane (in terms of passenger capacity) that flies out of Phildelphia
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 a.AirplaneNum,max(PassengerCapacity),PilotNum,DepartureDate,Destination,Model,Manufacturer from AIRPLANE a,FLIHT f where f.AirplaneNum=a.AirplaneNum and f.Origin='Phildelphia';
Above is the query which retrieves the required results:
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.