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

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

その1: SQL初心者〜中級者のための練習問題&解答例1
その3: SQL初心者〜中級者のための練習問題&解答例3

ツールの紹介

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設計であっても望むデータを取り出す練習のために、わざとこうしています。

Schema 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);

問1

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

name
杉山 圭佑
佐藤 結菜

解答例1

SELECT
	name
FROM
	employees
WHERE
	hobby1 = '映画鑑賞'
	OR
	hobby2 = '映画鑑賞'
	OR
	hobby3 = '映画鑑賞'

解答例2（提供: @takahasinaoki さん）

SELECT
	name
FROM
	employees
WHERE
	'映画鑑賞' IN (hobby1, hobby2, hobby3)

問2

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

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

解答例

SELECT name, hobby1 AS hobby FROM employees
UNION ALL
SELECT name, hobby2 FROM employees
UNION ALL
SELECT name, hobby3 FROM employees

問3

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

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

解答例1

SELECT
    name,
    COUNT(hobby1) + COUNT(hobby2) + COUNT(hobby3) AS hobby_count
FROM
    employees
WHERE
    name LIKE '佐藤 %'
GROUP BY
    name

解答例2

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

問4

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

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

解答例

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