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
Please show the names and descriptions of aircrafts associated with airports that have a total number of passengers bigger than 10000000.
assistant
SELECT T1.Aircraft , T1.Description 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.Total_Passengers > 10000000
human
Show the names of aircrafts that are associated with both an airport named 'London Heathrow' and an airport named '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 Heathrow' INTERSECT 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
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 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
What are the number of international and domestic passengers of the airport named London 'Heathrow'?
assistant
SELECT International_Passengers , Domestic_Passengers FROM airport WHERE Airport_Name = "London Heathrow" ;