The following are the tables of my database that I have already created in mysql

Question

The following are the tables of my database that I have already created in mysql.

What I need is a query that will:

1.) List the airport code of origin and airport code of destination, the departure and arrival time, the airline name, and plane manufacturer and model for all flights that will occur in the future. (The manufacturer and model may be in two separate fields; they do not need to be concatenated.)

2.) Be written so that whenever it is run, it will *always* return only those flights which will occur in the future. Sort your results so that the flights that will occur soonest in the future will be at the top, and the flights furthest out in the future will be at the bottom.

Airlines Database

Primary Key = Plane_ID                                                       

Foreign Key = Carrier_ID references Carriers (Carrier_ID)

Aircraft

Plane_ID

Manufacurer

Model_Num

Original_Purchase_Date

Last_Service

Carrier_ID

Number_of_Seats

Airports

Airport_Code

Airport_Location

Year_Opened

Nuber_of_Terminals

                                                            Primary Key = Airport_code

Carriers

Carrier_ID

Carrier_Name

Primary Key = Carrier_ID     

Flights

Flight_ID

Plane_ID

Carrier_ID

Airport_Code_Origin

Airport_Code_Destination

Departure_DateTime

Arrival_DateTime

Primary Key = Flight_ID

Foreign Key = Airport_Code_Origin references Airports(Airpot_Code)

                         Airport_Code_Destination references Airports(Airport_Code)

Aircraft

Plane_ID

Manufacurer

Model_Num

Original_Purchase_Date

Last_Service

Carrier_ID

Number_of_Seats

Explanation / Answer

Solution:

1)

SELECT Airport_Code_Origin, Airport_Code_Destination, Departure_DateTime, Arrival_DateTime, Carrier_Name, Manufacurer, Model_Num FROM Aircarft INNER JOIN Carriers ON Aircraft.Carrier_ID= Carrier.Carrier_ID INNER JOIN Flights ON Flights.Carrier_ID= Carrier.Carrier_ID

WHERE (

The above query emits all the field mentioned in the problem statement including all the flights of future where condition contains system date and time greater than which will be returned.

b)

1)

SELECT Airport_Code_Origin, Airport_Code_Destination, MIN(Departure_DateTime), Arrival_DateTime, Carrier_Name, Manufacurer, Model_Num FROM Aircarft INNER JOIN Carriers ON Aircraft.Carrier_ID= Carrier.Carrier_ID INNER JOIN Flights ON Flights.Carrier_ID= Carrier.Carrier_ID

WHERE (

We have used here MIN function in departure time which will produce the result as the flights which are departuring earliest will be sorted like that. If you want sort based on arrival time please use Min function at arrival date.

I hope this helps.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.