MySQL 公式サンプルデータを使った課題
課題１．city table から、すべての Name と CountryCode を取得し、最初は CountryCode で次は Name で並び替える
city table のスキーマを確認する。
mysql> desc city; +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | MUL | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
回答
order by で CountryCode,Name を指定する。
mysql> select Name,CountryCode from city order by CountryCode,Name; (結果セットは長いため省略)
課題２．countrylanguage table から、Language が English であり、Percentage が 10.0 を超えるすべての CountryCode,Language,IsOfficial,Percentage を取得する
countrylanguage table のスキーマを確認する。
mysql> desc countrylanguage;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3) | NO | PRI | | |
| Language | char(30) | NO | PRI | | |
| IsOfficial | enum('T','F') | NO | | F | |
| Percentage | decimal(4,1) | NO | | 0.0 | |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)回答
where で Language と Percentage を条件通りに指定する。
mysql> select * from countrylanguage where Language = 'English' and Percentage > 10.0; +-------------+----------+------------+------------+ | CountryCode | Language | IsOfficial | Percentage | +-------------+----------+------------+------------+ | AUS | English | T | 81.2 | | BLZ | English | T | 50.8 | | BMU | English | T | 100.0 | | CAN | English | T | 60.4 | | GBR | English | T | 97.3 | | GIB | English | T | 88.9 | | GUM | English | T | 37.5 | | IRL | English | T | 98.4 | | LCA | English | T | 20.0 | | NZL | English | T | 87.0 | | PRI | English | F | 47.4 | | TTO | English | F | 93.5 | | USA | English | T | 86.2 | | VIR | English | T | 81.7 | | VUT | English | T | 28.3 | +-------------+----------+------------+------------+ 15 rows in set (0.00 sec)
課題３．２で得られた結果に、CountryName を country table から取得し加える
country table のスキーマを確認する。
countrylanguage table と、Code で JOIN ができそう。
mysql> desc country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO | | Asia | |
| Region | char(26) | NO | | | |
| SurfaceArea | decimal(10,2) | NO | | 0.00 | |
| IndepYear | smallint(6) | YES | | NULL | |
| Population | int(11) | NO | | 0 | |
| LifeExpectancy | decimal(3,1) | YES | | NULL | |
| GNP | decimal(10,2) | YES | | NULL | |
| GNPOld | decimal(10,2) | YES | | NULL | |
| LocalName | char(45) | NO | | | |
| GovernmentForm | char(45) | NO | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int(11) | YES | | NULL | |
| Code2 | char(2) | NO | | | |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)回答
country table と countrylanguage table を countryCode で JOIN した後、Language と Percentage で条件に沿ったフィルタを行う。
英語が世界標準後と言われているが、以下のような興味深い結果が得られた。
※このデータがどこまで正確であるかは不明
１．それぞれの国内における使用率？が10パーセントを超えている国は15か国しかない
２．Puerto RicoとTrinidad and Tobagoでは、英語が国内公式言語ではないにも関わらず、使用率が高い
mysql> select country.Name as CountryName, lang.CountryCode, lang.Language, lang.IsOfficial, lang.Percentage from country INNER JOIN countrylanguage lang ON country.Code = lang.CountryCode where lang.Language = 'English' AND lang.Percentage > 10.0; +----------------------+-------------+----------+------------+------------+ | CountryName | CountryCode | Language | IsOfficial | Percentage | +----------------------+-------------+----------+------------+------------+ | Australia | AUS | English | T | 81.2 | | Belize | BLZ | English | T | 50.8 | | Bermuda | BMU | English | T | 100.0 | | Canada | CAN | English | T | 60.4 | | United Kingdom | GBR | English | T | 97.3 | | Gibraltar | GIB | English | T | 88.9 | | Guam | GUM | English | T | 37.5 | | Ireland | IRL | English | T | 98.4 | | Saint Lucia | LCA | English | T | 20.0 | | New Zealand | NZL | English | T | 87.0 | | Puerto Rico | PRI | English | F | 47.4 | | Trinidad and Tobago | TTO | English | F | 93.5 | | United States | USA | English | T | 86.2 | | Virgin Islands, U.S. | VIR | English | T | 81.7 | | Vanuatu | VUT | English | T | 28.3 | +----------------------+-------------+----------+------------+------------+ 15 rows in set (0.00 sec)
