演習: 結合
bookstore データベースで練習します。最初に USE bookstore; で切り替えてください。
5-a. 書籍とカテゴリ名を結合する
Section titled “5-a. 書籍とカテゴリ名を結合する”漫画コーナーの棚卸しのため、漫画カテゴリの書籍一覧をカテゴリ名付きで出力します。books と categories を結合し、categories.name が「漫画」の書籍について、タイトル、価格、カテゴリ名を、価格の安い順、同じ価格の中では書籍 ID の昇順に表示してください。カテゴリ名のカラム名は category_name とします。
期待される結果:
| title | price | category_name |
|---|---|---|
| DRAGON BALL 1 | 420 | 漫画 |
| ONE PIECE 1 | 440 | 漫画 |
| ONE PIECE 2 | 440 | 漫画 |
| 進撃の巨人 1 | 550 | 漫画 |
ヒント
JOIN ... ON 結合条件 で 2 つのテーブルを結合します。JOIN は INNER JOIN の省略形で、両方に一致する行だけを返します。AS でテーブルに別名を付けると SQL が読みやすくなります。
5-b. 書籍と著者名を結合する
Section titled “5-b. 書籍と著者名を結合する”高額書籍の著者を確認します。books と authors を結合し、価格が 3000 円以上の書籍について、タイトル、著者名、価格を、価格の高い順、同じ価格の中では書籍 ID の昇順に表示してください。著者名のカラム名は author_name とします。
期待される結果:
| title | author_name | price |
|---|---|---|
| SRE サイトリライアビリティエンジニアリング | ベッツィ・ベイヤー | 5280 |
| データ指向アプリケーションデザイン | マーティン・クレップマン | 5060 |
| リファクタリング(第2版) | マーチン・ファウラー | 4840 |
| Googleのソフトウェアエンジニアリング | タイタス・ウィンタース | 4840 |
| プログラミング言語Go | アラン・ドノバン | 4180 |
| Clean Code | ロバート・C・マーティン | 4104 |
| ファインマン物理学 I 力学 | リチャード・P・ファインマン | 3740 |
| 達人プログラマー(第2版) | アンドリュー・ハント | 3520 |
| 人月の神話 新装版 | フレデリック・P・ブルックス Jr. | 3520 |
ヒント
書籍の author_id と著者の id を ON で結びます。外部キーと主キーの結合の典型例です。
5-c. 3 つのテーブルを結合する
Section titled “5-c. 3 つのテーブルを結合する”技術書カテゴリの高額書籍カタログを作成します。books と authors と categories の 3 テーブルを結合し、カテゴリが「技術書」で価格が 4000 円以上の書籍について、タイトル、著者名、カテゴリ名、価格を、価格の高い順、同じ価格の中では書籍 ID の昇順に表示してください。著者名のカラム名は author_name、カテゴリ名のカラム名は category_name とします。
期待される結果:
| title | author_name | category_name | price |
|---|---|---|---|
| SRE サイトリライアビリティエンジニアリング | ベッツィ・ベイヤー | 技術書 | 5280 |
| データ指向アプリケーションデザイン | マーティン・クレップマン | 技術書 | 5060 |
| リファクタリング(第2版) | マーチン・ファウラー | 技術書 | 4840 |
| Googleのソフトウェアエンジニアリング | タイタス・ウィンタース | 技術書 | 4840 |
| プログラミング言語Go | アラン・ドノバン | 技術書 | 4180 |
| Clean Code | ロバート・C・マーティン | 技術書 | 4104 |
ヒント
JOIN は 3 つ以上のテーブルに対しても連鎖できます。FROM 表1 JOIN 表2 ON ... JOIN 表3 ON ... の形で左から結合を積み重ねます。
5-d. 販売実績のない書籍を調べる
Section titled “5-d. 販売実績のない書籍を調べる”長期在庫の洗い出しのため、販売実績のない書籍を特定します。books と sales を LEFT JOIN で結合し、sales 側にレコードが存在しない書籍のタイトルと価格を、価格の高い順に表示してください。
期待される結果:
| title | price |
|---|---|
| SRE サイトリライアビリティエンジニアリング | 5280 |
| データ指向アプリケーションデザイン | 5060 |
| Googleのソフトウェアエンジニアリング | 4840 |
| ファインマン物理学 I 力学 | 3740 |
ヒント
LEFT JOIN は左側のテーブルの全行を残し、右側に一致がなければ右側の列を NULL で埋めます。WHERE で右側列が IS NULL の行を選ぶと、右側に一致しなかった行（販売記録のない書籍）が得られます。
5-e. 書籍ごとの販売数を集計する
Section titled “5-e. 書籍ごとの販売数を集計する”売れ筋ランキングを作成します。books と sales を結合し、書籍ごとの販売数量の合計を、合計数量の多い順、同数の場合は書籍 ID の昇順に上位 10 件表示してください。タイトルと合計数量のカラム名を total_qty とします。
期待される結果:
| title | total_qty |
|---|---|
| サピエンス全史 下 | 16 |
| ハリー・ポッターとアズカバンの囚人 | 15 |
| ノルウェイの森 上 | 14 |
| ハリー・ポッターと賢者の石 | 14 |
| 幸せになる勇気 | 14 |
| 海辺のカフカ 上 | 12 |
| LIFE SHIFT | 12 |
| 嫌われる勇気 | 12 |
| 容疑者Xの献身 | 11 |
| 白夜行 | 11 |
ヒント
JOIN してから GROUP BY で書籍単位にまとめ、SUM(s.quantity) で合計数量を算出します。GROUP BY には SELECT 句で使う非集計列を書きます。上位 N 件の取り出しは ORDER BY ... LIMIT N です。
5-f. 特定顧客の購入履歴を調べる
Section titled “5-f. 特定顧客の購入履歴を調べる”顧客からの問い合わせに対応するため、customers、sales、books の 3 テーブルを結合し、顧客「田中 健」の購入履歴を、購入日時の古い順に表示してください。書籍タイトル、数量、合計金額、購入日時を出力します。
期待される結果:
| title | quantity | total_price | sold_at |
|---|---|---|---|
| ONE PIECE 2 | 1 | 440 | 2024-04-25 11:20:00 |
| 容疑者Xの献身 | 1 | 858 | 2024-08-10 15:50:00 |
| リファクタリング(第2版) | 2 | 9680 | 2024-08-16 16:30:00 |
| 銃・病原菌・鉄 上 | 1 | 1210 | 2024-10-11 09:10:00 |
| ガリレオの苦悩 | 1 | 825 | 2024-11-09 11:45:00 |
| 海辺のカフカ 下 | 1 | 1100 | 2025-01-08 10:00:00 |
ヒント
customers → sales → books と中間テーブル（sales）を介して 3 テーブルを連鎖結合します。WHERE で顧客を絞り込みます。
5-g. カテゴリ別の売上金額を集計する
Section titled “5-g. カテゴリ別の売上金額を集計する”カテゴリごとの売上構成を分析します。sales、books、categories の 3 テーブルを結合し、カテゴリごとの売上金額の合計を、売上金額の多い順に表示してください。カテゴリ名と売上金額合計のカラム名を category_name, total_revenue とします。
期待される結果:
| category_name | total_revenue |
|---|---|
| 文学 | 118096 |
| ビジネス | 115990 |
| 技術書 | 114756 |
| 児童書 | 97680 |
| 自己啓発 | 81114 |
| 歴史 | 75526 |
| 漫画 | 18500 |
ヒント
販売記録 sales に書籍情報 (books) を介してカテゴリ (categories) を対応付け、カテゴリごとに SUM(s.total_price) を求めます。
5-h. 都道府県別の売上件数を集計する
Section titled “5-h. 都道府県別の売上件数を集計する”都道府県別の販売活動の規模を把握します。sales と customers を結合し、都道府県ごとの売上件数を、件数の多い順、同数の場合は都道府県名の昇順に表示してください。件数のカラム名は sales_count とします。
期待される結果:
| prefecture | sales_count |
|---|---|
| 東京都 | 93 |
| 神奈川県 | 38 |
| 大阪府 | 37 |
| 兵庫県 | 16 |
| 愛知県 | 15 |
| 京都府 | 13 |
| 福岡県 | 11 |
| 広島県 | 9 |
| 千葉県 | 8 |
| 宮城県 | 7 |
| 北海道 | 3 |
| 埼玉県 | 3 |
ヒント
sales × customers で販売記録と顧客の居住地を紐付け、GROUP BY c.prefecture でまとめて COUNT(*) を求めます。
5-i. 書籍を多く持つ著者を調べる
Section titled “5-i. 書籍を多く持つ著者を調べる”複数作品を持つ著者を特定します。books と authors を結合し、書籍を 2 冊以上登録している著者について、著者名と書籍数を、書籍数の多い順、同数の場合は著者 ID の昇順に表示してください。著者名のカラム名は author_name、書籍数のカラム名は book_count とします。
期待される結果:
| author_name | book_count |
|---|---|
| 村上春樹 | 5 |
| 東野圭吾 | 3 |
| J.K.ローリング | 3 |
| ユヴァル・ノア・ハラリ | 3 |
| 夏目漱石 | 2 |
| 伊坂幸太郎 | 2 |
| ヘミングウェイ | 2 |
| アガサ・クリスティー | 2 |
| ピーター・ドラッカー | 2 |
| 稲盛和夫 | 2 |
| 岸見一郎 | 2 |
| ジャレド・ダイアモンド | 2 |
| 塩野七生 | 2 |
| 尾田栄一郎 | 2 |
ヒント
著者ごとに書籍をグループ化し、HAVING でグループの件数を絞り込みます。WHERE は結合前の行単位の絞り込み、HAVING は集計後のグループ単位の絞り込み。
5-j. 優良顧客を特定する
Section titled “5-j. 優良顧客を特定する”ロイヤルティプログラムの対象として、購入金額の多い顧客を特定します。sales と customers を結合し、合計購入金額の多い上位 10 名について、顧客 ID、名前、購入回数、合計金額を、合計金額の多い順、同額の場合は顧客 ID の昇順に表示してください。購入回数のカラム名は purchase_count、合計金額のカラム名は total_amount とします。
期待される結果:
| id | name | purchase_count | total_amount |
|---|---|---|---|
| 5 | 伊藤 優奈 | 13 | 39336 |
| 15 | 井上 千尋 | 9 | 32204 |
| 22 | 橋本 涼 | 7 | 26268 |
| 8 | 中村 拓海 | 6 | 24794 |
| 29 | 石井 さくら | 6 | 23364 |
| 20 | 森 健太郎 | 7 | 22990 |
| 30 | 坂本 健司 | 10 | 22926 |
| 31 | 遠藤 美香 | 7 | 20042 |
| 40 | 野村 雄介 | 7 | 18898 |
| 33 | 藤井 智子 | 9 | 18359 |
ヒント
JOIN + GROUP BY + 複数集計 + ORDER BY + LIMIT の総合問題です。COUNT(*) で購入回数、SUM(s.total_price) で合計金額を同時に算出します。