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

SQL初心者〜中級者のための練習問題+解答例をいくつか作ってみました。
SQL力向上のためにお役立てください。

その2: [SQL初心者〜中級者のための練習問題&解答例2](https://qiita.com/_hiro_dev/items/e16fa143487d2c3686c9 "SQL初心者〜中級者のための練習問題&解答例2")
その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」ボタンを押すだけです。
環境構築要らないので便利です。

# 題材
**学生テーブル**

| 学籍番号 | 氏名      | 性別 | 
| -------- | --------- | ---- | 
| 0001     | 長岡 一馬 | 男   | 
| 0002     | 中本 知佳 | 女   | 
| 0003     | 松本 義文 | 男   | 
| 0004     | 佐竹 友香 | 女   | 

**試験結果テーブル**

| 学籍番号 | 教科 | 点数 | 
| -------- | ---- | ---- | 
| 0001     | 国語 | 30   | 
| 0001     | 英語 | 30   | 
| 0002     | 国語 | 70   | 
| 0002     | 数学 | 80   | 
| 0003     | 理科 | 92   | 
| 0004     | 社会 | 90   | 
| 0004     | 理科 | 35   | 
| 0004     | 英語 | 22   | 

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

```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);
```
</div></details>

# 問1
性別が男である生徒の名前を一覧で表示せよ。

| name  |
| ----- |
| 長岡 一馬 |
| 松本 義文 |

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

```sql
SELECT
	name
FROM
	students
WHERE
	gender = '男'
```
</div></details>

# 問2
1教科でも30点以下の点数を取った生徒の名前を一覧で表示せよ。
ただし、重複は許さないものとする。

| name  |
| ----- |
| 長岡 一馬 |
| 佐竹 友香 |

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

```sql
SELECT DISTINCT
	s.name
FROM
	exam_results er
INNER JOIN students s
	ON er.student_id = s.id
WHERE
	er.score <= 30
```
</div></details>

# 問3
性別ごとに、最も高かった試験の点数を表示せよ。

| gender | max_score |
| ------ | --------- |
| 男      | 92        |
| 女      | 90        |

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

```sql
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
```
</div></details>

# 問4
教科ごとの試験の平均点が50点以下である教科を表示せよ。

| subject | avg_score |
| ------- | --------- |
| 国語      | 50.0000   |
| 英語      | 26.0000   |

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

```sql
SELECT
	subject,
    AVG(score) AS avg_score
FROM
	exam_results
GROUP BY
	subject
HAVING
	AVG(score) <= 50
```
</div></details>

# 問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           |

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

```sql
SELECT
	er1.*,
	(
		SELECT
			AVG(er2.score)
		FROM
			exam_results er2
		WHERE
			er1.subject = er2.subject
	) AS subject_avg_score
FROM
	exam_results er1
```
</div></details>

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

```sql
SELECT
	*,
	AVG(score) OVER(PARTITION BY subject) AS subject_avg_score
FROM
	exam_results
ORDER BY 
	student_id
```
※MySQL8.0以上でしか動きません

</div></details>

# 問6
試験結果に理科が含まれない生徒の名前を一覧で表示せよ。

| name  |
| ----- |
| 長岡 一馬 |
| 中本 知佳 |

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

```sql
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
```
</div></details>

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

```sql
SELECT
	name
FROM
	students
WHERE 
	id NOT IN (
		SELECT
			student_id
		FROM
			exam_results
		WHERE
			subject = '理科'
	)
```
</div></details>

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

```sql
SELECT
	name
FROM
	students s
WHERE 
	NOT EXISTS (
		SELECT
			*
		FROM
			exam_results
		WHERE
			subject = '理科'
			AND
			student_id = s.id
	)
```
</div></details>