BasicSELECT & Filtering10 points

Long-haul legs over 9,000 km

The crew planning team at an international airline must schedule extra rest for pilots on very long sectors. Union rules apply to any single leg longer than 9,000 km, and the team needs the list of those legs from the route network.

Task: from routes, return origin, dest and distance_km for every route with a distance strictly greater than 9000. Sort by distance_km from longest to shortest, then by origin A to Z, then by dest A to Z.

The data you can query

Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.

aircraft14 rows

id INTEGER	airline TEXT	model TEXT	seats INTEGER	delivered_year INTEGER
1	SkyMeridian	A320	180	2017
2	SkyMeridian	A320	180	2019
3	SkyMeridian	A321	200	2021
4	SkyMeridian	B787-9	290	2018
5	SkyMeridian	A350-900	320	2022
6	SkyMeridian	B787-9	290	2020

Showing the first 6 of 14 rows.

airports12 rows

code TEXT	name TEXT	city TEXT	country TEXT
JFK	John F. Kennedy International	New York	USA
LHR	Heathrow	London	United Kingdom
DXB	Dubai International	Dubai	UAE
SIN	Changi	Singapore	Singapore
LHE	Allama Iqbal International	Lahore	Pakistan
ISB	Islamabad International	Islamabad	Pakistan

Showing the first 6 of 12 rows.

bookings557 rows

id INTEGER	flight_id INTEGER	passenger_id INTEGER	cabin TEXT	fare_usd INTEGER
1	1	14	Economy	230
2	1	38	Economy	180
3	2	1	Economy	380
4	2	22	Economy	400
5	2	45	Economy	445
6	3	1	Economy	450

Showing the first 6 of 557 rows.

flights260 rows

id INTEGER	flight_no TEXT	route_id INTEGER	aircraft_id INTEGER	sched_dep TEXT	dep_delay_min INTEGER	status TEXT
1	FC185	3	12	2025-01-02 07:15:00	0	Completed
2	OW370	8	10	2025-01-03 05:30:00	40	Completed
3	SM489	35	5	2025-01-03 08:00:00	25	Completed
4	SM415	33	6	2025-01-03 14:45:00	10	Completed
5	OW592	14	9	2025-01-08 16:00:00	50	Completed
6	SM222	4	2	2025-01-09 08:45:00	35	Completed

Showing the first 6 of 260 rows.

passengers45 rows

id INTEGER	name TEXT	home_country TEXT	loyalty_tier TEXT
1	Ayesha Khan	Pakistan	Silver
2	Bilal Okafor	Australia	Silver
3	Chen Silva	Germany	NULL
4	Diego Tanaka	Australia	Silver
5	Elena Weber	Germany	NULL
6	Farid Nguyen	Australia	Gold

Showing the first 6 of 45 rows.

routes40 rows

id INTEGER	origin TEXT	dest TEXT	distance_km INTEGER
1	LHE	DXB	2100
2	DXB	LHE	2100
3	ISB	DXB	2300
4	DXB	ISB	2300
5	DXB	LHR	5500
6	LHR	DXB	5500

Showing the first 6 of 40 rows.

Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.

Need a hint?

A route is one direction only, so a pair of airports can appear twice.

SQL · SQLite

10 pts

Loading editor...

Run your code to see the output here. Ctrl + Enter also runs it.