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 is the id, name and IATA code of the airport that had most number of flights?
assistant
SELECT T1.id , T1.name , T1.IATA FROM airport AS T1 JOIN flight AS T2 ON T1.id = T2.airport_id GROUP BY T2.id ORDER BY count(*) DESC LIMIT 1
human
List the name of the pilots who have flied for both a company that mainly provide 'Cargo' services and a company that runs 'Catering services' activities.
assistant
SELECT T2.pilot FROM operate_company AS T1 JOIN flight AS t2 ON T1.id = T2.company_id WHERE T1.principal_activities = 'Cargo' INTERSECT SELECT T2.pilot FROM operate_company AS T1 JOIN flight AS t2 ON T1.id = T2.company_id WHERE T1.principal_activities = 'Catering services'
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
How many companies operates airlines in each airport?
assistant
SELECT T3.id , count(*) FROM operate_company AS T1 JOIN flight AS t2 ON T1.id = T2.company_id JOIN airport AS T3 ON T2.airport_id = T3.id GROUP BY T3.id
human
What are the names and types of the companies that have ever operated a flight?
assistant
SELECT T1.name , T1.type FROM operate_company AS T1 JOIN flight AS t2 ON T1.id = T2.company_id
human
What are the ids and names of the companies that operated more than one flight?
assistant
SELECT T1.id , T1.name FROM operate_company AS T1 JOIN flight AS t2 ON T1.id = T2.company_id GROUP BY T1.id HAVING COUNT( * ) > 1 ;