演習: サブクエリ
bookstore データベースで練習します。最初に USE bookstore; で切り替えてください。
6-a. 最高価格の書籍を調べる
Section titled “6-a. 最高価格の書籍を調べる”books テーブルから、最も高価な書籍のタイトルと価格を表示してください。同じ最高価格の書籍が複数ある場合はすべて表示します。
期待される結果:
| title | price |
|---|---|
| SRE サイトリライアビリティエンジニアリング | 5280 |
ヒント
MAX(price) は単一の値を返すスカラーサブクエリとして使えます。これを = の右辺に置けば「最高価格と等しい書籍」を抽出できます。ORDER BY price DESC LIMIT 1 では同価格の書籍を取りこぼす可能性があります。
6-b. 最も古く発売された書籍を調べる
Section titled “6-b. 最も古く発売された書籍を調べる”歴代で最も古く発売された書籍を確認します。books テーブルから、最も発売日が古い書籍のタイトルと発売日を表示してください。
期待される結果:
| title | published_at |
|---|---|
| こころ | 1952-03-04 |
ヒント
MIN(published_at) で最も古い日付を取得し、その値と一致する書籍を抽出します。
6-c. 特定著者の書籍を調べる
Section titled “6-c. 特定著者の書籍を調べる”「夏目漱石」の作品を調べます。books と authors を結合せずに、サブクエリを使って夏目漱石の書籍を、タイトルと価格で、価格の安い順に表示してください。
期待される結果:
| title | price |
|---|---|
| こころ | 473 |
| 吾輩は猫である | 693 |
ヒント
authors.name から著者 ID を求め、books.author_id と比較します。= で比較するときはサブクエリが 1 件を返すことが前提で、複数件返る可能性があるなら IN を使います。
6-d. 技術書カテゴリの平均価格より高い書籍を調べる
Section titled “6-d. 技術書カテゴリの平均価格より高い書籍を調べる”技術書の平均価格を基準に、それより高価な書籍を全カテゴリから探します。books テーブルから、技術書（category_id = 3）の平均価格より高い書籍を、タイトル、価格、カテゴリ ID で、価格の安い順、同じ価格の中では書籍 ID の昇順に表示してください。
期待される結果:
| title | price | category_id |
|---|---|---|
| Clean Code | 4104 | 3 |
| プログラミング言語Go | 4180 | 3 |
| リファクタリング(第2版) | 4840 | 3 |
| Googleのソフトウェアエンジニアリング | 4840 | 3 |
| データ指向アプリケーションデザイン | 5060 | 3 |
| SRE サイトリライアビリティエンジニアリング | 5280 | 3 |
ヒント
サブクエリの内側で技術書の平均価格を AVG で計算し、外側でその値より高い書籍を抽出します。今回は結果が技術書だけになりますが、他のカテゴリに技術書平均を超える書籍があれば含まれます。
6-e. 最高価格の書籍と同じカテゴリの書籍を調べる（CTE）
Section titled “6-e. 最高価格の書籍と同じカテゴリの書籍を調べる（CTE）”最高価格の書籍がどのカテゴリに属するかを調査し、同カテゴリの書籍を一覧化します。CTE（WITH 句）を使って、最高価格の書籍が属するカテゴリのすべての書籍を、タイトルと価格で、価格の高い順、同じ価格の中では書籍 ID の昇順に表示してください。
期待される結果:
| title | price |
|---|---|
| SRE サイトリライアビリティエンジニアリング | 5280 |
| データ指向アプリケーションデザイン | 5060 |
| リファクタリング(第2版) | 4840 |
| Googleのソフトウェアエンジニアリング | 4840 |
| プログラミング言語Go | 4180 |
| Clean Code | 4104 |
| ファインマン物理学 I 力学 | 3740 |
| 達人プログラマー(第2版) | 3520 |
| 人月の神話 新装版 | 3520 |
| リーダブルコード | 2640 |
| アルゴリズム図鑑 | 2618 |
| SQL 第2版 ゼロからはじめるデータベース操作 | 2068 |
ヒント
WITH 名前 AS (...) で中間結果に名前を付けられます。まず CTE で最高価格書籍のカテゴリ ID を定義し、本体クエリでそのカテゴリの書籍を抽出します。サブクエリをネストして書くより処理の流れが追いやすくなります。
6-f. 販売実績のある書籍を調べる
Section titled “6-f. 販売実績のある書籍を調べる”販売管理のため、一度でも売れた書籍を特定します。books テーブルから、sales テーブルに記録がある書籍のうち、タイトルの昇順に上位 5 件のタイトルと価格を表示してください。
期待される結果:
| title | price |
|---|---|
| 1Q84 BOOK1 前編 | 880 |
| Clean Code | 4104 |
| DRAGON BALL 1 | 420 |
| LIFE SHIFT | 1980 |
| ONE PIECE 1 | 440 |
ヒント
IN (サブクエリ) は、サブクエリが返す値のリストに含まれるかを判定します。sales から book_id を重複除去して取り出し、その集合に含まれる書籍を抽出します。
6-g. 販売実績のない高額書籍を調べる
Section titled “6-g. 販売実績のない高額書籍を調べる”在庫に残ったまま売れていない高額書籍を特定します。books テーブルから、sales テーブルに一度も登場せず、かつ価格が 3000 円以上の書籍を、タイトルと価格で、価格の高い順に表示してください。
期待される結果:
| title | price |
|---|---|
| SRE サイトリライアビリティエンジニアリング | 5280 |
| データ指向アプリケーションデザイン | 5060 |
| Googleのソフトウェアエンジニアリング | 4840 |
| ファインマン物理学 I 力学 | 3740 |
ヒント
NOT EXISTS (SELECT 1 FROM sales WHERE sales.book_id = books.id) で、該当する sales 行がない書籍を絞り込めます。NOT IN でも同じ結果になりますが、サブクエリ側に NULL がある場合の挙動や大規模データでの効率の観点から、NOT EXISTS のほうが実務では安全で推奨されることが多いです。
6-h. アメリカ出身の著者の書籍を調べる
Section titled “6-h. アメリカ出身の著者の書籍を調べる”海外展開を考えるため、アメリカ出身の著者の作品リストを作成します。books テーブルから、authors テーブルで country が「アメリカ」の著者が書いた書籍を、タイトル、author_id、価格で、author_id の昇順、同じ著者内では価格の高い順に表示してください。
期待される結果:
| title | author_id | price |
|---|---|---|
| 武器よさらば | 6 | 990 |
| 老人と海 | 6 | 572 |
| マネジメント[エッセンシャル版] | 9 | 2090 |
| プロフェッショナルの条件 | 9 | 1980 |
| ビジョナリー・カンパニー2 | 12 | 2420 |
| リーダブルコード | 15 | 2640 |
| 達人プログラマー(第2版) | 18 | 3520 |
| Clean Code | 19 | 4104 |
| 人月の神話 新装版 | 20 | 3520 |
| プログラミング言語Go | 21 | 4180 |
| 完訳 7つの習慣 | 25 | 2376 |
| 銃・病原菌・鉄 上 | 28 | 1210 |
| 銃・病原菌・鉄 下 | 28 | 1210 |
| 人を動かす 文庫版 | 33 | 715 |
| 思考は現実化する | 34 | 2420 |
| Googleのソフトウェアエンジニアリング | 36 | 4840 |
| ファインマン物理学 I 力学 | 37 | 3740 |
| SRE サイトリライアビリティエンジニアリング | 38 | 5280 |
ヒント
内側のサブクエリで「アメリカ出身の著者の id 一覧」を取得し、外側の WHERE ... IN でそれらに一致する書籍を抽出します。
6-i. 書籍ごとにカテゴリ名を表示する
Section titled “6-i. 書籍ごとにカテゴリ名を表示する”書籍リストにカテゴリ名を併記して一覧性を高めます。books テーブルから、価格が 3500 円以上の書籍について、タイトル、価格、カテゴリ名を、価格の高い順、同じ価格の中では書籍 ID の昇順に表示してください。カテゴリ名は SELECT 句のスカラーサブクエリで取得してください。カテゴリ名のカラム名は category_name とします。
期待される結果:
| title | price | category_name |
|---|---|---|
| SRE サイトリライアビリティエンジニアリング | 5280 | 技術書 |
| データ指向アプリケーションデザイン | 5060 | 技術書 |
| リファクタリング(第2版) | 4840 | 技術書 |
| Googleのソフトウェアエンジニアリング | 4840 | 技術書 |
| プログラミング言語Go | 4180 | 技術書 |
| Clean Code | 4104 | 技術書 |
| ファインマン物理学 I 力学 | 3740 | 技術書 |
| 達人プログラマー(第2版) | 3520 | 技術書 |
| 人月の神話 新装版 | 3520 | 技術書 |
ヒント
SELECT 句の中にサブクエリを書くと、各行について 1 つの値を返すスカラーサブクエリになります。外側の行（books.category_id）をサブクエリ内で参照することで、各書籍に対応するカテゴリ名を取り出せます。これは相関サブクエリと呼ばれます。
6-j. 複数冊を持つ著者で、平均価格が高い著者の書籍を調べる（CTE）
Section titled “6-j. 複数冊を持つ著者で、平均価格が高い著者の書籍を調べる（CTE）”レコメンド戦略の検討のため、複数作品を持ちかつ価格帯が高い著者を特定します。CTE を使って、書籍を 2 冊以上登録している著者のうち、平均価格が 2000 円を超える著者について、著者名、タイトル、価格を、著者名の昇順、同じ著者内では価格の高い順に表示してください。
期待される結果:
| name | title | price |
|---|---|---|
| J.K.ローリング | ハリー・ポッターと賢者の石 | 2420 |
| J.K.ローリング | ハリー・ポッターとアズカバンの囚人 | 2310 |
| J.K.ローリング | ハリー・ポッターと秘密の部屋 | 2090 |
| ピーター・ドラッカー | マネジメント[エッセンシャル版] | 2090 |
| ピーター・ドラッカー | プロフェッショナルの条件 | 1980 |
| ユヴァル・ノア・ハラリ | 21 Lessons | 2640 |
| ユヴァル・ノア・ハラリ | サピエンス全史 上 | 2090 |
| ユヴァル・ノア・ハラリ | サピエンス全史 下 | 2090 |
ヒント
CTE で「2 冊以上の著者 × 平均価格」の集計結果に名前を付けます。その CTE を本体クエリから JOIN して、条件に合う著者の書籍を books から引き出します。集計した中間結果に名前が付くことで、本体クエリの意図が読みやすくなります。