Write a SQL statement to list flight numbers and Airplane models for flights out
ID: 3588050 • Letter: W
Question
Write a SQL statement to list flight numbers and Airplane models for flights out of Philidelphia on November 20th, 2017
Schema below:
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
here is the query
Select FlighNum,Model from Flight,Airplane where Flight.AirplaneNum=Airplane.AirplaneNum and origin != "philidelphia and destination != "philidelphia" and date = "2017-11-20"
As we only want the flights which are out of philidelphia on 20th nov 2017 so we have to find only those flights whose origin and destination are not in that city "philidelphia"
can get this by only using table flight
but we want the airplane details so we need airplane table
i have took catesian product of two tables airplane,flight made these following conditions
Flight.AirplaneNum=Airplane.AirplaneNum
origin != "philidelphia and destination != "philidelphia"
date = "2017-11-20"
I HOPE THIS HELPS
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.