-- Q08 Compute the number of airports per EU member states, sorted first by number of airports descending, second by country ascending. (return country name, number of airports)
----
-- "France"          "55"
-- "United Kingdom"  "49"
-- "Spain"           "40"
-- "Sweden"          "37"
-- "Italy"           "36"
-- "Germany"         "31"
-- "Greece"          "30"
-- "Finland"         "19"
-- "Romania"         "12"
-- "Poland"          "11"
-- ...

SELECT C2.Name, count(*)
  FROM Airports A, Cities C1, Countries C2
  WHERE A.CityID = C1.CityID
    AND C1.CountryID = C2.CountryID
    AND C2.Name IN (
      'Austria', 'Belgium' ,'Bulgaria','Croatia',
      'Cyprus','Czech Republic','Denmark','Estonia',
      'Finland','France','Germany','Greece','Hungary',
      'Ireland','Italy','Latvia','Lithuania','Luxembourg',
      'Malta','Netherlands','Poland','Portugal','Romania',
      'Slovakia','Slovenia','Spain','Sweden','United Kingdom')
  GROUP BY C2.Name
  ORDER BY count(*) DESC, C2.Name ASC
