human
Schema:
pilot (Pilot_Id, Name, Age)
aircraft (Aircraft_ID, Aircraft, Description, Max_Gross_Weight, Total_disk_area, Max_disk_Loading)
match (Round, Location, Country, Date, Fastest_Qualifying, Winning_Pilot, Winning_Aircraft)
airport (Airport_ID, Airport_Name, Total_Passengers, %_Change_2007, International_Passengers, Domestic_Passengers, Transit_Passengers, Aircraft_Movements, Freight_Metric_Tonnes)
airport_aircraft (ID, Airport_ID, Aircraft_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the average total number of passengers of airports that are associated with aircraft 'Robinson R-22'?
assistant
SELECT avg(T3.Total_Passengers) FROM aircraft AS T1 JOIN airport_aircraft AS T2 ON T1.Aircraft_ID = T2.Aircraft_ID JOIN airport AS T3 ON T2.Airport_ID = T3.Airport_ID WHERE T1.Aircraft = 'Robinson R-22'
human
How many aircrafts are there?
assistant
SELECT count(*) FROM aircraft
human
Please show the names of aircrafts associated with airport with name 'London Gatwick'.
assistant
SELECT T1.Aircraft FROM aircraft AS T1 JOIN airport_aircraft AS T2 ON T1.Aircraft_ID = T2.Aircraft_ID JOIN airport AS T3 ON T2.Airport_ID = T3.Airport_ID WHERE T3.Airport_Name = 'London Gatwick'
human
What is the average number of international passengers of all airports?
assistant
SELECT avg(International_Passengers) FROM airport
human
What are the total number of Domestic Passengers of airports that contain the word 'London'.
assistant
SELECT sum(Domestic_Passengers) FROM airport WHERE Airport_Name LIKE '%London%'
human
How many international and domestic passengers are there in the airport London Heathrow?
assistant
SELECT International_Passengers , Domestic_Passengers FROM airport WHERE Airport_Name = "London Heathrow" ;