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
Find the number of routes operated by American Airlines.
assistant
SELECT count(*) FROM airlines AS T1 JOIN routes AS T2 ON T1.alid = T2.alid WHERE T1.name = 'American Airlines'
human
Which countries has the most number of airlines whose active status is 'Y'?
assistant
SELECT country FROM airlines WHERE active = 'Y' GROUP BY country ORDER BY count(*) DESC LIMIT 1
human
How many airlines does Russia has?
assistant
SELECT count(*) FROM airlines WHERE country = 'Russia'
human
Which countries has the most number of airlines?
assistant
SELECT country FROM airlines GROUP BY country ORDER BY count(*) DESC LIMIT 1
human
What is the total number of airlines?
assistant
SELECT COUNT( * ) FROM airlines ;