SQL初心者〜中級者のための練習問題+解答例をいくつか作ってみました。
SQL力向上のためにお役立てください。
その2: SQL初心者〜中級者のための練習問題&解答例2
その3: SQL初心者〜中級者のための練習問題&解答例3
ツールの紹介
DB Fiddleを使うとブラウザ上で簡単にSQLを実行できます。
画面左側のSchema SQLにスキーマを、画面右側のQuery SQLにクエリ（SELECT句）を書いて、画面上部の「Run」ボタンを押すだけです。
環境構築要らないので便利です。
題材
学生テーブル
| 学籍番号 | 氏名 | 性別 |
|---|---|---|
| 0001 | 長岡 一馬 | 男 |
| 0002 | 中本 知佳 | 女 |
| 0003 | 松本 義文 | 男 |
| 0004 | 佐竹 友香 | 女 |
試験結果テーブル
| 学籍番号 | 教科 | 点数 |
|---|---|---|
| 0001 | 国語 | 30 |
| 0001 | 英語 | 30 |
| 0002 | 国語 | 70 |
| 0002 | 数学 | 80 |
| 0003 | 理科 | 92 |
| 0004 | 社会 | 90 |
| 0004 | 理科 | 35 |
| 0004 | 英語 | 22 |
Schema SQL
CREATE TABLE `students` (
`id` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(255) NOT NULL,
`gender` varchar(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `students` (`id`, `name`, `gender`)
VALUES
(0001, '長岡 一馬', '男'),
(0002, '中本 知佳', '女'),
(0003, '松本 義文', '男'),
(0004, '佐竹 友香', '女');
CREATE TABLE `exam_results` (
`student_id` int(4) unsigned zerofill NOT NULL,
`subject` varchar(255) NOT NULL,
`score` bigint(20) unsigned NOT NULL,
PRIMARY KEY(`student_id`, `subject`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `exam_results` (`student_id`, `subject`, `score`)
VALUES
(0001, '国語', 30),
(0001, '英語', 30),
(0002, '国語', 70),
(0002, '数学', 80),
(0003, '理科', 92),
(0004, '社会', 90),
(0004, '理科', 35),
(0004, '英語', 22);
問1
性別が男である生徒の名前を一覧で表示せよ。
| name |
|---|
| 長岡 一馬 |
| 松本 義文 |
解答例
SELECT
name
FROM
students
WHERE
gender = '男'
問2
1教科でも30点以下の点数を取った生徒の名前を一覧で表示せよ。
ただし、重複は許さないものとする。
| name |
|---|
| 長岡 一馬 |
| 佐竹 友香 |
解答例
SELECT DISTINCT
s.name
FROM
exam_results er
INNER JOIN students s
ON er.student_id = s.id
WHERE
er.score <= 30
問3
性別ごとに、最も高かった試験の点数を表示せよ。
| gender | max_score |
|---|---|
| 男 | 92 |
| 女 | 90 |
解答例
SELECT
s.gender,
MAX(er.score) AS max_score
FROM
students s
INNER JOIN exam_results er
ON s.id = er.student_id
GROUP BY
s.gender
問4
教科ごとの試験の平均点が50点以下である教科を表示せよ。
| subject | avg_score |
|---|---|
| 国語 | 50.0000 |
| 英語 | 26.0000 |
解答例
SELECT
subject,
AVG(score) AS avg_score
FROM
exam_results
GROUP BY
subject
HAVING
AVG(score) <= 50
問5
試験結果テーブルの点数の右に、その教科の平均点を表示せよ。
| student_id | subject | score | subject_avg_score |
|---|---|---|---|
| 0001 | 国語 | 30 | 50.0000 |
| 0001 | 英語 | 30 | 26.0000 |
| 0002 | 国語 | 70 | 50.0000 |
| 0002 | 数学 | 80 | 80.0000 |
| 0003 | 理科 | 92 | 63.5000 |
| 0004 | 理科 | 35 | 63.5000 |
| 0004 | 社会 | 90 | 90.0000 |
| 0004 | 英語 | 22 | 26.0000 |
解答例1
SELECT
er1.*,
(
SELECT
AVG(er2.score)
FROM
exam_results er2
WHERE
er1.subject = er2.subject
) AS subject_avg_score
FROM
exam_results er1
解答例2（提供: @takahasinaoki さん）
SELECT
*,
AVG(score) OVER(PARTITION BY subject) AS subject_avg_score
FROM
exam_results
ORDER BY
student_id
※MySQL8.0以上でしか動きません
問6
試験結果に理科が含まれない生徒の名前を一覧で表示せよ。
| name |
|---|
| 長岡 一馬 |
| 中本 知佳 |
解答例1
SELECT
s.name
FROM
students s
INNER JOIN exam_results er
ON s.id = er.student_id
GROUP BY
s.id
HAVING
SUM(CASE WHEN er.subject = '理科' THEN 1 ELSE 0 END) = 0
解答例2（提供: @takahasinaoki さん）
SELECT
name
FROM
students
WHERE
id NOT IN (
SELECT
student_id
FROM
exam_results
WHERE
subject = '理科'
)
解答例3（提供: @takahasinaoki さん）
SELECT
name
FROM
students s
WHERE
NOT EXISTS (
SELECT
*
FROM
exam_results
WHERE
subject = '理科'
AND
student_id = s.id
)