うとうとしながら。

学んだことのノート

PostgreSQL 練習 (1)

↓の続きです。今回は問題を解いていきます。

utouto97.hatenablog.com

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

utouto97.hatenablog.com

終わり