Write a query to list the AirplaneNumber and PassengerCapacity of all airplanes
ID: 3587793 • Letter: W
Question
Write a query to list the AirplaneNumber and PassengerCapacity of all airplanes with their Model values starting with a letter "P" or "M".
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 AirplaneNum,PassengerCapacity from AIRPLANE where Model LIKE '%P% or Model LIKE '%M%' ;
Select FLIGHT.FlightNum,DepartureDate , PASSENGER.PassengerNum, ReservationDate,Fare,PILOT.PilotNum, AIRPLANE.AirplaneNum from FLIGHT inner join PILOT on FLIGHT.PilotNum = PILOT.PilotNum inner join AIRPLANE on FLIGHT.AirplaneNum = AIRPLANE.AirplaneNum inner join RESERVATION on RESERVATION.FlightNum = FLIGHT.FlightNum;
The common columns in tables need table name before column name to avoid ambiguity.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.