今回は初心者訓練所#08として、前回紹介した「テーブル結合」の中でも一番利用される「内部結合」について実践的な問題を通して説明していきます。問題は2問用意してあり、それぞれ「内部結合」を使用した解答例について解説を行います。「内部結合」をしっかり覚えて、SQLの武器を身に付け、ビックデータに立向かいましょう。
(1問目)受注商品の一覧表示
1問目は、ER図にある、商品マスタ、倉庫マスタ、受注データ、受注明細データの4つのテーブルを利用した内部結合になります。
【問題】
2023年3月に取引があった受注の商品一覧を表示しなさい。
表示項目は以下とする。(エイリアスを使用し→の項目名とする)
・WH_CODE → W_CD
・WH_NAME → W_NM
・ITEM_CODE → I_CD
・ITEM_NAME → I_NM
・ORDER_NO → OD_NO
・QUANTITY → OD_QT
表示順
1. 倉庫コードの昇順
2. 商品コードの昇順
3. 受注数量の降順
【使用するテーブルのER図(半角:物理名/全角:論理名)】
※ER図(Entity Relationship Diagram)とは、テーブル(Entity)とテーブル同士の関連(Relationship)を図に表したもので、データベース設計における代表的な設計図のことです。詳細の説明は、ここでは省かせていただきます。
【準備】
使用するテーブルを作成し、データを挿入します。テーブルが多くて大変ですが、頑張りましょう。(1)商品マスタのCREATE文とINSERT文
CREATE TABLE ITEM_MST (ITEM_CODE TEXT NOT NULL,ITEM_NAME TEXT,PRIMARY KEY(ITEM_CODE)); |
INSERT INTO ITEM_MST (ITEM_CODE,ITEM_NAME) VALUES ('100','BANANA'), ('110','APPLE'), ('120','ORANGE') , ('130','GRAPE'), ('140','PEAR'), ('150','PERSIMMON') , ('160','STRAWBERRY'), ('170','MELON'), ('180','WATERMELON'); |
(2)倉庫マスタのCREATE文とINSERT文
CREATE TABLE WH_MST (WH_CODE TEXT NOT NULL,WH_NAME TEXT,PRIMARY KEY (WH_CODE)); |
INSERT INTO WH_MST (WH_CODE,WH_NAME) VALUES ('100','WH_NO1'), ('200','WH_NO2'),('300','WH_NO3'); |
(3)受注テーブルのCREATE文とINSERT文
CREATE TABLE ORDER_TBL (ORDER_NO TEXT NOT NULL,ORDER_DATE TEXT NOT NULL,WH_CODE TEXT NOT NULL,ORDER_AMNT INTEGER NOT NULL DEFAULT '0',PRIMARY KEY(ORDER_NO)); |
INSERT INTO ORDER_TBL (ORDER_NO,ORDER_DATE,WH_CODE,ORDER_AMNT) VALUES ('OR010','2023-02-28','100','495'), ('OR100','2023-03-01','100','1640') , ('OR101','2023-03-05','200','2680'), ('OR102','2023-03-10','200','720') , ('OR103','2023-03-20','300','1780'), ('OR104','2023-03-31','200','3160') , ('OR200','2023-04-01','100','3560'); |
(4)受注明細テーブルのCREATE文とINSERT文
CREATE TABLE ORDER_DETAILS (ORDER_NO TEXT,OD_ROW_NO INTEGER,ITEM_CODE TEXT NOT NULL,UNITPRICE INTEGER NOT NULL DEFAULT '0',QUANTITY INTEGER NOT NULL DEFAULT '1',PRIMARY KEY(ORDER_NO,OD_ROW_NO)); |
INSERT INTO ORDER_DETAILS (ORDER_NO,OD_ROW_NO,ITEM_CODE,UNITPRICE,QUANTITY) VALUES ('OR010','1','150','99','5'), ('OR100','1','100','100','2') , ('OR100','2','110','120','4'), ('OR100','3','120','140','3') , ('OR100','4','130','540','1'), ('OR101','1','110','120','3') , ('OR101','2','120','140','5'), ('OR101','3','130','540','3') , ('OR102','1','110','120','6'), ('OR103','1','120','140','5') , ('OR103','2','130','540','2'), ('OR104','1','100','100','1') , ('OR104','2','110','120','5'), ('OR104','3','120','140','6') , ('OR104','4','130','540','3'), ('OR200','1','160','890','4'); |
※表示されるデータの出力フォーマットを調整します。(ログイン後に1回実施すれば大丈夫です)
.headers on (ヘッダ情報を出力)
.mode column (カラム毎に左揃え出力)
【解答例】
SELECT T1.WH_CODE AS W_CD , T3.WH_NAME AS W_NM , T2.ITEM_CODE AS I_CD , T4.ITEM_NAME AS I_NM , T1.ORDER_NO AS OD_NO , T2.QUANTITY AS OD_QT FROM ORDER_TBL AS T1 -- ① INNER JOIN ORDER_DETAILS AS T2 -- ② ON T1.ORDER_NO = T2.ORDER_NO INNER JOIN WH_MST AS T3 -- ③ ON T1.WH_CODE = T3.WH_CODE INNER JOIN ITEM_MST AS T4 -- ④ ON T2.ITEM_CODE = T4.ITEM_CODE WHERE T1.ORDER_DATE BETWEEN '2023-03-01' AND '2023-03-31' -- ⑤ ORDER BY T1.WH_CODE ASC , T2.ITEM_CODE ASC , T2.QUANTITY DESC; -- ⑥ |
【説明】
① 受注日を絞り込むために、受注テーブルをメインテーブルとしています。
② 受注数量を取得するために、受注明細テーブルを受注番号で内部結合しています。
③ 倉庫名を取得するために、倉庫マスタを倉庫コードで内部結合しています。
④ 商品名を取得するために、商品マスタを受注明細データの商品コードで内部結合しています。
⑤ BETWEEN句で2023年3月の受注データを絞り込んでいます。
⑥ 受注数量は降順で表示するため、DESCを指定しています。
【結果】
受注日が2023年3月に含まれるデータが表示されていることが確認できます。4テーブルを使用しているので、各テーブルはAS句で別名を付けて簡素化しています。
テーブルが多いとSQL文も読み難くなるので、各項目の先頭にテーブルの別名を付けて、どのテーブルの項目か分かり易いように記述しています。
(2問目)受注商品の集計
2問目は、集計関数、GROUP BY句を使用した内部結合です。
【問題】
2023年4月5日に受注した商品の受注数量と受注金額を商品毎に集計しなさい。受注金額は、単価(UNITPRICE)と受注数量(QUANTITY)を掛けて求めること。
表示項目は以下とする。(エイリアスを使用し→の項目名とする)
・ITEM_CODE → I_CD
・ITEM_NAME → I_NM
・QUANTITY → OD_QT
・(UNITPRICE * QUANTITY) → OD_AMT
表示順
1.受注金額の降順
2.商品コードの昇順
【使用するテーブルのER図(半角:物理名/全角:論理名)】
【準備】
商品マスタのデータは1問目で挿入したデータを使用します。受注テーブルと受注明細テーブルにデータを追加で挿入します。
(1)受注テーブルのINSERT文
INSERT INTO ORDER_TBL (ORDER_NO,ORDER_DATE,WH_CODE,ORDER_AMNT) VALUES ('OR410','2023-04-04','100','495'), ('OR500','2023-04-05','100','1640') , ('OR501','2023-04-05','200','2680'), ('OR502','2023-04-05','200','720') , ('OR503','2023-04-05','300','1780'), ('OR504','2023-04-05','200','3160') , ('OR600','2023-04-05','100','3240'), ('OR510','2023-04-05','300','3180') , ('OR530','2023-04-05','300','888'); |
(2)受注明細テーブルのINSERT文
INSERT INTO ORDER_DETAILS (ORDER_NO,OD_ROW_NO,ITEM_CODE,UNITPRICE,QUANTITY) VALUES ('OR410','1','150','99','5'), ('OR500','1','100','100','2') , ('OR500','2','110','120','4'), ('OR500','3','120','140','3') , ('OR500','4','130','540','1'), ('OR501','1','110','120','3') , ('OR501','2','120','140','5'), ('OR501','3','130','540','3') , ('OR502','1','110','120','6'), ('OR503','1','120','140','5') , ('OR503','2','130','540','2'), ('OR504','1','100','100','1') , ('OR504','2','110','120','5'), ('OR504','3','120','140','6') , ('OR504','4','130','540','3'), ('OR600','1','160','810','4') , ('OR510','1','100','100','7'), ('OR510','2','110','120','9') , ('OR510','3','120','140','10'), ('OR530','1','190','888','1'); |
【解答例】
SELECT T2.ITEM_CODE AS I_CD , T3.ITEM_NAME AS I_NM , SUM(T2.QUANTITY) AS OD_QT -- ④ , SUM(T2.UNITPRICE * T2.QUANTITY) AS OD_AMT -- ⑤ FROM ORDER_TBL AS T1 -- ① INNER JOIN ORDER_DETAILS AS T2 -- ② ON T1.ORDER_NO = T2.ORDER_NO INNER JOIN ITEM_MST AS T3 -- ③ ON T2.ITEM_CODE = T3.ITEM_CODE WHERE -- ⑥ T1.ORDER_DATE = '2023-04-05' GROUP BY -- ⑦ T2.ITEM_CODE , T3.ITEM_NAME ORDER BY -- ⑧ OD_AMT DESC , T2.ITEM_CODE ASC; |
【説明】
① 受注日を絞り込むために、受注テーブルをメインテーブルとしています。
② 受注数量を取得するために、受注明細テーブルを受注番号で内部結合しています。
③ 商品名を取得するために、商品マスタを受注明細データの商品コードで内部結合しています。
④ SUM関数を使用して、商品コード、商品名毎に受注数量を集計しています。
⑤ SUM関数を使用して、商品コード、商品名毎に単価と数量を掛けた値を集計しています。
⑥ 受注日が2023年4月5日の受注データを絞り込んでいます。
⑦ 商品コードと商品名をGROUP BY句でグルーピングしています。(表示項目に商品名が含まれているため、ここでも商品名を指定しています。表示項目に商品名が指定されていない場合は、商品名は不要です。)
⑧ 単価と数量を掛ける計算式を、AS句で変換した「OD_AMT」をそのままソート項目に使用しています。
【結果】
受注日が2023年4月5日のデータが、受注金額の降順、受注金額が同じ場合は商品コードの昇順で表示されていることが確認できます。よく利用される、データ集計と内部結合の組合せの例を紹介しました。今まで紹介してきたSQL文に比べ、より実践的なSQL文となりますが、もう皆さんにとっては簡単だったかも知れません。
おすすめの学習コンテンツ
本記事を読み終わって、さらに学びたい方、次は問題にチャレンジしてみたい方には、以下の無料コンテンツをご用意しておりますので、ぜひ活用してみてください!
SQLiteについて
・本記事で使用したデータベース「SQLite」について解説する資料です。
SQLiteとは?他のデータベースとの違いも解説
SQLの教科書
・一般的な解説から、実際にSQLを動かす操作イメージまで幅広く解説する資料です。
「SQLの教科書」ビックデータ時代の必須科目
SQL無料トライアルテスト
・TOPSIC-SQLの無料トライアルテストをご受験していただけます。
TOPSIC-SQL無料トライアルテスト
SQLコンテスト
・簡単な会員登録をするだけでSQLのオンラインコンテストに無料で参加できます。
過去に開催されたコンテストの問題にもチャレンジできます。
TOPSIC SQL CONTEST
SQL基礎解説YouTube
・「TOPSIC-SQL」を元にしたオンラインウェビナーです。
SQL-BOOT CAMP #1
ビッグデータ時代の必須科目SQLを学ぶ「実戦で通用する武器を身につけよう!」
まとめ
本記事では、初心者訓練所の第8弾として、テーブル結合の中でもよく利用される「内部結合」について説明しました。実践形式の問題に対しての解答を通して、「内部結合」の使い方のイメージがついたのではないでしょうか。それでは、次の初心者訓練所でまたお会いしましょう!
(株)システムインテグレータ インキュベーション部 元鬼軍曹 久保 司
- カテゴリ:
- SQL‐BOOTCAMP