Task
Scenario: Airline Database Management
You have been hired as a database engineer to analyze and optimize the
flight and employee data for an airline company. You will work with the
following database schema:
Tables
Flights(flight_id, from_loc, to_loc, distance, price)
Aircraft(aircraft_id, aircraft_name, cruising_range)
Certified(employee_id, aircraft_id)
Employees(employee_id, employee_name, salary)
Your task is to write SQL queries to answer the following complex problems
using aggregate functions, nested subqueries, and advanced
filtering techniques.
Part A: Aggregate Functions
1. Calculate the average flight distance and list the flights whose
distance is greater than this average.
2. Find the employee with the highest salary and display their name
and salary.
3. Identify the aircraft with the longest cruising range and return its
details.
4. Display the total number of aircraft each employee is certified
to operate.
Part B: Complex Subqueries
5. Find the employee(s) who are certified to operate all available
aircraft models.
6. Identify flights that require an aircraft with a cruising range
longer than the longest aircraft available.
7. List the employees earning above the average salary in their
department, along with their department name.
8. Display the aircraft that is certified to the most employees.
Part C: Advanced Query Challenges
9. Find the top 3 employees who are certified for the most
aircraft, along with the count.
10. Display flights with a price above the average price for
flights of similar distance ranges (e.g., short-haul, medium-haul,
and long-haul flights).
11. For each aircraft, list the cheapest and most expensive
flight using it, showing flight details and ticket prices.
12. Identify employees who earn more than at least one
employee in the ‘Certified Pilots’ category but are not certified
for any aircraft.
