６．複数テーブルの結合

以下のテーブルをもとに、演習を進める。（ここではもう、正規化していない saleテーブル は使わない。）
saleテーブルを正規化したproductテーブル、sale2テーブルを trysql に追加する場合は以下手順を確認の上、実施してください。
※「5.正規化とテーブル作成」のサンプル5-1～5-4を実行して、テーブルの作成とレコードの追加を実施している場合は必要ありません。
ただし、サンプル5-5を実行して、テーブルを削除した場合は削除したテーブルの作成とレコードを追加を行ってください。
まず、データ追加用SQLを以下保存先フォルダにダウンロードしてください。
【保存先フォルダ】 c:\mysql

次に、ダウンロードしたファイルをサクラエディタで開き、
ファイル内のSQLをHeidiSQLで実行してください。
最後に、HeidiSQLの更新ボタンをクリックし、product と sale2 が trysql の下にできていれば成功です。

[ product ]

product_code product_name product_price
N01 いちご 400
N02 いちじく 600
N03 りんご 140
N04 メロン 3500
N05 マンゴー 260
N06 バナナ 100
S01 七夕セット 1780
S02 常夏セット 2300
S03 秋の先取りセット 2500

[ sale2 ]

sale_id sale_date product_code product_count customer is_delivery note
101 2007/4/1 N03 20 A商店 false (NULL)
102 2007/4/2 N01 15 B商店 true (NULL)
103 2007/5/4 N01 2 A商店 false (NULL)
104 2007/5/12 N02 32 A商店パリ支店 true 空輸
105 2007/5/20 N05 18 C商店 false (NULL)
106 2007/6/7 N03 3 A商店 false (NULL)
107 2007/7/1 N04 12 W商店 true のしをつける
108 2007/7/7 S01 3 C商店 false (NULL)
109 2007/7/10 N05 10 B商店 false (NULL)
110 2007/8/1 S02 6 海の家 false (NULL)

６－１

販売データを確認 − 結合

分割されている複数テーブルを結合して表示する。

sale_id sale_date product_name product_price product_count total
101 2007/4/1 りんご 140 20 2800
102 2007/4/2 いちご 400 15 6000
103 2007/5/4 いちご 400 2 800
104 2007/5/12 いちじく 600 32 19200
105 2007/5/20 マンゴー 260 18 4680
106 2007/6/7 りんご 140 3 420
107 2007/7/1 メロン 3500 12 42000
108 2007/7/7 七夕セット 1780 3 5340
109 2007/7/10 マンゴー 260 10 2600
110 2007/8/1 常夏セット 2300 6 13800

キーワード：join 〜 on

【サンプルソースコード】

6-1.sql

/* 6-1．販売データを確認 − 結合 */ /* 　分割されている複数テーブルを結合して表示する */ /* 　selectでいちいちテーブル名を指定しているので、かなり長くなる */ select sale2.sale_id, sale2.sale_date, product.product_name, product.product_price, sale2.product_count, product.product_price * sale2.product_count as total from sale2 join product on sale2.product_code = product.product_code order by sale_id;

６－２

販売データを確認2 − 結合2

分割されている複数テーブルを結合して表示する。
※SQL文が煩雑になるのを避けるために、表に別名を定義する。

sale_id sale_date product_name product_price product_count total
101 2007/4/1 りんご 140 20 2800
102 2007/4/2 いちご 400 15 6000
103 2007/5/4 いちご 400 2 800
104 2007/5/12 いちじく 600 32 19200
105 2007/5/20 マンゴー 260 18 4680
106 2007/6/7 りんご 140 3 420
107 2007/7/1 メロン 3500 12 42000
108 2007/7/7 七夕セット 1780 3 5340
109 2007/7/10 マンゴー 260 10 2600
110 2007/8/1 常夏セット 2300 6 13800

キーワード：join 〜 on、as

【サンプルソースコード】

6-2.sql

/* 6-2．販売データを確認2 − 結合2 */ /* 　表に短い別名を付けてselectではそれを使うと、SQLが短めになる */ select s2.sale_id, s2.sale_date, p.product_name, p.product_price, s2.product_count, p.product_price * s2.product_count as total from sale2 as s2 join product as p on s2.product_code = p.product_code order by s2.sale_id;

６－３

販売データを確認3 − 結合3

分割されている複数テーブルを結合して表示する。
※joinを使わないやり方をしてみる。

sale_id sale_date product_name product_price product_count total
101 2007/4/1 りんご 140 20 2800
102 2007/4/2 いちご 400 15 6000
103 2007/5/4 いちご 400 2 800
104 2007/5/12 いちじく 600 32 19200
105 2007/5/20 マンゴー 260 18 4680
106 2007/6/7 りんご 140 3 420
107 2007/7/1 メロン 3500 12 42000
108 2007/7/7 七夕セット 1780 3 5340
109 2007/7/10 マンゴー 260 10 2600
110 2007/8/1 常夏セット 2300 6 13800

キーワード：where、as

【サンプルソースコード】

6-3.sql

/* 6-3．販売データを確認3 − 結合3 */ /* 　whereも利用できるが、もし抽出条件もあると、どうしても長くなる */ select s2.sale_id, s2.sale_date, p.product_name, p.product_price, s2.product_count, p.product_price * s2.product_count as total from sale2 as s2, product as p where s2.product_code = p.product_code order by s2.sale_id;

６－４

配送履歴を確認 − 結合と条件

いつ、何を、いくつ、どこに配送したかを表示する。
配送していない販売データは、表示する必要がない。

sale_date product_name product_count customer
2007/4/2 いちご 15 B商店
2007/5/12 いちじく 32 A商店パリ支店
2007/7/1 メロン 12 W商店

キーワード：join 〜 on、as、where

【サンプルソースコード】

6-4.sql

/* 6-4．配送履歴を確認 − 結合と条件 */ select s2.sale_date, p.product_name, s2.product_count, s2.customer from sale2 as s2 join product as p on s2.product_code = p.product_code where s2.is_delivery = true;

６－５

商品毎の売上総数を計算 − 結合とグループ化、集計

商品毎の販売総数を表示する。
ここでは、販売履歴がない商品は表示されなくてよい。

product_code product_name allcount
N01 いちご 17
N02 いちじく 32
N03 りんご 23
N04 メロン 12
N05 マンゴー 28
S01 七夕セット 3
S02 常夏セット 6

キーワード：join 〜 on、as、group by、集計関数

【サンプルソースコード】

6-5.sql

/* 6-5．商品毎の売上総数を計算 − 結合とグループ化、集計 */ /* 　ここでは、販売履歴がない商品は表示されない */ select p.product_code, p.product_name, sum(s2.product_count) as allcount from product as p join sale2 as s2 on p.product_code = s2.product_code group by p.product_code, p.product_name order by p.product_code;

６－６

商品毎の売上総数（販売履歴がない商品も含む） − 外部結合

商品毎の販売総数を表示する。
ここでは、販売履歴がない商品もあわせて表示する。

product_code product_name allcount
N01 いちご 17
N02 いちじく 32
N03 りんご 23
N04 メロン 12
N05 マンゴー 28
N06 バナナ 　
S01 七夕セット 3
S02 常夏セット 6
S03 秋の先取りセット 　

キーワード：left（またはright）join 〜 on、as、group by、集計関数

【サンプルソースコード】

6-6.sql

/* 6-6．商品毎の売上総数（販売履歴がない商品も含む） − 外部結合 */ /* 　ここでは、販売履歴がない商品もあわせて表示する */ /* 　outerを使うと、どちらかの表のデータはすべて表示することができる */ select p.product_code, p.product_name, sum(s2.product_count) as allcount from product as p left join sale2 as s2 on p.product_code = s2.product_code group by p.product_code, p.product_name order by p.product_code;

６－７

単価1000円以上の商品の販売履歴を確認

単価が1000円以上の商品の販売履歴を表示する。

sale_id sale_date customer is_delivery note
107 2007/7/1 W商店 true のしをつける
108 2007/7/7 C商店 false (NULL)
110 2007/8/1 海の家 false (NULL)

キーワード：join 〜 on、as、where

【サンプルソースコード】

6-7.sql

/* 6-7．単価1000円以上の商品の販売履歴を確認 */ select s2.sale_id, s2.sale_date, s2.customer, s2.is_delivery, s2.note from sale2 as s2 join product as p on s2.product_code = p.product_code where p.product_price >= 1000 order by s2.sale_id;

６－８

商品毎の販売総数の上位を確認

販売総数が20個以上の商品を、上位から並べて表示する。

product_code product_name allcount
N02 いちじく 32
N05 マンゴー 28
N03 りんご 23

キーワード：join 〜 on、as、group by、集計関数、having、order by

【サンプルソースコード】

6-8.sql

/* 6-8．商品毎の販売総数の上位を確認 */ select p.product_code, p.product_name, sum(s2.product_count) as allcount from product as p left join sale2 as s2 on p.product_code = s2.product_code group by p.product_code, p.product_name having sum(s2.product_count) >= 20 order by sum(s2.product_count) desc;

６－９

売れない商品を確認

１個も売れたことがない商品を表示する。

product_code product_name
N06 バナナ
S03 秋の先取りセット

キーワード：left（またはright）join 〜 on、as、group by、集計関数、having

【サンプルソースコード】

6-9.sql

/* 6-9．売れない商品を確認 */ select p.product_code, p.product_name from product as p left join sale2 as s2 on p.product_code = s2.product_code group by p.product_code, p.product_name having sum(s2.product_count) is NULL order by p.product_code;

課題

課題5-1で正規化したテーブルを使って、仕様を満たすようなSQL文を作成する。
既存データはINSERTしておくこと。

課題6-1．男性で、何かレッスンを受けている人を表示する。

※member_id, member_name, member_gender, レッスン名 を表示すること
※カラム名は各自のテーブル構成に置き換えて考えてください

[結果表示]

member_id member_name member_gender lesson
H028 長崎らっこ 男性 スイミング
H335 宮崎まんぼう 男性 ホットヨガ
M101 秋田きりん 男性 スカッシュ
M101 秋田きりん 男性 ジャズダンス
M211 福岡かまきり 男性 ジャズダンス
N113 岡山ふくろう 男性 スカッシュ
N280 香川ひらめ 男性 ジャズダンス
N511 長野となかい 男性 スイミング

課題6-2．何かレッスンを受けているマスター会員を、年齢の若い順に表示する。

※複数のレッスンを受けている会員でも1行のみ表示されること
※member_id, member_name, class, birthday を表示すること
※カラム名は各自のテーブル構成に置き換えて考えてください

[結果表示]

member_id member_name class birthday
M211 福岡かまきり マスター 1980-10-08
M101 秋田きりん マスター 1978-05-04
M502 石川うさぎ マスター 1976-05-21
M340 山口ぱんだ マスター 1976-04-28

課題6-3．各店舗での、レッスン受講者数を表示する。

※店舗名と店舗毎のレッスン受講者数を表示すること
※カラム名は適切な名前に変えること

[結果表示（最低限）]
上野店舗所属の秋田きりんが2つのレッスンを受けているため、2名分にカウントされる。
従って、本来は4人であるはずの上野店舗が5人に表示される。

店舗 人数
上野 5
千葉 3
品川 3

[結果表示（できれば）]

店舗 人数
上野 4
千葉 3
品川 3

課題6-4．2つ以上のレッスンを受けている人を表示する。

2つ以上のレッスンを受講している会員を表示する。
※member_id, member_name を表示すること
※カラム名は各自のテーブル構成に置き換えて考えてください

[結果表示]

member_id member_name
M101 秋田きりん

課題6-5．レッスン宣伝のため、レッスンをひとつも受けていない人を表示する。

レッスンを受講していない新規会員を登録し、その会員のみ表示する。
新規会員データは以下のようにする。

member_id regist_date member_name birthday member_gender 所属クラス 所属エリア
M999 2025-05-30 進化太郎 1982-11-12 男性 マスター 品川

そのためのINSERT文も提出すること。
※member_id, member_name を表示すること
※カラム名は各自のテーブル構成に置き換えて考えてください

[結果表示]

member_id member_name
M999 進化太郎

Copyright © Xincor miXell Co., Ltd. All rights reserved

results matching ""

    No results matching ""