Please answer these questions in SQL statements. The logical model is provided, please answer with SQL statements
Question:
Please answer these questions in SQL statements.
The logical model is provided, please answer with SQL statements based on this logical model.
Q1 List all endorsements held by pilots where the endorsement's last annual review date was after 31st March 2020. Show the helicopter type number, employee number, employee last name, employee first name and the date of the review. This listing should be displayed in ascending order of the last annual review date. [4 marks]
Q2 List those charters where special requirements are specified. Show the charter number, client number, client last name, client first name and the special requirements specified. This listing should be displayed in ascending order of the charter number. [4 marks]
Q3 List the charter details for those charters which meet the following requirements: ● destination is Mount Doom (ie. charters with any leg that has a destination of mount doom should be included in the output), and ● the charter cost is less than $1000 per hour, or no special requirements are specified Show the charter number, client last name, client first name and the charter cost per hour. The client name should be listed in a single column called FULLNAME (sample output: Gandalf The Grey). The listing should be displayed in the descending order of the client FULLNAME.
Q4 For each helicopter type for which there are at least two helicopters of that type, list the total number of helicopters of that type. Show the helicopter type number, helicopter type name and the number of helicopters owned. The listing should be displayed in descending order of the number of helicopters.
Q5 For each location that has been used as the origin of a charter leg more than once, list the location as well as the number of times it has been used as an origin. Show location number, location name and the number of times the location has been used as an origin. The listing should be displayed in the ascending order of the number of times a location has been used as an origin. [8 marks]
Q6 List the number of hours flown for EVERY helicopter type. Show helicopter type number, helicopter type name and the total number of hours flown. Types which have no recorded hours should be shown as 0 hours flown. Page 4 of 9 The listing should be displayed in the ascending order of hours flown. [8 marks]
Q7 List all the completed charters that Frodo Baggins has flown (completed flights), with the most recent charters appearing at the top of the list. A completed charter means all legs have been flown. Show the charter number and the date/time of departure for leg one (note: a charter may span several days). [8 marks]
Q8 List those completed charters where the total cost is less than the average total cost for all charters. The total cost for a charter is obtained by multiplying the charter cost per hour with the actual duration obtained from the leg actual departure and actual arrival times. A completed charter means all legs have been flown. Show charter number, client number, client last name, client first name, total charter cost. The total charter cost should be rounded to two decimal digits and displayed with a $ symbol e.g. $1234.56 For this question, if either client name is empty '-' should be displayed. The listing should be displayed in descending order of total charter cost. Your output must have the form shown below (partial output only shown): [10 marks]
Q9 Which charters have been able to depart at the time estimated for all legs of its flight? Show the charter number, pilot’s name and the client’s name (both names should be shown in a single column). Order the output by a charter number. Your output must have the form shown below (partial output only shown): Page 5 of 9 [10 marks]
Q10 For each client, list the name of their favorite destination location/s and how many times they have visited that destination. The client's favorite destination will be the location/s they visit the most based on completed legs. Show the client number, client full name in one column, the name of the destination location and the number of times the location has been visited by the client. Order the output by the client number. Where a client has several favorite destinations, order them by destination name.
Applied Regression Analysis and Other Multivariable Methods
ISBN: 978-1285051086
5th edition
Authors: David G. Kleinbaum, Lawrence L. Kupper, Azhar Nizam, Eli S. Rosenberg