Ａ１章−１

(1) 実行結果を参考に次のようなSQLを作成しなさい。制約などは適切に付加すること。
・教科を管理するテーブルsubjectを作成し、行を追加する。
・生徒を管理するテーブルeducandを作成し、行を追加する。
・講師を管理するテーブルteacherを作成し、行を追加する。ただし次の条件を満たすこと。
	1. 講師は一つの教科のみを担当する。
・生徒の教科ごとの講師を管理するテーブルassignを作成し、行を追加する。ただし次の条件を満たすこと。
	1. 生徒は一つの教科ごとに一人の講師のみに習う。ただし、受講する教科の選択は自由。
なお、それぞれの列が何を意味しているかは、適宜判断すること。

odexer=> \i a1_1_1.sql
...

odexer=> select * from subject;
 code | name
------+------
    1 | 数学
    2 | 物理
    3 | 化学
(3 rows)

odexer=> select * from educand;
 code |    name
------+------------
    1 | 村越カズミ
    2 | 久保田大介
    3 | 大塚紗弥香
    4 | 川村ヒロキ
    5 | 伊久美哲平
    6 | 内田麻菜美
    7 | 山田ナオト
    8 | 岡田美恵子
    9 | 広瀬健太郎
(9 rows)

odexer=> select * from teacher;
 code |   name   | s_code
------+----------+--------
    1 | 小平邦彦 |      1
    2 | 湯川秀樹 |      2
    3 | 福井謙一 |      3
    4 | 白川英樹 |      3
    5 | 小柴昌俊 |      2
    6 | 広中平祐 |      1
(6 rows)

odexer=> select * from assign;
 e_code | s_code | t_code
--------+--------+--------
      1 |      1 |      1
      1 |      3 |      4
      2 |      1 |      6
      2 |      2 |      2
      2 |      3 |      3
      3 |      1 |      1
      4 |      1 |      6
      4 |      2 |      2
      4 |      3 |      4
      5 |      1 |      1
      5 |      2 |      5
      6 |      1 |      6
      6 |      2 |      2
      7 |      1 |      6
      7 |      2 |      5
      8 |      1 |      1
      8 |      2 |      2
      8 |      3 |      4
      9 |      1 |      6
      9 |      2 |      5
      9 |      3 |      3
(21 rows)


(2) 実行結果を参考に次のようなSQLを作成しなさい。
・生徒の教科ごとの講師を表示するビューassign_vを作成する。

odexer=> \i a1_1_2.sql
...

odexer=> select * from assign_v;
 番号 |    名前    | 教科 |   講師
------+------------+------+----------
    1 | 村越カズミ | 数学 | 小平邦彦
    1 | 村越カズミ | 物理 |
    1 | 村越カズミ | 化学 | 白川英樹
    2 | 久保田大介 | 数学 | 広中平祐
    2 | 久保田大介 | 物理 | 湯川秀樹
    2 | 久保田大介 | 化学 | 福井謙一
    3 | 大塚紗弥香 | 数学 | 小平邦彦
    3 | 大塚紗弥香 | 物理 |
    3 | 大塚紗弥香 | 化学 |
    4 | 川村ヒロキ | 数学 | 広中平祐
    4 | 川村ヒロキ | 物理 | 湯川秀樹
    4 | 川村ヒロキ | 化学 | 白川英樹
    5 | 伊久美哲平 | 数学 | 小平邦彦
    5 | 伊久美哲平 | 物理 | 小柴昌俊
    5 | 伊久美哲平 | 化学 |
    6 | 内田麻菜美 | 数学 | 広中平祐
    6 | 内田麻菜美 | 物理 | 湯川秀樹
    6 | 内田麻菜美 | 化学 |
    7 | 山田ナオト | 数学 | 広中平祐
    7 | 山田ナオト | 物理 | 小柴昌俊
    7 | 山田ナオト | 化学 |
    8 | 岡田美恵子 | 数学 | 小平邦彦
    8 | 岡田美恵子 | 物理 | 湯川秀樹
    8 | 岡田美恵子 | 化学 | 白川英樹
    9 | 広瀬健太郎 | 数学 | 広中平祐
    9 | 広瀬健太郎 | 物理 | 小柴昌俊
    9 | 広瀬健太郎 | 化学 | 福井謙一
(27 rows)

ヒント）
CROSS JOINとLEFT JOINを使用する。


(3) 実行結果を参考に次のようなSQLを作成しなさい。
・講師の人気が分かるようなビューteacher_vを作成する。

odexer=> \i a1_1_3.sql

odexer=> select * from teacher_v;
 番号 |   名前   | 教科 | 受講人数
------+----------+------+----------
    1 | 小平邦彦 | 数学 |        4
    2 | 湯川秀樹 | 物理 |        4
    3 | 福井謙一 | 化学 |        2
    4 | 白川英樹 | 化学 |        3
    5 | 小柴昌俊 | 物理 |        3
    6 | 広中平祐 | 数学 |        5
(6 rows)

ヒント）
相関副問い合わせを使用する。


(4) 実行結果を参考に次のようなSQLを作成しなさい。
・生徒番号から、その生徒が選択していない教科を表示する関数subject_fを作成する。

odexer=> \i a1_1_4.sql
CREATE FUNCTION

odexer=> select * from subject_f(1);
 code | name
------+------
    2 | 物理
(1 row)

odexer=> select * from subject_f(2);
 code | name
------+------
(0 rows)

odexer=> select * from subject_f(3);
 code | name
------+------
    2 | 物理
    3 | 化学
(2 rows)

ヒント）
NOT EXISTS演算子で相関副問い合わせを使用する。


(5) 実行結果を参考に次のようなSQLを作成しなさい。
・全ての教科を選択している生徒を表示するビューeducand_vを作成する。

odexer=> \i a1_1_5.sql
CREATE VIEW

odexer=> select * from educand_v;
 code |    name
------+------------
    2 | 久保田大介
    4 | 川村ヒロキ
    8 | 岡田美恵子
    9 | 広瀬健太郎
(4 rows)

ヒント）
上記(4)をヒントにして、NOT EXISTSの相関副問い合わせ演算子を入れ子で使用する。