AviaCo Database: SQL Practice Exercises
Query Script: Ch08_AviaCo_MySQL.txt
Welcome to the AviaCo Data Analytics Team! AviaCo is a premier aircraft charter
company. As our newest database developer, you have been tasked with extracting
critical business intelligence from our operational database.
You will use MySQL Workbench to write queries that answer the specific business
questions below.
Database Schema Reference
To help you navigate, here is a quick overview of the tables in your database:
• AIRCRAFT: Specific airplanes owned by AviaCo.
• CHARTER: The log of all chartered flight trips, including distances and fuel used.
• CREW: Which employees were assigned to which charter trips.
• CUSTOMER: Client contact and balance information.
• EARNEDRATING: Specific flight certifications earned by our staff.
• EMPLOYEE: General staff demographic and hiring data.
• MODEL: Specifications for different types of aircraft (seats, charge per mile).
• PILOT: Specific pilot license and medical clearance data.
• RATING: The official names of the flight certification codes.
Phase 1: Database Exploration
Objective: Verify your data and practice basic filtering.
1. Verify Tables: Write a query to view all columns and all rows in the MODEL table.
2. Basic Filtering: The marketing team wants to run a local promotion and needs
to contact customers in the 615 area code. Write a query to select the first name,
last name, and phone number of these customers from the CUSTOMER table.
Phase 2: Relational Joins
Objective: Connect primary and foreign keys across multiple tables.
3. Two-Table Join: We need a basic flight manifest. Write a query to display the
Charter Trip ID, Date, Destination, and the Customer's Last Name. (Hint: You will
need the CHARTER and CUSTOMER tables).
4. Three-Table Join: Let's look at aircraft utilization. List the Charter Trip ID, the
Aircraft Number, and the Aircraft's Model Name. (Hint: You will need CHARTER,
AIRCRAFT, and MODEL).
5. Filtering with Joins: Who flew trip 10004? Display the trip number, employee
first name, last name, and their job title on that specific charter. (Hint: Join CREW
and EMPLOYEE).
Phase 3: Aggregation & Grouping
Objective: Summarize data using aggregate functions.
6. Basic Aggregation: Management wants a high-level overview of our fleet's
output. What is the total distance flown and total fuel used across all recorded
charters? (Hint: Use the CHARTER table).
7. Grouping Data: We want to identify our most frequent flyers. How many trips did
each customer take? Display the Customer Code, Last Name, and the total count
of their trips. (Hint: Join CUSTOMER and CHARTER).
8. Having Clause: Which aircraft models are being used for longer hauls? Write a
query to find the Aircraft Number and its average distance flown, but only display
those that average more than 500 miles per charter.
Phase 4: Advanced SQL (Subqueries & Calculations)
Objective: Perform cross-table math and nested queries.
9. Subqueries: Find the first and last names of all employees who are officially
listed as pilots in the system. Constraint: You must use a subquery rather than a
JOIN to achieve this. (Hint: Reference EMPLOYEE and PILOT).
10. Calculated Columns: AviaCo charges customers by the mile. The charge per
mile is stored in the MODEL table, and the distance flown is in the CHARTER table.
Write a query to calculate the expected revenue for each charter trip (Distance
multiplied by the Charge per Mile). Display the Trip ID, Distance, Charge per
Mile, and the newly calculated "Trip_Revenue".
11. Complex Analysis: Which pilot has flown the most total hours as a 'Pilot'
(exclude hours flown as a 'Copilot')? Display their Employee Number, Last
Name, and the Total Hours flown. (Hint: This requires EMPLOYEE, CREW, and
CHARTER. Sort your results to show the top pilot).