A SQL case study using flight and aircraft tables to answer route-capacity questions with joins, filters, aggregates, and grouped results.
This project uses SQL to connect flight schedules with aircraft seating capacity, then answer practical questions about how many seats arrive at SFO from key routes and which aircraft types create the most capacity.
If an airport, airline, or travel product team wanted to understand flight capacity into SFO, which routes and aircraft types would matter most? I used SQL to connect each flight to its aircraft seat count, then answered questions about total seats, maximum aircraft capacity, and flight frequency by aircraft type.
Click a query to see the same analysis logic run against a small embedded sample of the flight and aircraft data.
SELECT a query above to run it.| Waiting | Choose a query. |
The demo is ready. It calculates results in the browser from sample route data.
| Table | What It Contains | Important Columns |
|---|---|---|
| flights | One row per flight in the dataset. | Origin, Destination, Flight, Aircraft, Stops, duration |
| aircrafts | Aircraft code lookup table with seating capacity. | Aircraft, Seats |
from sqlalchemy import create_engine, text
engine = create_engine("sqlite:///:memory:")
conn = engine.connect()
flights_df.to_sql("flights", conn, index=False, if_exists="replace")
aircrafts_df.to_sql("aircrafts", conn, index=False, if_exists="replace")
conn.execute(text("SELECT COUNT(*) FROM flights")).fetchone()
conn.execute(text("SELECT COUNT(*) FROM aircrafts")).fetchone()| Flights table | 893 rows |
| Aircrafts table | 34 rows |
The first step was validating that both tables loaded correctly. This matters because every later query depends on joining flight records to the aircraft seat lookup table.
SELECT
COUNT(*) AS Flights,
SUM(a.Seats) AS Total_Seats,
ROUND(AVG(a.Seats), 1) AS Avg_Seats
FROM flights f
JOIN aircrafts a
ON f.Aircraft = a.Aircraft
WHERE f.Origin = 'LHR'
AND f.Destination = 'SFO';| Flights | Total Seats | Average Seats |
|---|---|---|
| 9 | 2,935 | 326.1 |
LHR to SFO is a high-capacity international route in this dataset. The average aircraft has more than 300 seats, which suggests this route is served by wide-body aircraft rather than smaller domestic planes.
SELECT
TRIM(f.Flight) AS Flight,
f.Aircraft,
a.Seats
FROM flights f
JOIN aircrafts a
ON f.Aircraft = a.Aircraft
WHERE f.Origin = 'LHR'
AND f.Destination = 'SFO'
ORDER BY a.Seats DESC, Flight;| Flight | Aircraft | Seats |
|---|---|---|
| BA 285 | 747 | 347 |
| BA 287 | 747 | 347 |
| CO 8239 | 744 | 347 |
| SQ 2519 | 744 | 347 |
| VS 019 | 744 | 347 |
| NZ 9831 | 777 | 300 |
| NZ 9855 | 777 | 300 |
| UA 931 | 777 | 300 |
| UA 955 | 777 | 300 |
The capacity is split between 747/744 aircraft with 347 seats and 777 aircraft with 300 seats. Showing the detail rows makes the total explainable instead of leaving it as a black-box number.
SELECT
f.Aircraft,
a.Seats,
f.Origin,
COUNT(*) AS Flights
FROM flights f
JOIN aircrafts a
ON f.Aircraft = a.Aircraft
WHERE f.Destination = 'SFO'
AND f.Origin IN ('LHR', 'FRA')
GROUP BY f.Aircraft, a.Seats, f.Origin
ORDER BY a.Seats DESC, Flights DESC
LIMIT 5;| Aircraft | Seats | Origin | Flights |
|---|---|---|---|
| 744 | 347 | FRA | 6 |
| 744 | 347 | LHR | 3 |
| 747 | 347 | LHR | 2 |
| 777 | 300 | LHR | 4 |
The largest aircraft serving SFO from these two origins have 347 seats. Frankfurt appears especially important for high-capacity 744 service in this dataset.
SELECT DISTINCT
f.Aircraft,
a.Seats
FROM flights f
JOIN aircrafts a
ON f.Aircraft = a.Aircraft
WHERE f.Destination = 'SFO'
AND f.Aircraft IN ('318', '319', '320', '32S', '346')
ORDER BY a.Seats DESC;| Aircraft | Seats |
|---|---|
| 346 | 300 |
| 320 | 150 |
| 32S | 150 |
| 319 | 124 |
A first-pass aircraft-family filter used codes containing the number 3. An explicit code list is cleaner because it avoids accidentally including unrelated aircraft codes.
SELECT
Aircraft,
COUNT(*) AS Number_of_Flights
FROM flights
WHERE Origin = 'LAX'
AND Destination = 'SFO'
GROUP BY Aircraft
ORDER BY Number_of_Flights DESC, Aircraft;| Aircraft | Number of Flights |
|---|---|
| 752 | 19 |
| 319 | 14 |
| 320 | 14 |
| 735 | 14 |
| M80 | 13 |
| M83 | 8 |
| 733 | 7 |
| 734 | 3 |
| CRJ | 3 |
| 737 | 2 |
| 739 | 1 |
The LAX to SFO route has 98 total flights in this dataset. Aircraft 752 appears most often, but frequency alone does not fully explain seat capacity, so I added a capacity-weighted query next.
SELECT
f.Aircraft,
COUNT(*) AS Flights,
a.Seats,
COUNT(*) * a.Seats AS Estimated_Seat_Capacity
FROM flights f
JOIN aircrafts a
ON f.Aircraft = a.Aircraft
WHERE f.Origin = 'LAX'
AND f.Destination = 'SFO'
GROUP BY f.Aircraft, a.Seats
ORDER BY Estimated_Seat_Capacity DESC;| Aircraft | Flights | Seats | Estimated Capacity |
|---|---|---|---|
| 752 | 19 | 182 | 3,458 |
| 320 | 14 | 150 | 2,100 |
| M80 | 13 | 136 | 1,768 |
| 319 | 14 | 124 | 1,736 |
| 735 | 14 | 104 | 1,456 |
This is the strongest portfolio query because it turns a simple count into an operational insight. The 752 aircraft contributes the most estimated seat capacity on LAX to SFO because it combines high frequency with a larger seat count.