[Solved]Pk Pk Ap Code Ame Ap City Ap Country Epowle T Fli Numavailseats Assigned H Ipi City Pi Sta Q37047981
Use the above ERD to write the following SQL Queries.
PK PK Ap Code ame Ap-City – Ap Country epowle-t Fli NumAvailSeats assigned H Ipi-City Pi StateOrProvince assigned FK1 Ac SerialNum K2 PI CAA Lice FK3 Ap Code AircraftType Airport PK,FK1 AT ID PK,FK2 Ap Code Pi EmergencyPhone Booking Class PK BC ID BC FliCostFactor BC Description PK Pas ID Aircraft Type PK AT ID AT Name Bk SeatNum Bk CreditCard Aircraft um FK3 Fli Num Ac ModelNum Ac NumSeats Aircraft Manufacturer Ac ManufactureDate M Name FK1 AT ID i. Query 6: List the total number of aircrafts each passenger has travelled in. List the passenger’s title, full name and country as well. The total number of aircrafts and full name should be aliased as TotalAircrafts and Full Name, respectively. Sort the results in ascending order of the passenger’s full name i. Query Z: For each flight, list its flight number, departing airport (eg. Nadi International Airport) and the airport’s country (eg. Fiji). List also the flight’s departure date in the format: 08/04/2018, which represents 8th April 2018. Order the results in descending order of the departing airport’s country name VI. Query 8: Write a query to display for each booking, the booking ID, the corresponding flight number, the corresponding passenger’s full name aliased as Full Name, the corresponding flight’s departure date and time combined using the format example: Jan 9, 2017 – 9.24 PM. Also include the flight’s combined arrival date and arrival time in the same format as the departure details. For these combined time details, if either the date or time is null, show a null value for the combined result. Alias the combined time details as DepartureTime and ArrivalTime for the departure and arrival time details, respectively Finally, include the flight duration (aliased as Duration) in the format: 1 day(s), 4 hr(s) and 4 min(s) For this query, assume that some flights may be longer in duration due to stoppages/transits etc Hence, the duration tracking by days is included as well. The duration is simply the difference between the ArrivalTime and DepartureTime described earlier, and each day will be considered to be 24 hours. The duration should be nullified if any of the values being combined in the duration are null. All results are to be sorted in the ascending order of the passenger’s full name VIl. Query 9:Write a query to update the pilot salary to double its current figure for all pilots whose current salary is less than or the same as $20,000. The same update should also happen to all pilots that are based in the state of Arizona with a zip code of 85003 in USA (use this abbreviation and not its full form in the query). Only one query is required for this question VIll. Query 10:Write a query to remove all bookings that have not yet been paid for Show transcribed image text PK PK Ap Code ame Ap-City – Ap Country epowle-t Fli NumAvailSeats assigned H Ipi-City Pi StateOrProvince assigned FK1 Ac SerialNum K2 PI CAA Lice FK3 Ap Code AircraftType Airport PK,FK1 AT ID PK,FK2 Ap Code Pi EmergencyPhone Booking Class PK BC ID BC FliCostFactor BC Description PK Pas ID Aircraft Type PK AT ID AT Name Bk SeatNum Bk CreditCard Aircraft um FK3 Fli Num Ac ModelNum Ac NumSeats Aircraft Manufacturer Ac ManufactureDate M Name FK1 AT ID
i. Query 6: List the total number of aircrafts each passenger has travelled in. List the passenger’s title, full name and country as well. The total number of aircrafts and full name should be aliased as TotalAircrafts and Full Name, respectively. Sort the results in ascending order of the passenger’s full name i. Query Z: For each flight, list its flight number, departing airport (eg. Nadi International Airport) and the airport’s country (eg. Fiji). List also the flight’s departure date in the format: 08/04/2018, which represents 8th April 2018. Order the results in descending order of the departing airport’s country name VI. Query 8: Write a query to display for each booking, the booking ID, the corresponding flight number, the corresponding passenger’s full name aliased as Full Name, the corresponding flight’s departure date and time combined using the format example: Jan 9, 2017 – 9.24 PM. Also include the flight’s combined arrival date and arrival time in the same format as the departure details. For these combined time details, if either the date or time is null, show a null value for the combined result. Alias the combined time details as DepartureTime and ArrivalTime for the departure and arrival time details, respectively Finally, include the flight duration (aliased as Duration) in the format: 1 day(s), 4 hr(s) and 4 min(s) For this query, assume that some flights may be longer in duration due to stoppages/transits etc Hence, the duration tracking by days is included as well. The duration is simply the difference between the ArrivalTime and DepartureTime described earlier, and each day will be considered to be 24 hours. The duration should be nullified if any of the values being combined in the duration are null. All results are to be sorted in the ascending order of the passenger’s full name VIl. Query 9:Write a query to update the pilot salary to double its current figure for all pilots whose current salary is less than or the same as $20,000. The same update should also happen to all pilots that are based in the state of Arizona with a zip code of 85003 in USA (use this abbreviation and not its full form in the query). Only one query is required for this question VIll. Query 10:Write a query to remove all bookings that have not yet been paid for
Expert Answer
Answer to PK PK Ap Code ame Ap-City – Ap Country epowle-t Fli NumAvailSeats assigned H Ipi-City Pi StateOrProvince assigned FK1 Ac… . . .
OR
