SQL for Airport Operations

Overview

This lecture explains how to use a UNION ALL SQL query to calculate the number of takeoffs and landings for each airport from flight manifest data and compute corresponding federal funding.

Problem Setup & Goals

• Task: Calculate takeoffs and landings for each airport using the manifest table.
• Funding is awarded per takeoff ($300) and per landing ($150) to airports.
• Results needed: counts for takeoffs, landings, and total funding per airport.

Relevant Data & Relationships

• Use the manifest table, joined with the flight table by flight number.
• Origin airport equals takeoff location; destination airport equals landing location.
• Flight number and flight date uniquely identify each flight occurrence.

SQL Query Construction Steps

• Start by selecting flight number, flight date, and origin for takeoffs.
• Use GROUP BY on flight number, flight date, and origin to aggregate departures.
• Assign 1 as takeoff count for each grouped row.
• Repeat similar logic for landings by selecting destination and assigning 1 as landing.
• Use UNION ALL to combine takeoff and landing datasets, ensuring column alignment.
• Represent columns as 1 for takeoff, 0 for landing in part a, and vice versa in part b.
• Alias the airport field as "airport code" for clarity in both queries.

Aggregating Results

• Select only airport code, takeoff, and landing columns from the UNION ALL output.
• GROUP BY airport code and sum takeoff and landing counts to get totals per airport.

Funding Computation

• Compute funding as: (takeoffs × $300) + (landings × $150) per airport.
• Format resulting funding amounts to display as currency.

Output Formatting & Sorting

• Use SQL formatting functions to show funding with dollar sign, commas, and two decimal places.
• Optionally, order results by funding amount as needed.

Key Terms & Definitions

• UNION ALL — SQL operator that combines results from multiple SELECT queries, including duplicates.
• GROUP BY — SQL clause used to aggregate results based on one or more columns.
• Alias — Renaming a column in SQL for clarity or formatting in results.
• Manifest Table — Database table containing details for each flight instance.

Action Items / Next Steps

• Practice constructing similar UNION ALL queries using bifurcated questions (separate logic for takeoffs and landings).
• Review and try to replicate the sequence of steps for other multi-part SQL problems.