Flight Routes
Start Timer
0:00:00
You’re working as a data analyst at Delta Airlines, and you’re provided with two tables: flights and aircrafts.
Write a SQL query to list all flights scheduled between Mumbai ('BOM') and Delhi ('DEL') that use an aircraft whose capacity is greater than the average capacity of all aircrafts in the database.
Schema:
Input Tables:
flights
| Column | Type |
|---|---|
| flight_id | INTEGER |
| origin | VARCHAR |
| destination | VARCHAR |
| departure_time | DATETIME |
| arrival_time | DATETIME |
| aircraft_id | INTEGER |
aircrafts
| Column | Type |
|---|---|
| aircraft_id | INTEGER |
| model | VARCHAR |
| capacity | INTEGER |
Output:
| Column | Type |
|---|---|
| origin | VARCHAR |
| destination | VARCHAR |
| departure_time | DATETIME |
| aircraft_model | VARCHAR |
| capacity | INTEGER |
Example:
Input Tables:
flights
| flight_id | origin | destination | departure_time | arrival_time | aircraft_id |
|---|---|---|---|---|---|
| 1 | BOM | DEL | 2025-11-19 08:00 | 2025-11-19 10:00 | 201 |
| 2 | DEL | BLR | 2025-11-19 11:00 | 2025-11-19 13:00 | 202 |
aircrafts
| aircraft_id | model | capacity |
|---|---|---|
| 201 | A320 | 180 |
| 202 | B737 | 160 |
Output:
| origin | destination | departure_time | aircraft_model | capacity |
|---|---|---|---|---|
| BOM | DEL | 2025-11-19 08:00 | A320 | 180 |
Explanation:
The flight from BOM → DEL uses aircraft 201 with capacity 180, which is greater than the average capacity of all aircrafts. Hence, it is included in the output.
.
.
.
.
.
.
.
.
.
Comments