演習: world データベース
world データベースで練習します。最初に USE world; で切り替えてください。
テーブル情報
Section titled “テーブル情報”country — 239 カ国の情報
| カラム名 | 型 | 説明 |
|---|---|---|
Code | CHAR(3) | 国コード（主キー） |
Name | CHAR(52) | 国名 |
Continent | ENUM | 大陸（Asia, Europe, North America, South America, Africa, Oceania, Antarctica） |
Region | CHAR(26) | 地域 |
SurfaceArea | DECIMAL(10,2) | 面積（km²） |
IndepYear | SMALLINT | 独立年（NULL あり） |
Population | INT | 人口 |
LifeExpectancy | DECIMAL(3,1) | 平均寿命（NULL あり） |
GNP | DECIMAL(10,2) | 国民総生産（NULL あり） |
city — 都市の情報
| カラム名 | 型 | 説明 |
|---|---|---|
ID | INT | 都市 ID（主キー） |
Name | CHAR(35) | 都市名 |
CountryCode | CHAR(3) | 国コード（外部キー → country.Code） |
District | CHAR(20) | 地区 |
Population | INT | 人口 |
countrylanguage — 言語の情報
| カラム名 | 型 | 説明 |
|---|---|---|
CountryCode | CHAR(3) | 国コード（外部キー → country.Code） |
Language | CHAR(30) | 言語名 |
IsOfficial | ENUM('T','F') | 公用語かどうか |
Percentage | DECIMAL(4,1) | 話者の割合 |
1. 都市名から都市情報を調べる
Section titled “1. 都市名から都市情報を調べる”city テーブルから、都市名が 'Tokyo' の都市について、国コード、地区、人口を表示してください。
期待される結果
| CountryCode | District | Population |
|---|---|---|
| JPN | Tokyo-to | 7980230 |
ヒント
WHERE 列 = 値 で値の一致を判定します。文字列値はシングルクォートで囲みます。
2. 国名の末尾で絞り込む
Section titled “2. 国名の末尾で絞り込む”country テーブルから、国名が 'land' で終わる国の国名と人口を、人口の多い順に表示してください。
期待される結果
| Name | Population |
|---|---|
| Thailand | 61399000 |
| Poland | 38653600 |
| Switzerland | 7160400 |
| Finland | 5171300 |
| New Zealand | 3862000 |
| Ireland | 3775100 |
| Swaziland | 1008000 |
| Iceland | 279000 |
| Greenland | 56000 |
| Christmas Island | 2500 |
| Norfolk Island | 2000 |
| Bouvet Island | 0 |
ヒント
LIKE '%パターン' で後方一致を表せます。% は 0 文字以上の任意の文字列にマッチします。
3. 独立が新しい国を平均寿命付きで並べる
Section titled “3. 独立が新しい国を平均寿命付きで並べる”country テーブルから、独立年が 1992 年以降で、かつ平均寿命（LifeExpectancy）が記録されている国について、国名、独立年、平均寿命を、独立年の古い順、同じ独立年の中では国名の昇順に表示してください。
期待される結果
| Name | IndepYear | LifeExpectancy |
|---|---|---|
| Bosnia and Herzegovina | 1992 | 71.5 |
| Czech Republic | 1993 | 74.5 |
| Eritrea | 1993 | 55.8 |
| Slovakia | 1993 | 73.7 |
| Palau | 1994 | 68.6 |
ヒント
>= で「以上」を判定します。NULL でないことは IS NOT NULL で判定します（!= NULL や <> NULL は正しく動作しません）。複数の条件は AND で組み合わせ、ORDER BY はカンマ区切りで複数のキーを指定できます。
4. 大陸ごとの平均面積を調べる
Section titled “4. 大陸ごとの平均面積を調べる”country テーブルから、大陸ごとの平均面積を小数第 2 位で四捨五入した値を、平均面積の大きい順に表示してください。平均面積のカラム名は avg_area とします。
期待される結果
| Continent | avg_area |
|---|---|
| Antarctica | 2626420.20 |
| South America | 1276066.14 |
| North America | 654445.14 |
| Asia | 625117.75 |
| Africa | 521558.22 |
| Europe | 501068.13 |
| Oceania | 305867.64 |
ヒント
GROUP BY 列 で同じ値の行をひとつのグループにまとめ、集計関数でグループ単位の値を求めます。ROUND(値, 桁数) で小数点以下の桁数を指定して四捨五入できます。ORDER BY には AS で付けたエイリアス名を直接使えます。
5. 有力な言語を多く持つ国を調べる
Section titled “5. 有力な言語を多く持つ国を調べる”countrylanguage テーブルから、話者割合（Percentage）が 10% 以上の言語を対象として、そのような言語が 4 つ以上記録されている国コードを、該当言語数の多い順、同数の場合は国コードの昇順に表示してください。該当言語数のカラム名は lang_count とします。
期待される結果
| CountryCode | lang_count |
|---|---|
| KEN | 5 |
| CIV | 4 |
| COD | 4 |
| GAB | 4 |
| NGA | 4 |
ヒント
WHERE は行単位の絞り込み、HAVING はグループ化された後の絞り込みです。先に WHERE Percentage >= 10 で行を絞り、GROUP BY CountryCode でまとめてから、HAVING COUNT(*) >= 4 でグループを絞ります。
6. 北米の国ごとの都市人口合計を調べる
Section titled “6. 北米の国ごとの都市人口合計を調べる”country と city を結合し、北米大陸（North America）に属する国ごとに、その国に登録されている都市の人口合計を、人口合計の多い順に上位 10 件表示してください。国名と人口合計を出力し、人口合計のカラム名は total_city_pop とします。
期待される結果
| Name | total_city_pop |
|---|---|
| United States | 78625774 |
| Mexico | 59752521 |
| Canada | 12673840 |
| Cuba | 4629925 |
| Dominican Republic | 2438276 |
| Puerto Rico | 1564174 |
| Haiti | 1517338 |
| Honduras | 1287000 |
| Nicaragua | 1269223 |
| Guatemala | 1225188 |
ヒント
country と city を ON country.Code = city.CountryCode で結合します。大陸の絞り込みを WHERE で行い、国ごとに GROUP BY してから SUM(city.Population) を計算します。上位 N 件は ORDER BY ... DESC LIMIT N で取り出します。