【SQL】MySQL公式サンプルデータベースを使って検索してみる

https://qiita.com/yukibe/items/fc6016348ecf4f3b10bf
を教えてもらったので、ググってでてきた
https://gist.github.com/mdang/9a4a8063ebea3b829b8025746643ade1
の問題をやってみる。

#データの確認
##city
```sql:desc_city
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int      | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

```

`MUL`については
https://qiita.com/qyuser/items/2c535888b6a899685821
を参照。

##country
```sql:desc_country
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                                                                              | YES  |     | NULL    |       |
| Population     | int                                                                                   | 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                                                                                   | YES  |     | NULL    |       |
| Code2          | char(2)                                                                               | NO   |     |         |       |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)
```

##countrylanguage
```sql:desc_coutnrylanguage
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)
```
#エクササイズ

- Using `count`, get the number of cities in the USA
- Find out what the population and average life expectancy for people in Argentina (ARG) is
- Using `IS NOT NULL`, `ORDER BY`, `LIMIT`, what country has the highest life expectancy?
- Using `LEFT JOIN`, `ON`, what is the capital of Spain (ESP)?
- Using `LEFT JOIN`, `ON`, list all the languages spoken in the 'Southeast Asia' region


BONUS

- Select 25 cities around the world that start with the letter 'F' in a single SQL query.

##Using `count`, get the number of cities in the USA
```sql:count_USA
mysql> select count(*) as count, CountryCode from city where CountryCode="USA";
+-------+-------------+
| count | CountryCode |
+-------+-------------+
|   274 | USA         |
+-------+-------------+
1 row in set (0.00 sec)
```

###解説
`city`テーブルに`CountryCode`列があるので、**WHERE**で抽出してみた。

##Find out what the population and average life expectancy for people in Argentina (ARG) is

```sql:ARG_popurlation&LifeExpectancy
mysql> select Name,Population,LifeExpectancy from country 
     where Code="ARG";
+-----------+------------+----------------+
| Name      | Population | LifeExpectancy |
+-----------+------------+----------------+
| Argentina |   37032000 |           75.1 |
+-----------+------------+----------------+
1 row in set (0.00 sec)
```

###解説
こんどは`country`テーブルから`Code`を指定して、`Population`と`LifeExpectancy`を抽出

ここまでは、どの列にどんな値が入っているかわかれば簡単。

##Using `IS NOT NULL`, `ORDER BY`, `LIMIT`, what country has the highest life expectancy?

```sql:Highest_LifeExpectancy
mysql> select Name,LifeExpectancy 
     from country
     where LifeExpectancy IS NOT NULL
     ORDER BY LifeExpectancy desc
     LIMIT 1;
+---------+----------------+
| Name    | LifeExpectancy |
+---------+----------------+
| Andorra |           83.5 |
+---------+----------------+
1 row in set (0.00 sec)
```

###解説
普段はSplunkなので`IS NOT NULL`ってどう使うのかわからなかった:sweat:

```sql:is_not_null
mysql> help is not null;
Name: 'IS NOT NULL'
Description:
Syntax:
IS NOT NULL

Tests whether a value is not NULL.

URL: https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html

Examples:
mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
        -> 1, 1, 0
```
`NULL`じゃなければ*TRUE*とのこと

##Using `LEFT JOIN`, `ON`, what is the capital of Spain (ESP)?
```sql:capital_ESP
mysql> select country.Name,city.Name 
    from country LEFT JOIN city
    ON city.ID=country.Capital
    where country.Code="ESP"; 
+-------+--------+
| Name  | Name   |
+-------+--------+
| Spain | Madrid |
+-------+--------+
1 row in set (0.00 sec)
```
###解説
結果的には
http://www.innovato.com/splunk/SQLSplunk.html
ここに載っていたけど、`ON`で条件を書く必要があった。

##Using `LEFT JOIN`, `ON`, list all the languages spoken in the 'Southeast Asia' region
```sql:laungage
mysql> select countrylanguage.Language,country.Name,country.Region 
    from country LEFT JOIN countrylanguage
    ON countrylanguage.CountryCode=country.Code
    where country.Region="Southeast Asia";
+---------------+-------------+----------------+
| Language      | Name        | Region         |
+---------------+-------------+----------------+
| Chinese       | Brunei      | Southeast Asia |
| English       | Brunei      | Southeast Asia |
| Malay         | Brunei      | Southeast Asia |
| Malay-English | Brunei      | Southeast Asia |
| Bali          | Indonesia   | Southeast Asia |
| Banja         | Indonesia   | Southeast Asia |
| Batakki       | Indonesia   | Southeast Asia |
| Bugi          | Indonesia   | Southeast Asia |
| Javanese      | Indonesia   | Southeast Asia |
| Madura        | Indonesia   | Southeast Asia |
| Malay         | Indonesia   | Southeast Asia |
| Minangkabau   | Indonesia   | Southeast Asia |
| Sunda         | Indonesia   | Southeast Asia |
| Chinese       | Cambodia    | Southeast Asia |
| Khmer         | Cambodia    | Southeast Asia |
| Tšam          | Cambodia    | Southeast Asia |
| Vietnamese    | Cambodia    | Southeast Asia |
| Lao           | Laos        | Southeast Asia |
| Lao-Soung     | Laos        | Southeast Asia |
| Mon-khmer     | Laos        | Southeast Asia |
| Thai          | Laos        | Southeast Asia |
| Burmese       | Myanmar     | Southeast Asia |
| Chin          | Myanmar     | Southeast Asia |
| Kachin        | Myanmar     | Southeast Asia |
| Karen         | Myanmar     | Southeast Asia |
| Kayah         | Myanmar     | Southeast Asia |
| Mon           | Myanmar     | Southeast Asia |
| Rakhine       | Myanmar     | Southeast Asia |
| Shan          | Myanmar     | Southeast Asia |
| Chinese       | Malaysia    | Southeast Asia |
| Dusun         | Malaysia    | Southeast Asia |
| English       | Malaysia    | Southeast Asia |
| Iban          | Malaysia    | Southeast Asia |
| Malay         | Malaysia    | Southeast Asia |
| Tamil         | Malaysia    | Southeast Asia |
| Bicol         | Philippines | Southeast Asia |
| Cebuano       | Philippines | Southeast Asia |
| Hiligaynon    | Philippines | Southeast Asia |
| Ilocano       | Philippines | Southeast Asia |
| Maguindanao   | Philippines | Southeast Asia |
| Maranao       | Philippines | Southeast Asia |
| Pampango      | Philippines | Southeast Asia |
| Pangasinan    | Philippines | Southeast Asia |
| Pilipino      | Philippines | Southeast Asia |
| Waray-waray   | Philippines | Southeast Asia |
| Chinese       | Singapore   | Southeast Asia |
| Malay         | Singapore   | Southeast Asia |
| Tamil         | Singapore   | Southeast Asia |
| Chinese       | Thailand    | Southeast Asia |
| Khmer         | Thailand    | Southeast Asia |
| Kuy           | Thailand    | Southeast Asia |
| Lao           | Thailand    | Southeast Asia |
| Malay         | Thailand    | Southeast Asia |
| Thai          | Thailand    | Southeast Asia |
| Portuguese    | East Timor  | Southeast Asia |
| Sunda         | East Timor  | Southeast Asia |
| Chinese       | Vietnam     | Southeast Asia |
| Khmer         | Vietnam     | Southeast Asia |
| Man           | Vietnam     | Southeast Asia |
| Miao          | Vietnam     | Southeast Asia |
| Muong         | Vietnam     | Southeast Asia |
| Nung          | Vietnam     | Southeast Asia |
| Thai          | Vietnam     | Southeast Asia |
| Tho           | Vietnam     | Southeast Asia |
| Vietnamese    | Vietnam     | Southeast Asia |
+---------------+-------------+----------------+
65 rows in set (0.00 sec)
```

###解説
```sql:continent
mysql> select * from country where Continent="Asia";
```

まず、どこの列に`Southeast Asia`があるのかわからなかったので、先のクエリーで検索。

`Region`にあることがわかったので、そこからクエリーを組み立てた。

##Select 25 cities around the world that start with the letter 'F' in a single SQL query.

```sql:25F_city
mysql> select Name,CountryCode from city where Name like 'F%' limit 25;
+---------------------------+-------------+
| Name                      | CountryCode |
+---------------------------+-------------+
| Fagatogo                  | ASM         |
| Florencio Varela          | ARG         |
| Formosa                   | ARG         |
| Francistown               | BWA         |
| Fortaleza                 | BRA         |
| Feira de Santana          | BRA         |
| Franca                    | BRA         |
| Florianópolis             | BRA         |
| Foz do Iguaçu             | BRA         |
| Ferraz de Vasconcelos     | BRA         |
| Francisco Morato          | BRA         |
| Franco da Rocha           | BRA         |
| Fuenlabrada               | ESP         |
| Faridabad                 | IND         |
| Firozabad                 | IND         |
| Farrukhabad-cum-Fatehgarh | IND         |
| Faizabad                  | IND         |
| Fatehpur                  | IND         |
| Firenze                   | ITA         |
| Foggia                    | ITA         |
| Ferrara                   | ITA         |
| Forlì                     | ITA         |
| Fukuoka                   | JPN         |
| Funabashi                 | JPN         |
| Fukuyama                  | JPN         |
+---------------------------+-------------+
25 rows in set (0.00 sec)
```
###解説
**LIKE**はSplunkと変わりがないので、そのまま検索。

#まとめ

どの列のどんな値が入っているのかの調べ方がまだまだ弱いので、そこらへんを重点的に調べていきたいと思います。