PostgreSQL 練習 (1)
↓の続きです。今回は問題を解いていきます。
問1
性別が男である生徒の名前を一覧で表示せよ。
こんな感じの SQL 文を書きました。
SELECT name FROM students WHERE gender = '男';
得られた出力は以下の通りです。
name ----------- 長岡 一馬 松本 義文 (2 rows)
参考: https://www.postgresql.jp/document/12/html/tutorial-select.html
問2
1教科でも30点以下の点数を取った生徒の名前を一覧で表示せよ。 ただし、重複は許さないものとする。
こんな感じの SQL 文を書きました。
SELECT DISTINCT students.name FROM students, exam_results WHERE students.id = exam_results.student_id AND exam_results.score <= 30;
得られた出力は以下の通りです。
name ----------- 長岡 一馬 佐竹 友香 (2 rows)
別解として、INNER JOINを使った書き方も試してみました。
SELECT DISTINCT students.name FROM students INNER JOIN exam_results ON exam_results.student_id = students.id WHERE exam_results.score <= 30;
参考: https://www.postgresql.jp/document/12/html/tutorial-join.html
問3
性別ごとに、最も高かった試験の点数を表示せよ。
こんな感じの SQL 文を書きました。
SELECT students.gender, MAX(exam_results.score) AS max_score FROM students, exam_results WHERE students.id = exam_results.student_id GROUP BY students.gender;
得られた出力は以下の通りです。
gender | max_score --------+----------- 男 | 92 女 | 90 (2 rows)
この問題も別解としてINNER JOINを使った書き方も試してみました。
SELECT students.gender, MAX(exam_results.score) AS max_score FROM students INNER JOIN exam_results ON exam_results.student_id = students.id GROUP BY students.gender;
参考: https://www.postgresql.jp/document/12/html/tutorial-agg.html
問4
教科ごとの試験の平均点が50点以下である教科を表示せよ。
こんな感じの SQL 文を書きました。
SELECT subject, AVG(score) AS avg_score FROM exam_results GROUP BY subject HAVING AVG(score) <= 50;
得られた出力は以下の通りです。
subject | avg_score ---------+--------------------- 英語 | 26.0000000000000000 国語 | 50.0000000000000000 (2 rows)
問5
試験結果テーブルの点数の右に、その教科の平均点を表示せよ。
こんな感じの SQL 文を書きました。
SELECT student_id, subject, score, AVG(score) OVER (PARTITION BY subject) AS subject_avg_score FROM exam_results;
得られた出力は以下の通りです。
student_id | subject | score | subject_avg_score
------------+---------+-------+---------------------
1 | 国語 | 30 | 50.0000000000000000
2 | 国語 | 70 | 50.0000000000000000
2 | 数学 | 80 | 80.0000000000000000
3 | 理科 | 92 | 63.5000000000000000
4 | 理科 | 35 | 63.5000000000000000
4 | 社会 | 90 | 90.0000000000000000
4 | 英語 | 22 | 26.0000000000000000
1 | 英語 | 30 | 26.0000000000000000
(8 rows)
参考: https://www.postgresql.jp/document/12/html/tutorial-window.html
問6
試験結果に理科が含まれない生徒の名前を一覧で表示せよ。
こんな感じの SQL 文を書きました。
SELECT name FROM students WHERE id NOT IN ( SELECT student_id FROM exam_results WHERE subject = '理科' );
得られた出力は以下の通りです。
name ----------- 長岡 一馬 中本 知佳 (2 rows)
次
終わり
