サブクエリの習作 まーくつー

うええええあああああ

っとなりまして、

1. 公用語が英語の国のデータをとる
2. そのなかでGNPが上位5位以内の国のデータをとる
3. さらにそのなかで人口が上位15位以内の都市のデータをとる

mysql> select * from City where City.CountryCode in (select Code from Country where Code in (select CountryCode from CountryLanguage where Language = 'English' and IsOfficial = 'T') order by GNP desc) order by City.Population desc limit 15;
+------+-------------------------+-------------+----------------------+------------+
| ID   | Name                    | CountryCode | District             | Population |
+------+-------------------------+-------------+----------------------+------------+
| 3793 | New York                | USA         | New York             |    8008278 |
|  456 | London                  | GBR         | England              |    7285000 |
| 3794 | Los Angeles             | USA         | California           |    3694820 |
|  130 | Sydney                  | AUS         | New South Wales      |    3276207 |
| 3795 | Chicago                 | USA         | Illinois             |    2896016 |
|  131 | Melbourne               | AUS         | Victoria             |    2865329 |
|  712 | Cape Town               | ZAF         | Western Cape         |    2352121 |
|  936 | Kowloon and New Kowloon | HKG         | Kowloon and New Kowl |    1987996 |
| 3796 | Houston                 | USA         | Texas                |    1953631 |
| 3797 | Philadelphia            | USA         | Pennsylvania         |    1517550 |
| 4068 | Harare                  | ZWE         | Harare               |    1410000 |
| 3798 | Phoenix                 | USA         | Arizona              |    1321045 |
|  937 | Victoria                | HKG         | Hongkong             |    1312637 |
|  132 | Brisbane                | AUS         | Queensland           |    1291117 |
| 3799 | San Diego               | USA         | California           |    1223400 |
+------+-------------------------+-------------+----------------------+------------+

サンプルデータ 世界 - by shigemk2

僕にはこれが限度であった。