I need the mysql querry for this problem, that I cannot seem to get right. Below

Question

I need the mysql querry for this problem, that I cannot seem to get right. Below is the database structure, which I already created.

I need to 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.)

A successful query will:

1.) Concatenate your aircraft model number and manufacturer to one column

2.) Present the total number of seats in decimal format, to two decimal places (for compatability with legacy systems).

3.) List the aircraft with highest seat count first.

The results will look something like this

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

Plane Type Boring-Rocketship-123 Air Scooter-Putt Putt XJ 314 right Bros-Canvas Delite 987 Number o seats 50.00 3000 2.00

Explanation / Answer

Edited as per the requirement in comment,

we need " concatenate the manufacturer and model number from the aircraft table as one column, and list it along with the number of seats as the second column from planes that were made from 1/1/2010 - 12/31/2015 with 2 places after the decimal in the number of seats column"

since we only need data from one table and we dont even need join.

since we want aircrafts manufature between certain dates, we can use between and for concatenation, we can use concat function available in mysql and finally to get the seats upto 2 decimal places we can use round(a,2) and at the last we can order the data in descending order by using order by desc. thus the query becomes

select CONCAT(b.Manufacurer,b.Model_Num),ROUND(b.Number_of_Seats,2)

from Aircraft b where (b.Original_Purchase_Date BETWEEN '2010-01-01' AND '2015-12-31')

order by b.Number_of_Seats desc;

Thumps up if this was helpful, if not let me know in comments.

Good day.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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