落書きノート

ふと自分が気になった事を書いてます

SQLで問題を解く

今回は主に表の結合を取り扱ってます。Oracle10gではテーブルの3つ以上の結合にNATURAL JOINを使うとバグで動作がおかしくなったようですが、Oracle12cなので、そのような動作にはなりませんでした。

SQL> select cust_name
  2  from customer, slip
  3  where customer.cust_id = slip.cust_id
  4  and slip.slip_id = 8;

CUST_NAME
--------------------
西日本商会

SQL> select cust_name
  2  from customer natural join slip
  3  where slip_id = 8;

CUST_NAME
--------------------
西日本商会

SQL> select cust_name
  2  from slip natural join customer
  3  where slip_id = 8;

CUST_NAME
--------------------
西日本商会

SQL> select cust_name
  2  from customer join slip on (customer.cust_id = slip.cust_id)
  3  where slip_id = 8;

CUST_NAME
--------------------
西日本商会

SQL> select * from customer, slip;

   CUST_ID CUST_NAME        ADDRESS                     SLIP_ID    CUST_ID SLIP_DAT
---------- -------------------- ---------------------------------------- ---------- ---------- --------
         1 古千谷商店           東京都足立区古千谷本町                            1          2 95-06-19
         1 古千谷商店           東京都足立区古千谷本町                            2          5 95-06-23
         1 古千谷商店           東京都足立区古千谷本町                            3          4 95-06-23
         1 古千谷商店           東京都足立区古千谷本町                            4          2 95-06-26
         1 古千谷商店           東京都足立区古千谷本町                            5          3 95-06-29
         1 古千谷商店           東京都足立区古千谷本町                            6          2 95-07-03
         1 古千谷商店           東京都足立区古千谷本町                            7          1 95-07-03
         1 古千谷商店           東京都足立区古千谷本町                            8          5 95-07-07
         2 千代田商事           東京都千代田区大手町                              1          2 95-06-19
         2 千代田商事           東京都千代田区大手町                              2          5 95-06-23
         2 千代田商事           東京都千代田区大手町                              3          4 95-06-23
         2 千代田商事           東京都千代田区大手町                              4          2 95-06-26
         2 千代田商事           東京都千代田区大手町                              5          3 95-06-29
         2 千代田商事           東京都千代田区大手町                              6          2 95-07-03
         2 千代田商事           東京都千代田区大手町                              7          1 95-07-03
         2 千代田商事           東京都千代田区大手町                              8          5 95-07-07
         3 竹の塚マート         東京都足立区竹の塚                                1          2 95-06-19
         3 竹の塚マート         東京都足立区竹の塚                                2          5 95-06-23
         3 竹の塚マート         東京都足立区竹の塚                                3          4 95-06-23
         3 竹の塚マート         東京都足立区竹の塚                                4          2 95-06-26
         3 竹の塚マート         東京都足立区竹の塚                                5          3 95-06-29
         3 竹の塚マート         東京都足立区竹の塚                                6          2 95-07-03

   CUST_ID CUST_NAME        ADDRESS                     SLIP_ID    CUST_ID SLIP_DAT
---------- -------------------- ---------------------------------------- ---------- ---------- --------
         3 竹の塚マート         東京都足立区竹の塚                                7          1 95-07-03
         3 竹の塚マート         東京都足立区竹の塚                                8          5 95-07-07
         4 竹里屋               埼玉県春日部市竹里                                1          2 95-06-19
         4 竹里屋               埼玉県春日部市竹里                                2          5 95-06-23
         4 竹里屋               埼玉県春日部市竹里                                3          4 95-06-23
         4 竹里屋               埼玉県春日部市竹里                                4          2 95-06-26
         4 竹里屋               埼玉県春日部市竹里                                5          3 95-06-29
         4 竹里屋               埼玉県春日部市竹里                                6          2 95-07-03
         4 竹里屋               埼玉県春日部市竹里                                7          1 95-07-03
         4 竹里屋               埼玉県春日部市竹里                                8          5 95-07-07
         5 西日本商会           大阪府大阪市西区北堀江                            1          2 95-06-19
         5 西日本商会           大阪府大阪市西区北堀江                            2          5 95-06-23
         5 西日本商会           大阪府大阪市西区北堀江                            3          4 95-06-23
         5 西日本商会           大阪府大阪市西区北堀江                            4          2 95-06-26
         5 西日本商会           大阪府大阪市西区北堀江                            5          3 95-06-29
         5 西日本商会           大阪府大阪市西区北堀江                            6          2 95-07-03
         5 西日本商会           大阪府大阪市西区北堀江                            7          1 95-07-03
         5 西日本商会           大阪府大阪市西区北堀江                            8          5 95-07-07

40行が選択されました。

SQL> select cust_name, goods_name
  2  from customer, goods 
  3  where customer.cust_id = goods.goods_id;

CUST_NAME        GOODS_NAME
-------------------- --------------------
古千谷商店           片手鍋
千代田商事           両手鍋
竹の塚マート         中華鍋
竹里屋               フライパン

SQL> select slip_date 受注日付,
  2  cust_name 顧客名,
  3  goods_name 商品名,
  4  qty 数量 
  5  from customer, goods, slip, account
  6  where customer.cust_id = slip.cust_id
  7  and goods.goods_id = account.goods_id
  8  and slip.slip_id = account.slip_id
  9  order by slip_date;

受注日付 顧客名               商品名                     数量
-------- -------------------- -------------------- ----------
95-06-19 千代田商事           両手鍋                       10
95-06-19 千代田商事           中華鍋                        5
95-06-19 千代田商事           片手鍋                       15
95-06-23 竹里屋               中華鍋                        5
95-06-23 西日本商会           片手鍋                       20
95-06-23 竹里屋               フライパン                    5
95-06-26 千代田商事           片手鍋                       15
95-06-26 千代田商事           両手鍋                       10
95-06-29 竹の塚マート         中華鍋                        7
95-07-03 千代田商事           中華鍋                        5
95-07-03 千代田商事           両手鍋                       10
95-07-03 古千谷商店           両手鍋                        5
95-07-03 千代田商事           片手鍋                       15
95-07-03 古千谷商店           片手鍋                        8
95-07-03 千代田商事           フライパン                    5
95-07-07 西日本商会           片手鍋                       20

16行が選択されました。

SQL> select slip_date 受注日付,
  2  cust_name 顧客名,
  3  goods_name 商品名,
  4  qty 数量
  5  from customer 
  6  join slip on customer.cust_id = slip.cust_id
  7  join account on slip.slip_id = account.slip_id
  8  join goods on account.goods_id = goods.goods_id
  9  order by slip_date;

受注日付 顧客名               商品名                     数量
-------- -------------------- -------------------- ----------
95-06-19 千代田商事           両手鍋                       10
95-06-19 千代田商事           中華鍋                        5
95-06-19 千代田商事           片手鍋                       15
95-06-23 竹里屋               中華鍋                        5
95-06-23 西日本商会           片手鍋                       20
95-06-23 竹里屋               フライパン                    5
95-06-26 千代田商事           片手鍋                       15
95-06-26 千代田商事           両手鍋                       10
95-06-29 竹の塚マート         中華鍋                        7
95-07-03 千代田商事           中華鍋                        5
95-07-03 千代田商事           両手鍋                       10
95-07-03 古千谷商店           両手鍋                        5
95-07-03 千代田商事           片手鍋                       15
95-07-03 古千谷商店           片手鍋                        8
95-07-03 千代田商事           フライパン                    5
95-07-07 西日本商会           片手鍋                       20

16行が選択されました。

SQL> select slip_date 受注日付,
  2  cust_name 顧客名,
  3  goods_name 商品名,
  4  qty 数量
  5  from customer
  6  join slip using(cust_id)
  7  join account using(slip_id)
  8  join goods using(goods_id)
  9  order by slip_date;

受注日付 顧客名               商品名                     数量
-------- -------------------- -------------------- ----------
95-06-19 千代田商事           両手鍋                       10
95-06-19 千代田商事           中華鍋                        5
95-06-19 千代田商事           片手鍋                       15
95-06-23 竹里屋               中華鍋                        5
95-06-23 西日本商会           片手鍋                       20
95-06-23 竹里屋               フライパン                    5
95-06-26 千代田商事           片手鍋                       15
95-06-26 千代田商事           両手鍋                       10
95-06-29 竹の塚マート         中華鍋                        7
95-07-03 千代田商事           中華鍋                        5
95-07-03 千代田商事           両手鍋                       10
95-07-03 古千谷商店           両手鍋                        5
95-07-03 千代田商事           片手鍋                       15
95-07-03 古千谷商店           片手鍋                        8
95-07-03 千代田商事           フライパン                    5
95-07-07 西日本商会           片手鍋                       20

16行が選択されました。

SQL> select slip_date 受注日付,
  2  cust_name 顧客名,
  3  goods_name 商品名,
  4  qty 数量
  5  from customer
  6  natural join slip
  7  natural join account
  8  natural join goods
  9  order by slip_date;

受注日付 顧客名               商品名                     数量
-------- -------------------- -------------------- ----------
95-06-19 千代田商事           両手鍋                       10
95-06-19 千代田商事           中華鍋                        5
95-06-19 千代田商事           片手鍋                       15
95-06-23 竹里屋               中華鍋                        5
95-06-23 西日本商会           片手鍋                       20
95-06-23 竹里屋               フライパン                    5
95-06-26 千代田商事           片手鍋                       15
95-06-26 千代田商事           両手鍋                       10
95-06-29 竹の塚マート         中華鍋                        7
95-07-03 千代田商事           中華鍋                        5
95-07-03 千代田商事           両手鍋                       10
95-07-03 古千谷商店           両手鍋                        5
95-07-03 千代田商事           片手鍋                       15
95-07-03 古千谷商店           片手鍋                        8
95-07-03 千代田商事           フライパン                    5
95-07-07 西日本商会           片手鍋                       20

16行が選択されました。

SQL> select slip_date 受注日付,
  2  cust_name 顧客名,
  3  goods_name 商品名,
  4  qty*unit_price 受注額
  5  from customer, goods, slip, account
  6  where customer.cust_id = slip.cust_id
  7  and goods.goods_id = account.goods_id
  8  and slip.slip_id = account.slip_id
  9  order by slip_date;

受注日付 顧客名               商品名                   受注額
-------- -------------------- -------------------- ----------
95-06-19 千代田商事           両手鍋                     8000
95-06-19 千代田商事           中華鍋                     9000
95-06-19 千代田商事           片手鍋                     7500
95-06-23 竹里屋               中華鍋                     9000
95-06-23 西日本商会           片手鍋                    10000
95-06-23 竹里屋               フライパン                 7500
95-06-26 千代田商事           片手鍋                     7500
95-06-26 千代田商事           両手鍋                     8000
95-06-29 竹の塚マート         中華鍋                    12600
95-07-03 千代田商事           中華鍋                     9000
95-07-03 千代田商事           両手鍋                     8000
95-07-03 古千谷商店           両手鍋                     4000
95-07-03 千代田商事           片手鍋                     7500
95-07-03 古千谷商店           片手鍋                     4000
95-07-03 千代田商事           フライパン                 7500
95-07-07 西日本商会           片手鍋                    10000

16行が選択されました。

SQL> select slip_date 受注日付,
  2  cust_name 顧客名,
  3  goods_name 商品名,
  4  qty*unit_price 受注額
  5  from customer
  6  join slip on customer.cust_id = slip.cust_id
  7  join account on slip.slip_id = account.slip_id
  8  join goods on account.goods_id = goods.goods_id
  9  order by slip_date;

受注日付 顧客名               商品名                   受注額
-------- -------------------- -------------------- ----------
95-06-19 千代田商事           両手鍋                     8000
95-06-19 千代田商事           中華鍋                     9000
95-06-19 千代田商事           片手鍋                     7500
95-06-23 竹里屋               中華鍋                     9000
95-06-23 西日本商会           片手鍋                    10000
95-06-23 竹里屋               フライパン                 7500
95-06-26 千代田商事           片手鍋                     7500
95-06-26 千代田商事           両手鍋                     8000
95-06-29 竹の塚マート         中華鍋                    12600
95-07-03 千代田商事           中華鍋                     9000
95-07-03 千代田商事           両手鍋                     8000
95-07-03 古千谷商店           両手鍋                     4000
95-07-03 千代田商事           片手鍋                     7500
95-07-03 古千谷商店           片手鍋                     4000
95-07-03 千代田商事           フライパン                 7500
95-07-07 西日本商会           片手鍋                    10000

16行が選択されました。

SQL> select slip_date 受注日付,
  2  cust_name 顧客名,
  3  goods_name 商品名,
  4  qty*unit_price 受注額
  5  from customer
  6  join slip using(cust_id)
  7  join account using(slip_id) 
  8  join goods using(goods_id)
  9  order by slip_date;

受注日付 顧客名               商品名                   受注額
-------- -------------------- -------------------- ----------
95-06-19 千代田商事           両手鍋                     8000
95-06-19 千代田商事           中華鍋                     9000
95-06-19 千代田商事           片手鍋                     7500
95-06-23 竹里屋               中華鍋                     9000
95-06-23 西日本商会           片手鍋                    10000
95-06-23 竹里屋               フライパン                 7500
95-06-26 千代田商事           片手鍋                     7500
95-06-26 千代田商事           両手鍋                     8000
95-06-29 竹の塚マート         中華鍋                    12600
95-07-03 千代田商事           中華鍋                     9000
95-07-03 千代田商事           両手鍋                     8000
95-07-03 古千谷商店           両手鍋                     4000
95-07-03 千代田商事           片手鍋                     7500
95-07-03 古千谷商店           片手鍋                     4000
95-07-03 千代田商事           フライパン                 7500
95-07-07 西日本商会           片手鍋                    10000

16行が選択されました。

SQL> select slip_date 受注日付,
  2  cust_name 顧客名,
  3  goods_name 商品名,
  4  qty*unit_price 受注額
  5  from customer
  6  natural join slip
  7  natural join account
  8  natural join goods
  9  order by slip_date;

受注日付 顧客名               商品名                   受注額
-------- -------------------- -------------------- ----------
95-06-19 千代田商事           両手鍋                     8000
95-06-19 千代田商事           中華鍋                     9000
95-06-19 千代田商事           片手鍋                     7500
95-06-23 竹里屋               中華鍋                     9000
95-06-23 西日本商会           片手鍋                    10000
95-06-23 竹里屋               フライパン                 7500
95-06-26 千代田商事           片手鍋                     7500
95-06-26 千代田商事           両手鍋                     8000
95-06-29 竹の塚マート         中華鍋                    12600
95-07-03 千代田商事           中華鍋                     9000
95-07-03 千代田商事           両手鍋                     8000
95-07-03 古千谷商店           両手鍋                     4000
95-07-03 千代田商事           片手鍋                     7500
95-07-03 古千谷商店           片手鍋                     4000
95-07-03 千代田商事           フライパン                 7500
95-07-07 西日本商会           片手鍋                    10000

16行が選択されました。

SQL> select slip.slip_id 伝票番号,
  2  sum(qty*unit_price) 受注額 
  3  from goods, slip, account
  4  where goods.goods_id = account.goods_id
  5  and slip.slip_id = account.slip_id
  6  group by slip.slip_id;

  伝票番号     受注額
---------- ----------
     1 24500
     6 32000
     2 10000
     4 15500
     5 12600
     8 10000
     7  8000
     3 16500

8行が選択されました。

SQL> select slip.slip_id 伝票番号,
  2  sum(qty*unit_price) 受注額
  3  from goods, slip, account
  4  where goods.goods_id = account.goods_id
  5  and slip.slip_id = account.slip_id
  6  group by slip.slip_id
  7  order by slip.slip_id;

  伝票番号     受注額
---------- ----------
     1 24500
     2 10000
     3 16500
     4 15500
     5 12600
     6 32000
     7  8000
     8 10000

8行が選択されました。

SQL> select slip.slip_id 伝票番号,
  2  sum(qty*unit_price) 受注額
  3  from slip
  4  join account on slip.slip_id = account.slip_id
  5  join goods on account.goods_id = goods.goods_id
  6  group by slip.slip_id
  7  order by slip.slip_id;

  伝票番号     受注額
---------- ----------
     1 24500
     2 10000
     3 16500
     4 15500
     5 12600
     6 32000
     7  8000
     8 10000

8行が選択されました。

SQL> select slip_id 伝票番号,
  2  sum(qty*unit_price) 受注額
  3  from slip
  4  natural join account
  5  natural join goods
  6  group by slip_id
  7  order by slip_id;

  伝票番号     受注額
---------- ----------
     1 24500
     2 10000
     3 16500
     4 15500
     5 12600
     6 32000
     7  8000
     8 10000

8行が選択されました。

SQL> select slip_id 伝票番号,
  2  sum(qty*unit_price) 受注額
  3  from slip
  4  join account using(slip_id)
  5  join goods using(goods_id)
  6  group by slip_id
  7  order by slip_id;

  伝票番号     受注額
---------- ----------
     1 24500
     2 10000
     3 16500
     4 15500
     5 12600
     6 32000
     7  8000
     8 10000

8行が選択されました。

SQL> select cust_name 顧客名,
  2  sum(qty*unit_price) 受注額
  3  from customer, goods, slip, account
  4  where customer.cust_id = slip.cust_id
  5  and goods.goods_id = account.goods_id
  6  and slip.slip_id = account.slip_id
  7  group by customer.cust_name
  8  order by customer.cust_name;

顧客名                   受注額
-------------------- ----------
千代田商事                72000
古千谷商店                 8000
竹の塚マート              12600
竹里屋                    16500
西日本商会                20000

SQL> select cust_name 顧客名,
  2  sum(qty*unit_price) 受注額
  3  from customer, goods, slip, account
  4  where customer.cust_id = slip.cust_id
  5  and goods.goods_id = account.goods_id
  6  and slip.slip_id = account.slip_id
  7  group by customer.cust_name;

顧客名                   受注額
-------------------- ----------
千代田商事                72000
西日本商会                20000
古千谷商店                 8000
竹の塚マート              12600
竹里屋                    16500

SQL> select cust_name 顧客名,
  2  sum(qty*unit_price) 受注額
  3  from customer
  4  join slip on customer.cust_id = slip.cust_id
  5  join account on slip.slip_id = account.slip_id
  6  join goods on account.goods_id = goods.goods_id
  7  group by customer.cust_name;

顧客名                   受注額
-------------------- ----------
千代田商事                72000
西日本商会                20000
古千谷商店                 8000
竹の塚マート              12600
竹里屋                    16500

SQL> select cust_name 顧客名,
  2  sum(qty*unit_price) 受注額
  3  from customer
  4  join slip using(cust_id) 
  5  join account using(slip_id)
  6  join goods using(goods_id)
  7  group by cust_name;

顧客名                   受注額
-------------------- ----------
千代田商事                72000
西日本商会                20000
古千谷商店                 8000
竹の塚マート              12600
竹里屋                    16500

SQL> select cust_name 顧客名,
  2  sum(qty*unit_price) 受注額
  3  from customer
  4  natural join slip
  5  natural join account
  6  natural join goods
  7  group by cust_name;

顧客名                   受注額
-------------------- ----------
千代田商事                72000
西日本商会                20000
古千谷商店                 8000
竹の塚マート              12600
竹里屋                    16500

SQL> select *         
  2  from emp
  3  where not (job is NULL);

     EMPNO ENAME      JOB          MGR HIREDATE    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 80-12-17      800           20
      7499 ALLEN      SALESMAN       7698 81-02-20     1600       300    30
      7521 WARD       SALESMAN       7698 81-02-22     1250       500    30
      7566 JONES      MANAGER        7839 81-04-02     2975           20
      7654 MARTIN     SALESMAN       7698 81-09-28     1250      1400    30
      7698 BLAKE      MANAGER        7839 81-05-01     2850           30
      7782 CLARK      MANAGER        7839 81-06-09     2450           10
      7788 SCOTT      ANALYST        7566 87-04-19     3000           20
      7839 KING       PRESIDENT       81-11-17      5000           10
      7844 TURNER     SALESMAN       7698 81-09-08     1500     0    30
      7876 ADAMS      CLERK          7788 87-05-23     1100           20
      7900 JAMES      CLERK          7698 81-12-03      950           30
      7902 FORD       ANALYST        7566 81-12-03     3000           20
      7934 MILLER     CLERK          7782 82-01-23     1300           10

14行が選択されました。

SQL> select *
  2  from emp
  3  where job = 'CLERK'
  4  and deptno = 10;

     EMPNO ENAME      JOB          MGR HIREDATE    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7934 MILLER     CLERK          7782 82-01-23     1300           10

SQL> select *
  2  from emp
  3  where job = 'CLERK'
  4  or deptno = 10;

     EMPNO ENAME      JOB          MGR HIREDATE    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 80-12-17      800           20
      7782 CLARK      MANAGER        7839 81-06-09     2450           10
      7839 KING       PRESIDENT       81-11-17      5000           10
      7876 ADAMS      CLERK          7788 87-05-23     1100           20
      7900 JAMES      CLERK          7698 81-12-03      950           30
      7934 MILLER     CLERK          7782 82-01-23     1300           10

6行が選択されました。