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 names of the airports which are not in the country 'Iceland'?
assistant
SELECT name FROM airport WHERE country != 'Iceland'
human
how many airports are there in each country?
assistant
SELECT count(*) , country FROM airport GROUP BY country
human
which countries have more than 2 airports?
assistant
SELECT country FROM airport GROUP BY country HAVING count(*) > 2
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 id, country, city and name of the airports ordered alphabetically by the name.
assistant
SELECT id , country , city , name FROM airport ORDER BY name
human
Which of the airport names contains the word 'international'?
assistant
SELECT name FROM airport WHERE name LIKE "%international%" ;