第3弾を作ってみました。
SQL力向上のためにお役立てください。
その1: SQL初心者〜中級者のための練習問題&解答例1
その2: SQL初心者〜中級者のための練習問題&解答例2
ツールの紹介
DB Fiddleを使うとブラウザ上で簡単にSQLを実行できます。
画面左側のSchema SQLにスキーマを、画面右側のQuery SQLにクエリ（SELECT句）を書いて、画面上部の「Run」ボタンを押すだけです。
環境構築要らないので便利です。
題材
ユーザーテーブル
| ユーザーID | ユーザー名 | メールアドレス | 年齢 |
|---|---|---|---|
| 1 | もっくん | mokkun@example.com | 19 |
| 2 | みみこ | mimiko@example.net | 20 |
| 3 | さくら | sakura@example.com | 31 |
| 4 | ひよこ | hiyoko@example1.jp | 23 |
| 5 | すずき | suzuki@example.jp | 28 |
フォローテーブル
※フォロワー:フォローする側、フォロイー:フォローされる側
| フォロワーID | フォロイーID |
|---|---|
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 3 | 1 |
| 3 | 2 |
| 4 | 5 |
| 5 | 1 |
| 5 | 2 |
| 5 | 3 |
| 5 | 4 |
Schema SQL
CREATE TABLE `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`age` int(3) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `users` (`id`, `name`, `email`, `age`)
VALUES
(1, 'もっくん', 'mokkun@example.com', 19),
(2, 'みみこ', 'mimiko@example.net', 20),
(3, 'さくら', 'sakura@example.com', 31),
(4, 'ひよこ', 'hiyoko@example1.jp', 23),
(5, 'すずき', 'suzuki@example.jp', 28);
CREATE TABLE `follows` (
`follower_id` int(11) unsigned NOT NULL,
`followee_id` int(11) unsigned NOT NULL,
PRIMARY KEY(`follower_id`, `followee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `follows` (`follower_id`, `followee_id`)
VALUES
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(3, 1),
(3, 2),
(4, 5),
(5, 1),
(5, 2),
(5, 3),
(5, 4);
問1
さくらがフォローしているユーザーの名前を一覧で表示せよ。
| name |
|---|
| もっくん |
| みみこ |
解答例
SELECT
u2.name
FROM
users u1
INNER JOIN follows f
ON u1.id = f.follower_id
INNER JOIN users u2
ON f.followee_id = u2.id
WHERE
u1.id = 3
問2
誰もフォローしていないユーザーの名前を表示せよ。
| name |
|---|
| みみこ |
解答例
SELECT
u.name
FROM
users u
LEFT JOIN follows f
ON u.id = f.follower_id
WHERE
f.follower_id IS NULL
問3
10代、20代、30代といった年代別にフォロー数の平均を表示せよ。
| age_group | avg_count |
|---|---|
| 10代 | 4.0000 |
| 20代 | 1.6667 |
| 30代 | 2.0000 |
解答例1
SELECT
CONCAT((FLOOR(age / 10) * 10), '代') AS age_group,
AVG(count) AS avg_count
FROM (
SELECT
u.age,
SUM(CASE WHEN f.follower_id IS NOT NULL THEN 1 ELSE 0 END) AS count
FROM
users u
LEFT JOIN follows f
ON u.id = f.follower_id
GROUP BY
u.id
) follows_count
GROUP BY
CONCAT((FLOOR(age / 10) * 10), '代')
解答例2（提供: @takahasinaoki さん）
SELECT
CONCAT(age_group * 10, '代') AS age_group,
AVG(count) AS avg_count
FROM (
SELECT
FLOOR(age / 10) AS age_group,
count(f.follower_id) AS count
FROM
users u
LEFT JOIN follows f
ON u.id = f.follower_id
GROUP BY
u.id
) follows_count
GROUP BY
age_group
解答例3（提供: @kensei_o さん）
SELECT
CONCAT((FLOOR(age / 10) * 10), '代') AS age_group,
SUM(CASE WHEN f.follower_id THEN 1 ELSE 0 END) / COUNT(DISTINCT u.id) AS avg_count
FROM
users u
LEFT JOIN follows f
ON u.id = f.follower_id
GROUP BY
CONCAT((FLOOR(age / 10) * 10), '代')
問4
相互フォローしているユーザーのIDを表示せよ。
なお、重複は許さないものとする。
| id1 | id2 |
|---|---|
| 1 | 3 |
| 1 | 5 |
| 4 | 5 |
解答例1
SELECT
CASE WHEN f1.follower_id > f1.followee_id THEN f1.followee_id ELSE f1.follower_id END AS id1,
CASE WHEN f1.follower_id > f1.followee_id THEN f1.follower_id ELSE f1.followee_id END AS id2
FROM
follows f1
INNER JOIN follows f2
ON f1.follower_id = f2.followee_id
AND f1.followee_id = f2.follower_id
GROUP BY
CASE WHEN f1.follower_id > f1.followee_id THEN f1.followee_id ELSE f1.follower_id END,
CASE WHEN f1.follower_id > f1.followee_id THEN f1.follower_id ELSE f1.followee_id END
解答例2（提供: @takahasinaoki さん）
SELECT
f1.follower_id As id1,
f1.followee_id As id2
FROM
follows f1
INNER JOIN follows f2
ON f1.follower_id = f2.followee_id
AND f1.followee_id = f2.follower_id
WHERE
f1.follower_id < f1.followee_id
解答例3（提供: @HIKARU_JY さん）
SELECT
*
FROM
follows f1
WHERE
EXISTS (
SELECT
*
FROM
follows f2
WHERE
f1.follower_id = f2.followee_id
AND f2.follower_id = f1.followee_id
)
AND f1.follower_id < f1.followee_id