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
How many airports are there?
assistant
SELECT COUNT( * ) FROM airport;
human
How many total companies are there?
assistant
SELECT COUNT( * ) FROM operate_company;
human
How many companies operate airlines in each airport? | Do you want the result by airport id, or airport name or all airport information? | I would like the results by airport id, please.
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 ;