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
Show me the names of all the airports
assistant
SELECT name FROM airport;
human
What are the number of flights for each of these airports?
assistant
SELECT name , COUNT( * ) FROM airport AS t1 JOIN flight AS t2 ON t1.id = t2.airport_id GROUP BY t1.id;
human
Which airport had the most number of flights?
assistant
SELECT t1.name FROM airport AS t1 JOIN flight AS t2 ON t1.id = t2.airport_id GROUP BY t1.id ORDER BY COUNT( * ) DESC LIMIT 1;
human
Show me the id and IATA code of this airport as well.
assistant
SELECT T1.id , T1.name , T1.IATA FROM airport AS t1 JOIN flight AS t2 ON t1.id = t2.airport_id GROUP BY t1.id ORDER BY COUNT( * ) DESC LIMIT 1 ;