human
Schema:
routes (rid, dst_apid, dst_ap, src_apid, src_ap, alid, airline, codeshare)
airports (apid, name, city, country, x, y, elevation, iata, icao)
airlines (alid, name, iata, icao, callsign, country, active)
Please answer with SQL.
assistant
Sure, I understand.
human
List all countries and their number of airlines in the descending order of number of airlines.
assistant
SELECT country , count(*) FROM airlines GROUP BY country ORDER BY count(*) DESC
human
List the cities which have more than one airport and number of airports.
assistant
SELECT city , count(*) FROM airports GROUP BY city HAVING count(*) > 1
human
Find the number of routes and airport name for each source airport, order the results by decreasing number of routes.
assistant
SELECT count(*) , T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid = T2.src_apid GROUP BY T1.name ORDER BY count(*) DESC
human
List the cities which have more than 2 airports sorted by the number of airports.
assistant
SELECT city FROM airports GROUP BY city HAVING count(*) > 2 ORDER BY count(*)
human
How many airports are there per city in the United States? Order the cities by decreasing number of airports.
assistant
SELECT count(*) , city FROM airports WHERE country = 'United States' GROUP BY city ORDER BY count(*) DESC
human
How many airports are there per country? Order the countries by decreasing number of airports.
assistant
SELECT COUNT( * ) , country FROM airports GROUP BY country ORDER BY COUNT( * ) DESC ;