Write a query to list all reservations (all fields) for flight 278 on February 2
ID: 3587802 • Letter: W
Question
Write a query to list all reservations (all fields) for flight 278 on February 21, 2012.
Based On:
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 FlightNum,Date,PassengerNum,Fare,ReservationDate, DepartureDate from RESERVATION r,FLIGHT f where f.FlightNum=r.FlightNum and f.DepartureDate='21-02-2012' and f.FlightNum=278;
The above is the Sql query which retrieves the required records.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.