Interview Query
Back to SQL Interview
SQL Interview

SQL Interview

27 of 56 Completed

Select All Flights
Go to question details page

Problem Statement

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.

Your output should include:

  • origin
  • destination
  • departure_time
  • aircraft_model
  • capacity

Example 1

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

Example 2

Input Data

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.

Note: Date-times are formatted in YYYY-MM-DD HH:MM

SELECT and WHERE
Like Tracker
Good job, keep it up!

48%

Completed

You have 29 sections remaining on this learning path.