SQL初心者〜中級者のための練習問題&解答例2

第2弾を作ってみました。
SQL力向上のためにお役立てください。

その1: [SQL初心者〜中級者のための練習問題&解答例1](https://qiita.com/_hiro_dev/items/ece39759879c5d1f8536 "SQL初心者〜中級者のための練習問題&解答例1")
その3: [SQL初心者〜中級者のための練習問題&解答例3](https://qiita.com/_hiro_dev/items/9e547f80a0388d68ec13 "SQL初心者〜中級者のための練習問題&解答例3")

# ツールの紹介
[DB Fiddle](https://www.db-fiddle.com/ "DB Fiddle")を使うとブラウザ上で簡単にSQLを実行できます。
画面左側のSchema SQLにスキーマを、画面右側のQuery SQLにクエリ（SELECT句）を書いて、画面上部の「Run」ボタンを押すだけです。
環境構築要らないので便利です。

# 題材
**社員テーブル**

| 社員番号 | 氏名      | 所属部署 | 趣味1    | 趣味2    | 趣味3    | 
| -------- | --------- | -------- | -------- | -------- | -------- | 
| 1        | 杉山 圭佑 | 営業部   | サッカー | ドライブ | 映画鑑賞     | 
| 2        | 佐藤 結菜 | 人事部   | 映画鑑賞 | 旅行     | インスタ | 
| 3        | 高橋 絵里 | 経理部   | ゲーム   | NULL     | NULL     | 
| 4        | 早川 良太 | 人事部   | ドライブ | 料理     | NULL     | 
| 5        | 佐藤 一弥 | 経理部   | NULL     | NULL     | NULL     | 
| 6        | 佐藤 優穂 | 営業部   | インスタ | TikTok   | NULL     | 

※ありがちなDB設計の例として趣味1～3を列で持っていますが、これはSQLアンチパターンの「マルチカラムアトリビュート」に該当するため、設計の参考にはしないでください。アンチパターンのDB設計であっても望むデータを取り出す練習のために、わざとこうしています。

<details><summary>Schema SQL</summary><div>

```sql
CREATE TABLE `employees` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(255) NOT NULL,
  `department` varchar(255) NOT NULL,
  `hobby1` varchar(255),
  `hobby2` varchar(255),
  `hobby3` varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `employees` (`id`, `name`, `department`, `hobby1`, `hobby2`, `hobby3`)
VALUES
	(1, '杉山 圭佑', '営業部', 'サッカー', 'ドライブ', '映画鑑賞'),
	(2, '佐藤 結菜', '人事部', '映画鑑賞', '旅行', 'インスタ'),
	(3, '高橋 絵里', '経理部', 'ゲーム', NULL, NULL),
	(4, '早川 良太', '人事部', 'ドライブ', '料理', NULL),
	(5, '佐藤 一弥', '経理部', NULL, NULL, NULL),
	(6, '佐藤 優穂', '営業部', 'インスタ', 'TikTok', NULL);
```
</div></details>

# 問1
趣味に映画鑑賞が含まれる社員の名前を一覧で表示せよ。

| name  |
| ----- |
| 杉山 圭佑 |
| 佐藤 結菜 |

<details><summary>解答例1</summary><div>

```sql
SELECT
	name
FROM
	employees
WHERE
	hobby1 = '映画鑑賞'
	OR
	hobby2 = '映画鑑賞'
	OR
	hobby3 = '映画鑑賞'
```
</div></details>

<details><summary>解答例2（提供: @takahasinaoki さん）</summary><div>

```sql
SELECT
	name
FROM
	employees
WHERE
	'映画鑑賞' IN (hobby1, hobby2, hobby3)
```
</div></details>

# 問2
趣味1～3を縦に表示せよ。

| name  | hobby  |
| ----- | ------ |
| 杉山 圭佑 | サッカー   |
| 佐藤 結菜 | 映画鑑賞   |
| 高橋 絵里 | ゲーム    |
| 早川 良太 | ドライブ   |
| 佐藤 一弥 | NULL   |
| 佐藤 優穂 | インスタ   |
| 杉山 圭佑 | ドライブ   |
| 佐藤 結菜 | 旅行     |
| 高橋 絵里 | NULL   |
| 早川 良太 | 料理     |
| 佐藤 一弥 | NULL   |
| 佐藤 優穂 | TikTok |
| 杉山 圭佑 | 映画鑑賞   |
| 佐藤 結菜 | インスタ   |
| 高橋 絵里 | NULL   |
| 早川 良太 | NULL   |
| 佐藤 一弥 | NULL   |
| 佐藤 優穂 | NULL   |

<details><summary>解答例</summary><div>

```sql
SELECT name, hobby1 AS hobby FROM employees
UNION ALL
SELECT name, hobby2 FROM employees
UNION ALL
SELECT name, hobby3 FROM employees
```
</div></details>

# 問3
名字が佐藤である社員の、趣味の数を表示せよ。

| name  | hobby_count |
| ----- | ----------- |
| 佐藤 一弥 | 0           |
| 佐藤 優穂 | 2           |
| 佐藤 結菜 | 3           |

<details><summary>解答例1</summary><div>

```sql
SELECT
    name,
    COUNT(hobby1) + COUNT(hobby2) + COUNT(hobby3) AS hobby_count
FROM
    employees
WHERE
    name LIKE '佐藤 %'
GROUP BY
    name
```
</div></details>

<details><summary>解答例2</summary><div>

```sql
SELECT
	name,
    CASE WHEN hobby1 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN hobby2 IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN hobby3 IS NOT NULL THEN 1 ELSE 0 END AS hobby_count
FROM
	employees
WHERE
	name LIKE '佐藤 %'
ORDER BY
	name
```
</div></details>

# 問4
同じ趣味を持つ社員の一覧を表示せよ。
なお、氏名リストの並び順は社員番号の昇順で、区切り文字は「, 」とする。

| hobby | name_list    |
| ----- | ------------ |
| インスタ  | 佐藤 結菜, 佐藤 優穂 |
| ドライブ  | 杉山 圭佑, 早川 良太 |
| 映画鑑賞  | 杉山 圭佑, 佐藤 結菜 |

<details><summary>解答例</summary><div>

```sql
SELECT
    hobby,
    GROUP_CONCAT(name ORDER BY id separator ', ') AS name_list
FROM (
    SELECT id, name, hobby1 AS hobby FROM employees
    UNION ALL
    SELECT id, name, hobby2 FROM employees
    UNION ALL
    SELECT id, name, hobby3 FROM employees
) hobby_table
WHERE
    hobby IS NOT NULL
GROUP BY
    hobby
HAVING
    COUNT(*) > 1
```
</div></details>