human
Schema:
airport (id, City, Country, IATA, ICAO, name)
operate_company (id, name, Type, Principal_activities, Incorporated_in, Group_Equity_Shareholding)
flight (id, Vehicle_Flight_number, Date, Pilot, Velocity, Altitude, airport_id, company_id)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the distinct types of the companies that have operated any flights with velocity less than 200?
assistant
SELECT DISTINCT T1.type FROM operate_company AS T1 JOIN flight AS t2 ON T1.id = T2.company_id WHERE T2.velocity < 200
human
What are the different pilot names who had piloted a flight in the country 'United States' or in the airport named 'Billund Airport'?
assistant
SELECT DISTINCT T2.pilot FROM airport AS T1 JOIN flight AS T2 ON T1.id = T2.airport_id WHERE T1.country = 'United States' OR T1.name = 'Billund Airport'
human
which pilot is in charge of the most number of flights?
assistant
SELECT pilot FROM flight GROUP BY pilot ORDER BY count(*) DESC LIMIT 1
human
How many flights have a velocity larger than 200?
assistant
SELECT count(*) FROM flight WHERE velocity > 200
human
How many airports haven't the pilot 'Thompson' driven an aircraft?
assistant
SELECT count(*) FROM airport WHERE id NOT IN ( SELECT airport_id FROM flight WHERE pilot = 'Thompson' );
human
What is the velocity of the pilot named 'Thompson'?
assistant
SELECT avg ( velocity ) FROM flight WHERE pilot = "Thompson" ;