
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
*/
Find the name of the airport in the city of Goroka.
SELECT name FROM airports WHERE city  =  'Goroka'
[{'name': 'Goroka Airport'}]