解答例 - 実習課題２ - ９. ビュー

実習課題 2

8章の実習課題 2 で作成した SQL 文をもとに、ビューを作成しなさい。

• ビュー名は OfficeCheckView とする。
• select * from OfficeCheckView; としたときに、8章の実習課題 2 の結果と同じになるようにすればよい。

解答例

CREATE VIEW OfficeCheckView AS
   SELECT c.c_name AS 顧客名,       co.office AS 顧客管理営業所名,
          e.e_name AS 販売従業員名, eo.office AS 販売営業所名
      FROM (
         SELECT a.c_num, c.office AS c_office, a.employee, e.office AS e_office
            FROM accept_order a
               JOIN customer c ON a.c_num = c.c_num
               JOIN employee e ON a.employee = e.e_num
         EXCEPT
         SELECT a.c_num, e.office, a.employee, c.office
            FROM accept_order a
               JOIN customer c ON a.c_num = c.c_num
               JOIN employee e ON a.employee = e.e_num
         ) data
         JOIN customer c ON data.c_num = c.c_num
         JOIN office co ON data.c_office = co.o_num
         JOIN employee e ON data.employee = e.e_num
         JOIN office eo ON data.e_office = eo.o_num;