Overview
This lecture demonstrates how to use nested SQL queries to find the most popular flights and departure dates for passengers with 10,000 or fewer frequent flyer miles during Q1 of 2017.
Identifying Query Requirements
- The question focuses on passengers with ≤10,000 cumulative frequent flyer miles.
- Only flights departing in Q1 of 2017 (January 1 to March 31) are relevant.
- Output should show the most popular flight numbers and dates among these passengers.
Building the Nested Query
- Start with the frequent flyer table to select FF numbers for passengers with ≤10,000 miles.
- Use a nested SELECT to move from the frequent flyer table to the passenger table, getting passenger numbers.
- Nest another SELECT to move from the passenger table to the manifest table, retrieving flight number and flight date.
- Apply a WHERE clause in the manifest table to restrict flight dates to Q1 2017 (between '2017-01-01' and '2017-03-31').
Grouping and Counting Results
- Use COUNT in the SELECT statement to determine the number of passengers per flight and date.
- Utilize GROUP BY on flight number and flight date to aggregate passenger counts for each unique flight-date pair.
- Alias the count column as "passenger count" for clarity.
Key Terms & Definitions
- Nested Query — A SQL query placed within another query, often in the WHERE clause, to filter results.
- Foreign Key — A field in one table that links to a primary key in another table.
- Primary Key — A unique identifier for records in a database table.
- Manifest Table — Database table containing details about each flight, including passenger numbers, flight numbers, and dates.
- GROUP BY — SQL clause used to aggregate results by specific columns.
Action Items / Next Steps
- Practice building and running nested queries with different constraints.
- Prepare for the next nested query example in the following lecture.