Question-1
a) Display each aircraft’s number (heading: Aircraft No.), the aircraft’s model name (heading: Model Name), and the destinations the aircraft has flown to (heading: Destination).
b) For each aircraft and destination (that it has flown to), display the following statistics:
i. The number of trips (heading: Number of Trips)
ii. Total billable hours, which is the combination of hours flown plus hours wait (heading: Billable Hours, format: 9,999.99) across all trips
Note: each aircraft and destination pair should be in a separate line.
c) Only retain aircraft that have flown to STL or ATL at least once. Note: retain overall trip statistics for aircraft & destinations seen in part b). So, if an aircraft has flown to STL/ATL retain statistics for ALL its destinations.
d) Restrict your results to aircraft that have at least 9 (total) billable hours.
e) Sort the results by aircraft number.
Question-2
a) For each employee, display their employee number (heading: Employee Number), and their full name (combining the first and last name into a single output column, heading: Employee Name).
b) Additionally, display the following information related to each employee:
i. How many years have they have worked for AVIA (the number of years between today’s date and the hire date). Use a heading: Years Worked. Format to two decimal places.
ii. The total number of hours the employee has flown so far (heading: Hours Flown).
iii. The average trip distance for the employee (heading: Average Trip Distance).
iv. The number of different trips the employee has flown on (heading: Number of Trips).
c) Only include the information of employees who have a Number of Trips greater than or equal to the average number of trips AVIA employees have flown (i.e., average out the number of trips across all employees).
d) Sort the results by the Number of Trips (lowest first).
Hint: review how SYSDATE works
Question-3
a) Our goal is to get statistics for crew members who have worked together. For each pair of employees (who have worked together), display their employee numbers separately (heading: Employee1, Employee2). Also, show their job title (heading: Job1, Job2)
Organize the column headings as follows: Employee1, Job1, Employee2, Job2
b) Additionally, display the following information related to each pair of employees:
a. The number of charter trips they have flown together (heading: Num Flights Together).
b. The total distance they have flown together (heading: Total Distance, format: 9,999).
c) Restrict your results to cases where the Total Distance (see part b) above) is at least 1000 miles.
d) Sort the result by the first employee number.
e) You should ensure there’s no redundant information for each pair of employees and roles. For example, suppose your output has an entry for (employee01, Pilot, …, employee02, Copilot, …), then avoid showing an output row for the same pair reversed: (employee02, Copilot, …, employee01, Pilot, …) since that’s repeated data. Note: the same pair of employees in different roles is not considered repeated data. For example, (employee01, Pilot, employee02, Copilot) is different from (employee01, Copilot, employee02, Pilot).
Hint: consider using a WITH clause.
Sample Solution