This project analyzes airline reservation data using SQL to understand flight operations, ticket pricing, passenger behavior, and revenue performance.
A relational database was designed with multiple interconnected tables to simulate a real-world airline booking system.
The project demonstrates advanced SQL concepts including joins, aggregations, window functions, and subqueries.
The system consists of the following tables:
- Airlines
- Flights
- Passengers
- Reservations
Relationships were established using primary keys and foreign keys to maintain data integrity.
- CREATE TABLE
- INSERT INTO
- SELECT statements
- WHERE, ORDER BY, GROUP BY
- INNER JOIN
- Aggregate Functions (SUM, COUNT, AVG)
- Window Functions (RANK)
- Subqueries
- Filtering & Sorting
- Identified top-rated airlines
- Analyzed flights departing from specific cities
- Evaluated ticket price distribution
- Calculated total revenue from confirmed bookings
- Ranked airlines by fleet size
- Identified high-value passengers
- Compared ticket prices above average
- Revenue is primarily driven by confirmed bookings.
- Business class bookings generate higher ticket revenue.
- Certain airlines dominate fleet size and route coverage.
- Flights priced above average often belong to premium routes.
This analysis helps:
- Evaluate airline performance
- Optimize pricing strategy
- Identify profitable routes
- Understand passenger booking behavior
- Support data-driven decision-making in airline operations
The complete database schema, data insertion queries, and analytical queries are included in the .sql file.