
CREATE TABLE airlines (
	alid INTEGER, 
	name TEXT, 
	iata VARCHAR(2), 
	icao VARCHAR(3), 
	callsign TEXT, 
	country TEXT, 
	active VARCHAR(2), 
	PRIMARY KEY (alid)
)


CREATE TABLE airports (
	apid INTEGER, 
	name TEXT NOT NULL, 
	city TEXT, 
	country TEXT, 
	x REAL, 
	y REAL, 
	elevation BIGINT, 
	iata TEXT(3), 
	icao TEXT(4), 
	PRIMARY KEY (apid)
)


CREATE TABLE routes (
	rid INTEGER, 
	dst_apid INTEGER, 
	dst_ap VARCHAR(4), 
	src_apid BIGINT, 
	src_ap VARCHAR(4), 
	alid BIGINT, 
	airline VARCHAR(4), 
	codeshare TEXT, 
	PRIMARY KEY (rid), 
	FOREIGN KEY(dst_apid) REFERENCES airports (apid), 
	FOREIGN KEY(src_apid) REFERENCES airports (apid), 
	FOREIGN KEY(alid) REFERENCES airlines (alid)
)

CREATE TABLE airlines (
	alid INTEGER, 
	name TEXT, 
	iata VARCHAR(2), 
	icao VARCHAR(3), 
	callsign TEXT, 
	country TEXT, 
	active VARCHAR(2), 
	PRIMARY KEY (alid)
)

/*
2 rows from airlines table:
alid	name	iata	icao	callsign	country	active
-1	Unknown	-	N/A	None	None	Y
1	Private flight	-	N/A			Y
*/


CREATE TABLE airports (
	apid INTEGER, 
	name TEXT NOT NULL, 
	city TEXT, 
	country TEXT, 
	x REAL, 
	y REAL, 
	elevation BIGINT, 
	iata TEXT(3), 
	icao TEXT(4), 
	PRIMARY KEY (apid)
)

/*
2 rows from airports table:
apid	name	city	country	x	y	elevation	iata	icao
1	Goroka Airport	Goroka	Papua New Guinea	145.391998291	-6.081689834590001	5282	GKA	AYGA
2	Madang Airport	Madang	Papua New Guinea	145.789001465	-5.20707988739	20	MAG	AYMD
*/


CREATE TABLE routes (
	rid INTEGER, 
	dst_apid INTEGER, 
	dst_ap VARCHAR(4), 
	src_apid BIGINT, 
	src_ap VARCHAR(4), 
	alid BIGINT, 
	airline VARCHAR(4), 
	codeshare TEXT, 
	PRIMARY KEY (rid), 
	FOREIGN KEY(dst_apid) REFERENCES airports (apid), 
	FOREIGN KEY(src_apid) REFERENCES airports (apid), 
	FOREIGN KEY(alid) REFERENCES airlines (alid)
)

/*
2 rows from routes table:
rid	dst_apid	dst_ap	src_apid	src_ap	alid	airline	codeshare
37	2990	KZ	2965	AER	410	2B	None
38	2990	KZ	2966	ASF	410	2B	None
*/
For the airline ids with the top 10 most routes operated, what are their names?
SELECT T1.name ,  T2.alid FROM airlines AS T1 JOIN routes AS T2 ON T1.alid  =  T2.alid GROUP BY T2.alid ORDER BY count(*) DESC LIMIT 10
[{'name': 'Ryanair', 'alid': 4296}, {'name': 'American Airlines', 'alid': 24}, {'name': 'United Airlines', 'alid': 5209}, {'name': 'Delta Air Lines', 'alid': 2009}, {'name': 'US Airways', 'alid': 5265}, {'name': 'China Southern Airlines', 'alid': 1767}, {'name': 'China Eastern Airlines', 'alid': 1758}, {'name': 'Air China', 'alid': 751}, {'name': 'Southwest Airlines', 'alid': 4547}, {'name': 'easyJet', 'alid': 2297}]