見出し画像
Photo by subarasikiai

『実践Pythonによるデータベース入門』第4章演習問題

在野研究者トシヤ

今日は、『実践Pythonによるデータベース入門』の第4章演習問題を解いていきます。

【4.1】

【例題4.2】で作成したデータベースjikanwaribaseを用いて、学生別受講科目一覧を表示せよ。ただし、学生1名につき、一つのSQL命令文で実行する。
（1）学生1名ずつの受講科目一覧表（ソートなし）
（2）学生1名ずつの受講科目一覧表（曜日の昇順と時限の昇順でソート）
<補足>曜日の値が日本語表記の場合は、「…ORDER BY…」の結果は、漢字の文字コードの順番になるので、通常の曜日の順番と異なる。例えば、テーブルkamokuを曜日順に表示するには、以下のような工夫が必要となる。
SELECT youbi FROM kamoku ORDER BY CASE youbi when '日' then 1 when '月' then 2 when '火' then 3 when '水' then 4 when '木' then 5 when '金' then 6 when '土' then 7 END;

（1）学生1名ずつの受講科目一覧表（ソートなし）

自然結合演算を2回行い、三つの表を結合し、必要と思われる列名（kamokumei, kyoushitsu, youbi, jigen）を指定しました。さらに、WHERE句で学生の名前を指定することで学生1名ずつの指定を行いました。

mysql> SELECT kamoku.kamokumei, kamoku.kyoushitsu, kamoku.youbi, kamoku.jigen FROM gakusei NATURAL INNER JOIN rishu NATURAL INNER JOIN kamoku
WHERE gakusei.namae='田中花子';
+--------------------------+------------+-------+-------+
| kamokumei                | kyoushitsu | youbi | jigen |
+--------------------------+------------+-------+-------+
| ネットワーク基礎         | 2111       | 火    | 1     |
| データベース             | 1203       | 月    | 2     |
| プロジェクト1            | 1211       | 水    | 3     |
| 英語1                    | 3221       | 火    | 2     |
| 体育1                    | 体育館     | 水    | 3     |
+--------------------------+------------+-------+-------+
5 rows in set (0.02 sec)

mysql> SELECT kamoku.kamokumei, kamoku.kyoushitsu, kamoku.youbi, kamoku.jigen FROM gakusei NATURAL INNER JOIN rishu NATURAL INNER JOIN kamoku
WHERE gakusei.namae='山田聡';
+--------------------------------------+------------+-------+-------+
| kamokumei                            | kyoushitsu | youbi | jigen |
+--------------------------------------+------------+-------+-------+
| ネットワーク基礎                     | 2111       | 火    | 1     |
| オペレーティングシステム             | 1203       | 月    | 3     |
| プロジェクト1                        | 1211       | 水    | 3     |
| 英語2                                | 3221       | 金    | 2     |
| 体育1                                | 体育館     | 水    | 3     |
+--------------------------------------+------------+-------+-------+
5 rows in set (0.00 sec)

mysql> SELECT kamoku.kamokumei, kamoku.kyoushitsu, kamoku.youbi, kamoku.jigen FROM gakusei NATURAL INNER JOIN rishu NATURAL INNER JOIN kamoku
WHERE gakusei.namae='佐藤美那子';
+--------------------------------------+--------------+-------+-------+
| kamokumei                            | kyoushitsu   | youbi | jigen |
+--------------------------------------+--------------+-------+-------+
| ネットワーク基礎                     | 2111         | 火    | 1     |
| オペレーティングシステム             | 1203         | 月    | 3     |
| プロジェクト1                        | 1211         | 水    | 3     |
| 英語2                                | 3221         | 金    | 2     |
| 体育2                                | グランド     | 火    | 2     |
+--------------------------------------+--------------+-------+-------+
5 rows in set (0.01 sec)

mysql> SELECT kamoku.kamokumei, kamoku.kyoushitsu, kamoku.youbi, kamoku.jigen FROM gakusei NATURAL INNER JOIN rishu NATURAL INNER JOIN kamoku
WHERE gakusei.namae='鈴木博';
+--------------------------------------+--------------+-------+-------+
| kamokumei                            | kyoushitsu   | youbi | jigen |
+--------------------------------------+--------------+-------+-------+
| プログラミング入門                   | 2111         | 火    | 2     |
| オペレーティングシステム             | 1203         | 月    | 3     |
| プロジェクト2                        | 1212         | 水    | 4     |
| 英語2                                | 3221         | 金    | 2     |
| 体育2                                | グランド     | 火    | 2     |
+--------------------------------------+--------------+-------+-------+
5 rows in set (0.01 sec)

（2）学生1名ずつの受講科目一覧表（曜日の昇順と時限の昇順でソート）
<補足>曜日の値が日本語表記の場合は、「…ORDER BY…」の結果は、漢字の文字コードの順番になるので、通常の曜日の順番と異なる。例えば、テーブルkamokuを曜日順に表示するには、以下のような工夫が必要となる。
SELECT youbi FROM kamoku ORDER BY CASE youbi when '日' then 1 when '月' then 2 when '火' then 3 when '水' then 4 when '木' then 5 when '金' then 6 when '土' then 7 END;

まず、曜日の昇順でソートするSELECT文を考えてみました。

mysql> SELECT kamoku.kamokumei, kamoku.kyoushitsu, kamoku.youbi, kamoku.jigen FROM gakusei NATURAL INNER JOIN rishu NATURAL INNER JOIN kamoku
WHERE gakusei.namae='田中花子' ORDER BY CASE kamoku.youbi when '日' then 1 when '月' then 2 when '火' then 3 when '水' then 4 when '木' then 5 when '金' then 6 when '土' then 7 END;
+--------------------------+------------+-------+-------+
| kamokumei                | kyoushitsu | youbi | jigen |
+--------------------------+------------+-------+-------+
| データベース             | 1203       | 月    | 2     |
| ネットワーク基礎         | 2111       | 火    | 1     |
| 英語1                    | 3221       | 火    | 2     |
| プロジェクト1            | 1211       | 水    | 3     |
| 体育1                    | 体育館     | 木    | 3     |
+--------------------------+------------+-------+-------+
5 rows in set (0.00 sec)

複数の列名でソートする場合は、カンマ(,)で区切り続けて記述します。複数の列名を指定した場合には、まず最初の列名でソートを行い、次に最初の列名で同じ値だったものだけを対象に2番目の列名でソートを行います。

mysql> SELECT kamoku.kamokumei, kamoku.kyoushitsu, kamoku.youbi, kamoku.jigen FROM gakusei NATURAL INNER JOIN rishu NATURAL INNER JOIN kamoku
WHERE gakusei.namae='田中花子' ORDER BY CASE kamoku.youbi when '日' then 1 when '月' then 2 when '火' then 3 when '水' then 4 when '木' then 5 when '金' then 6 when '土' then 7 END,　kamoku.jigen;
+--------------------------+------------+-------+-------+
| kamokumei                | kyoushitsu | youbi | jigen |
+--------------------------+------------+-------+-------+
| データベース             | 1203       | 月    | 2     |
| ネットワーク基礎         | 2111       | 火    | 1     |
| 英語1                    | 3221       | 火    | 2     |
| プロジェクト1            | 1211       | 水    | 3     |
| 体育1                    | 体育館     | 木    | 3     |
+--------------------------+------------+-------+-------+
5 rows in set (0.03 sec)

mysql> SELECT kamoku.kamokumei, kamoku.kyoushitsu, kamoku.youbi, kamoku.jigen FROM gakusei NATURAL INNER JOIN rishu NATURAL INNER JOIN kamoku
WHERE gakusei.namae='山田聡' ORDER BY CASE kamoku.youbi when '日' then 1 when '月' then 2 when '火' then 3 when '水' then 4 when '木' then 5 w
hen '金' then 6 when '土' then 7 END, kamoku.jigen;
+--------------------------------------+------------+-------+-------+
| kamokumei                            | kyoushitsu | youbi | jigen |
+--------------------------------------+------------+-------+-------+
| オペレーティングシステム             | 1203       | 月    | 3     |
| ネットワーク基礎                     | 2111       | 火    | 1     |
| プロジェクト1                        | 1211       | 水    | 3     |
| 体育1                                | 体育館     | 木    | 3     |
| 英語2                                | 3221       | 金    | 2     |
+--------------------------------------+------------+-------+-------+
5 rows in set (0.00 sec)

mysql> SELECT kamoku.kamokumei, kamoku.kyoushitsu, kamoku.youbi, kamoku.jigen FROM gakusei NATURAL INNER JOIN rishu NATURAL INNER JOIN kamoku
WHERE gakusei.namae='佐藤美那子' ORDER BY CASE kamoku.youbi when '日' then 1 when '月' then 2 when '火' then 3 when '水' then 4 when '木' then
5 when '金' then 6 when '土' then 7 END, kamoku.jigen;
+--------------------------------------+--------------+-------+-------+
| kamokumei                            | kyoushitsu   | youbi | jigen |
+--------------------------------------+--------------+-------+-------+
| オペレーティングシステム             | 1203         | 月    | 3     |
| ネットワーク基礎                     | 2111         | 火    | 1     |
| 体育2                                | グランド     | 火    | 2     |
| プロジェクト1                        | 1211         | 水    | 3     |
| 英語2                                | 3221         | 金    | 2     |
+--------------------------------------+--------------+-------+-------+
5 rows in set (0.00 sec)

mysql> SELECT kamoku.kamokumei, kamoku.kyoushitsu, kamoku.youbi, kamoku.jigen FROM gakusei NATURAL INNER JOIN rishu NATURAL INNER JOIN kamoku
WHERE gakusei.namae='鈴木博' ORDER BY CASE kamoku.youbi when '日' then 1 when '月' then 2 when '火' then 3 when '水' then 4 when '木' then 5 w
hen '金' then 6 when '土' then 7 END, kamoku.jigen;
+--------------------------------------+--------------+-------+-------+
| kamokumei                            | kyoushitsu   | youbi | jigen |
+--------------------------------------+--------------+-------+-------+
| オペレーティングシステム             | 1203         | 月    | 3     |
| プログラミング入門                   | 2111         | 火    | 2     |
| 体育2                                | グランド     | 火    | 2     |
| プロジェクト2                        | 1212         | 水    | 4     |
| 英語2                                | 3221         | 金    | 2     |
+--------------------------------------+--------------+-------+-------+
5 rows in set (0.00 sec)

【4.2】

データベースjikanwaribaseを用いて、受講学生の受講単位が10単位を超える学生の学生証番号、氏名漢字、合計単位数のデータを表示せよ。

mysql> SELECT gakusei.gakuseino, gakusei.namae, SUM(kamoku.tani) AS 
total_tani FROM gakusei NATURAL INNER JOIN rishu NATURAL INNER JOIN kamoku 
HAVING total_tani > 10;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'jikanwaribase.gakusei.gakuseino'; this is incompatible with sql_mode=only_full_group_by

どうやら、GROUP BY句を用いていないことがエラーの一端みたいです。

mysql> SELECT gakusei.gakuseino, gakusei.namae, SUM(kamoku.tani) AS 
total_tani FROM gakusei NATURAL INNER JOIN rishu NATURAL INNER JOIN kamoku 
GROUP BY gakusei.gakuseino;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jikanwaribase.gakusei.namae' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

今度は、SELECT文の引数の2番目について、GROUP BY句で言及していないことがエラーの一端みたいです。

gakusei.gakuseinoとgakusei.namaeの2つを集約化すればいいのかなと考えました。

mysql> SELECT gakusei.gakuseino, gakusei.namae, SUM(kamoku.tani) 
FROM gakusei NATURAL INNER JOIN rishu NATURAL INNER JOIN kamoku 
GROUP BY gakusei.gakuseino, gakusei.namae;
+-----------+-----------------+------------------+
| gakuseino | namae           | SUM(kamoku.tani) |
+-----------+-----------------+------------------+
| 80AB0003  | 佐藤美那子      |                9 |
| 80AB0002  | 山田聡          |                9 |
| 80AB0001  | 田中花子        |               11 |
| 80AB0004  | 鈴木博          |                9 |
+-----------+-----------------+------------------+
4 rows in set (0.05 sec)

ふう。
後は、HAVING句を用いて条件を設定してレコードを抽出してやればいいですね。

mysql> SELECT gakusei.gakuseino, gakusei.namae, SUM(kamoku.tani) AS total_tani FROM gakusei NATURAL INNER JOIN rishu NATURAL INNER JOIN kamoku GROUP BY gakusei.gakuseino, gakusei.namae HAVING SUM(kamoku.tani) > 10;
+-----------+--------------+------------+
| gakuseino | namae        | total_tani |
+-----------+--------------+------------+
| 80AB0001  | 田中花子     |         11 |
+-----------+--------------+------------+
1 row in set (0.03 sec)

​

サポート、本当にありがとうございます。サポートしていただいた金額は、知的サイドハッスルとして取り組んでいる、個人研究の費用に充てさせていただきますね♪