
CREATE TABLE airport (
	id INTEGER, 
	"City" TEXT, 
	"Country" TEXT, 
	"IATA" TEXT, 
	"ICAO" TEXT, 
	name TEXT, 
	PRIMARY KEY (id)
)


CREATE TABLE flight (
	id INTEGER, 
	"Vehicle_Flight_number" TEXT, 
	"Date" TEXT, 
	"Pilot" TEXT, 
	"Velocity" REAL, 
	"Altitude" REAL, 
	airport_id INTEGER, 
	company_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(company_id) REFERENCES operate_company (id), 
	FOREIGN KEY(airport_id) REFERENCES airport (id)
)


CREATE TABLE operate_company (
	id INTEGER, 
	name TEXT, 
	"Type" TEXT, 
	"Principal_activities" TEXT, 
	"Incorporated_in" TEXT, 
	"Group_Equity_Shareholding" REAL, 
	PRIMARY KEY (id)
)

CREATE TABLE airport (
	id INTEGER, 
	"City" TEXT, 
	"Country" TEXT, 
	"IATA" TEXT, 
	"ICAO" TEXT, 
	name TEXT, 
	PRIMARY KEY (id)
)

/*
2 rows from airport table:
id	City	Country	IATA	ICAO	name
1	Akureyri	Iceland	AEY	BIAR	Akureyri Airport
2	Amsterdam	Netherlands	AMS	EHAM	Schiphol Airport
*/


CREATE TABLE flight (
	id INTEGER, 
	"Vehicle_Flight_number" TEXT, 
	"Date" TEXT, 
	"Pilot" TEXT, 
	"Velocity" REAL, 
	"Altitude" REAL, 
	airport_id INTEGER, 
	company_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(company_id) REFERENCES operate_company (id), 
	FOREIGN KEY(airport_id) REFERENCES airport (id)
)

/*
2 rows from flight table:
id	Vehicle_Flight_number	Date	Pilot	Velocity	Altitude	airport_id	company_id
1	M2-F1 #0	March 1, 1963	Thompson	135.0	0.0	1	2
2	M2-F1 #1	August 16, 1963	Thompson	240.0	3650.0	2	3
*/


CREATE TABLE operate_company (
	id INTEGER, 
	name TEXT, 
	"Type" TEXT, 
	"Principal_activities" TEXT, 
	"Incorporated_in" TEXT, 
	"Group_Equity_Shareholding" REAL, 
	PRIMARY KEY (id)
)

/*
2 rows from operate_company table:
id	name	Type	Principal_activities	Incorporated_in	Group_Equity_Shareholding
1	Air China	Corporate	Airline	China	18.77
2	Air China Cargo	Joint Venture	Cargo airline	China	49.0
*/
What is the velocity of the pilot named 'Thompson'?
SELECT avg(velocity) FROM flight WHERE pilot  =  'Thompson'
[{'avg(velocity)': 229.5}]