🎓 Step 15: 総合演習プロジェクト

実践的なECサイトのデータベースを操作しよう！

📋 このステップで学ぶこと

仮想ECサイトのデータベース設計を理解する
商品検索クエリを作成する
売上集計レポートを作成する
顧客別の購入履歴を分析する
在庫管理のためのクエリを作成する
これまでの知識を総動員！

🎯 1. プロジェクト概要

1-1. 仮想ECサイト「フルーツマート」

これまで学んだSQLの知識を使って、フルーツマートという仮想のECサイトのデータベースを操作します。

🏪 フルーツマートとは？

果物や野菜を販売するオンラインショップ
全国に50人以上の顧客がいる
商品は80種類以上
毎月数十件の注文がある

1-2. あなたの役割

あなたはデータアナリストとして、以下の業務を担当します。

💼 担当業務

商品検索：お客様が欲しい商品を見つける
売上分析：どの商品が売れているか調べる
顧客分析：どんなお客様がいるか把握する
在庫管理：在庫状況を確認する
レポート作成：経営陣への報告資料を作る

実際の仕事で使うような課題に挑戦しましょう！

📊 2. データベース構造

2-1. 4つのテーブル

フルーツマートのデータベースは、以下の4つのテーブルで構成されています。

📋 1. 顧客テーブル（customers）

列名	データ型	説明
customer_id	INTEGER	主キー、顧客ID
customer_name	TEXT	顧客名
email	TEXT	メールアドレス
prefecture	TEXT	都道府県
registration_date	TEXT	登録日
is_vip	INTEGER	VIPフラグ（1=VIP, 0=一般）

📋 2. 商品テーブル（products）

列名	データ型	説明
product_id	INTEGER	主キー、商品ID
product_name	TEXT	商品名
category	TEXT	カテゴリ（果物/野菜/飲料など）
price	INTEGER	価格
stock	INTEGER	在庫数
supplier	TEXT	仕入先

📋 3. 注文テーブル（orders）

列名	データ型	説明
order_id	INTEGER	主キー、注文ID
customer_id	INTEGER	外部キー、顧客ID
order_date	TEXT	注文日
total_amount	INTEGER	合計金額
status	TEXT	ステータス（処理中/発送済み/完了）

📋 4. 注文明細テーブル（order_details）

列名	データ型	説明
detail_id	INTEGER	主キー、明細ID
order_id	INTEGER	外部キー、注文ID
product_id	INTEGER	外部キー、商品ID
quantity	INTEGER	数量
unit_price	INTEGER	単価

2-2. テーブル間の関係

📌 テーブルの関係（リレーション）

customers → orders：1人の顧客が複数の注文をする
orders → order_details：1つの注文に複数の商品が含まれる
products → order_details：1つの商品が複数の注文で購入される

例：田中さん（顧客）が、りんご3個とバナナ5本（注文明細）を1回の注文で購入

2-3. サンプルデータの作成

以下のSQLを実行して、練習用のサンプルデータを作成してください。

ステップ1：テーブルを作成

※横にスクロールできます

— 顧客テーブル
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT NOT NULL,
    email TEXT,
    prefecture TEXT,
    registration_date TEXT,
    is_vip INTEGER DEFAULT 0
);

— 商品テーブル
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT,
    price INTEGER NOT NULL,
    stock INTEGER DEFAULT 0,
    supplier TEXT
);

— 注文テーブル
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    total_amount INTEGER,
    status TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

— 注文明細テーブル
CREATE TABLE order_details (
    detail_id INTEGER PRIMARY KEY,
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    unit_price INTEGER,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

ステップ2：サンプルデータを挿入

※横にスクロールできます

— 顧客データ
INSERT INTO customers (customer_id, customer_name, email, prefecture, registration_date, is_vip) VALUES
(1, ‘田中太郎’, ‘tanaka@example.com’, ‘東京都’, ‘2023-01-15’, 1),
(2, ‘佐藤花子’, ‘sato@example.com’, ‘大阪府’, ‘2023-02-20’, 1),
(3, ‘鈴木次郎’, ‘suzuki@example.com’, ‘神奈川県’, ‘2023-03-10’, 0),
(4, ‘高橋美咲’, ‘takahashi@example.com’, ‘東京都’, ‘2023-04-05’, 0),
(5, ‘山田三郎’, ‘yamada@example.com’, ‘福岡県’, ‘2023-05-12’, 0),
(6, ‘伊藤恵’, ‘ito@example.com’, ‘北海道’, ‘2023-06-18’, 1),
(7, ‘渡辺健’, ‘watanabe@example.com’, ‘愛知県’, ‘2023-07-22’, 0),
(8, ‘中村さくら’, ‘nakamura@example.com’, ‘京都府’, ‘2024-01-08’, 0),
(9, ‘小林翔太’, ‘kobayashi@example.com’, ‘東京都’, ‘2024-02-14’, 0),
(10, ‘加藤由美’, NULL, ‘大阪府’, ‘2024-03-20’, 0);

— 商品データ
INSERT INTO products (product_id, product_name, category, price, stock, supplier) VALUES
(101, ‘りんご’, ‘果物’, 150, 50, ‘農協A’),
(102, ‘みかん’, ‘果物’, 100, 80, ‘農協A’),
(103, ‘バナナ’, ‘果物’, 120, 45, ‘果物商B’),
(104, ‘ぶどう’, ‘果物’, 300, 20, ‘果物商B’),
(105, ‘メロン’, ‘果物’, 800, 5, ‘農協A’),
(106, ‘トマト’, ‘野菜’, 200, 60, ‘野菜卸C’),
(107, ‘きゅうり’, ‘野菜’, 80, 70, ‘野菜卸C’),
(108, ‘レタス’, ‘野菜’, 150, 40, ‘野菜卸C’),
(109, ‘にんじん’, ‘野菜’, 100, 55, ‘農協A’),
(110, ‘オレンジジュース’, ‘飲料’, 180, 30, ‘飲料メーカーD’),
(111, ‘りんごジュース’, ‘飲料’, 200, 25, ‘飲料メーカーD’),
(112, ‘お茶’, ‘飲料’, 150, 100, ‘飲料メーカーD’);

— 注文データ
INSERT INTO orders (order_id, customer_id, order_date, total_amount, status) VALUES
(1001, 1, ‘2024-10-01’, 1500, ‘完了’),
(1002, 2, ‘2024-10-03’, 2400, ‘完了’),
(1003, 1, ‘2024-10-05’, 800, ‘完了’),
(1004, 3, ‘2024-10-08’, 1200, ‘完了’),
(1005, 4, ‘2024-10-10’, 3000, ‘完了’),
(1006, 1, ‘2024-10-15’, 2100, ‘完了’),
(1007, 5, ‘2024-10-18’, 1800, ‘完了’),
(1008, 2, ‘2024-10-20’, 900, ‘完了’),
(1009, 6, ‘2024-10-22’, 4500, ‘完了’),
(1010, 7, ‘2024-10-25’, 600, ‘完了’),
(1011, 1, ‘2024-11-01’, 1350, ‘発送済み’),
(1012, 8, ‘2024-11-03’, 2000, ‘発送済み’),
(1013, 2, ‘2024-11-05’, 1600, ‘処理中’),
(1014, 9, ‘2024-11-07’, 750, ‘処理中’),
(1015, 4, ‘2024-11-09’, 2200, ‘処理中’);

— 注文明細データ
INSERT INTO order_details (detail_id, order_id, product_id, quantity, unit_price) VALUES
(1, 1001, 101, 5, 150),
(2, 1001, 102, 5, 100),
(3, 1001, 103, 2, 120),
(4, 1002, 104, 3, 300),
(5, 1002, 101, 10, 150),
(6, 1003, 105, 1, 800),
(7, 1004, 106, 4, 200),
(8, 1004, 107, 5, 80),
(9, 1005, 104, 5, 300),
(10, 1005, 101, 10, 150),
(11, 1006, 106, 5, 200),
(12, 1006, 108, 4, 150),
(13, 1006, 109, 5, 100),
(14, 1007, 110, 5, 180),
(15, 1007, 111, 4, 200),
(16, 1008, 102, 6, 100),
(17, 1008, 112, 2, 150),
(18, 1009, 105, 3, 800),
(19, 1009, 104, 5, 300),
(20, 1009, 101, 6, 150),
(21, 1010, 107, 5, 80),
(22, 1010, 109, 2, 100),
(23, 1011, 101, 5, 150),
(24, 1011, 103, 5, 120),
(25, 1012, 106, 6, 200),
(26, 1012, 108, 4, 150),
(27, 1012, 112, 2, 150),
(28, 1013, 110, 4, 180),
(29, 1013, 111, 4, 200),
(30, 1014, 102, 5, 100),
(31, 1014, 107, 3, 80),
(32, 1015, 104, 4, 300),
(33, 1015, 106, 5, 200);

2-4. データ件数を確認

サンプルデータが正しく作成されたか確認しましょう。

※横にスクロールできます

SELECT ‘customers’ AS テーブル名, COUNT(*) AS 件数 FROM customers
UNION ALL
SELECT ‘products’, COUNT(*) FROM products
UNION ALL
SELECT ‘orders’, COUNT(*) FROM orders
UNION ALL
SELECT ‘order_details’, COUNT(*) FROM order_details;

📋 実行結果

テーブル名	件数
customers	10
products	12
orders	15
order_details	33

✅ 全てのテーブルにデータが入っていれば準備完了！

🔍 3. 課題1：商品検索システム

🎯 ミッション

お客様が欲しい商品を見つけられるようにしましょう！

課題 1-1：カテゴリ別商品一覧（基本）

課題 1-1 基本

「果物」カテゴリの商品を価格順に表示

やること：「果物」カテゴリの全商品を、価格の安い順に表示してください。
表示する列：商品名、価格、在庫数

考え方：

productsテーブルからデータを取得
categoryが「果物」のものだけに絞り込む
priceで昇順（安い順）に並べ替え

※横にスクロールできます

SELECT 
    product_name AS 商品名,
    price AS 価格,
    stock AS 在庫数
FROM products
WHERE category = ‘果物’
ORDER BY price ASC;

📌 このSQLの意味

部分	意味
`SELECT product_name AS 商品名, ...`	表示する列を指定、ASで別名をつける
`FROM products`	商品テーブルから
`WHERE category = '果物'`	カテゴリが「果物」のものだけ
`ORDER BY price ASC`	価格の昇順（安い順）で並べ替え

📋 実行結果

商品名	価格	在庫数
みかん	100	80
バナナ	120	45
りんご	150	50
ぶどう	300	20
メロン	800	5

課題 1-2：価格帯で絞り込み（応用）

課題 1-2 応用

価格帯別のカテゴリ分析

やること：100円以上300円以下の商品を、カテゴリごとにグループ化して、カテゴリ別の商品数と平均価格を表示してください。
表示する列：カテゴリ、商品数、平均価格
並び順：商品数が多い順

考え方：

価格が100円以上300円以下の商品に絞り込む
カテゴリでグループ化する
各グループの件数（COUNT）と平均（AVG）を計算

※横にスクロールできます

SELECT 
    category AS カテゴリ,
    COUNT(*) AS 商品数,
    ROUND(AVG(price)) AS 平均価格
FROM products
WHERE price BETWEEN 100 AND 300
GROUP BY category
ORDER BY 商品数 DESC;

📌 このSQLの意味

部分	意味
`WHERE price BETWEEN 100 AND 300`	価格が100〜300円の範囲
`GROUP BY category`	カテゴリごとにまとめる
`COUNT(*)`	各グループの件数を数える
`ROUND(AVG(price))`	平均価格を四捨五入

📋 実行結果

カテゴリ	商品数	平均価格
野菜	4	133
果物	3	190
飲料	3	177

課題 1-3：在庫切れ間近の商品（応用）

課題 1-3 応用

在庫が少ない商品をリストアップ

やること：在庫が20個以下の商品を、カテゴリとともに表示してください。
表示する列：商品名、カテゴリ、在庫数、価格
並び順：在庫の少ない順

※横にスクロールできます

SELECT 
    product_name AS 商品名,
    category AS カテゴリ,
    stock AS 在庫数,
    price AS 価格
FROM products
WHERE stock <= 20
ORDER BY stock ASC;

📋 実行結果

商品名	カテゴリ	在庫数	価格
メロン	果物	5	800
ぶどう	果物	20	300

メロンは在庫5個で要注意！

課題 1-4：人気商品ランキング（チャレンジ）

課題 1-4 チャレンジ

最も売れている商品TOP5

やること：order_detailsテーブルを使って、最も多く注文された商品トップ5を表示してください。
表示する列：商品名、カテゴリ、合計注文数、売上合計
ポイント：productsテーブルとorder_detailsテーブルを結合する

考え方：

productsとorder_detailsをINNER JOINで結合
商品ごとにグループ化
数量の合計（SUM）と売上の合計を計算
合計注文数の多い順に並べ、上位5件だけ取得

※横にスクロールできます

SELECT 
    p.product_name AS 商品名,
    p.category AS カテゴリ,
    SUM(od.quantity) AS 合計注文数,
    SUM(od.quantity * od.unit_price) AS 売上合計
FROM products p
INNER JOIN order_details od
    ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name, p.category
ORDER BY 合計注文数 DESC
LIMIT 5;

📌 このSQLの意味

部分	意味
`INNER JOIN order_details od ON ...`	商品テーブルと注文明細を結合
`SUM(od.quantity)`	注文数量の合計
`SUM(od.quantity * od.unit_price)`	売上金額の合計（数量×単価）
`LIMIT 5`	上位5件だけ取得

📋 実行結果

商品名	カテゴリ	合計注文数	売上合計
りんご	果物	36	5400
トマト	野菜	20	4000
ぶどう	果物	17	5100
みかん	果物	16	1600
きゅうり	野菜	13	1040

りんごが最も人気！果物が上位を占めています。

📈 4. 課題2：売上分析レポート

🎯 ミッション

経営陣への報告資料を作成しましょう！

課題 2-1：月別売上集計（応用）

課題 2-1 応用

月別の売上を集計

やること：2024年の月別売上を集計してください。
表示する列：年月、注文件数、売上合計、平均注文金額

考え方：

STRFTIME関数で注文日から「年-月」を抽出
年月でグループ化
各グループの件数、合計、平均を計算

※横にスクロールできます

SELECT 
    STRFTIME(‘%Y-%m’, order_date) AS 年月,
    COUNT(*) AS 注文件数,
    SUM(total_amount) AS 売上合計,
    ROUND(AVG(total_amount)) AS 平均注文金額
FROM orders
WHERE order_date >= ‘2024-01-01’
GROUP BY STRFTIME(‘%Y-%m’, order_date)
ORDER BY 年月;

📌 STRFTIME関数とは？

日付から特定の部分を取り出す関数です。

%Y：4桁の年（2024）
%m：2桁の月（01〜12）
%d：2桁の日（01〜31）

STRFTIME('%Y-%m', '2024-10-15') → '2024-10'

📋 実行結果

年月	注文件数	売上合計	平均注文金額
2024-10	10	18800	1880
2024-11	5	7900	1580

10月は10件、11月は5件の注文がありました。

課題 2-2：カテゴリ別売上シェア（チャレンジ）

課題 2-2 チャレンジ

各カテゴリの売上シェアを計算

やること：各カテゴリの売上金額と、全体に対する割合（シェア）を計算してください。
表示する列：カテゴリ、売上金額、シェア（%）
ポイント：サブクエリで全体の売上を計算

考え方：

productsとorder_detailsを結合
カテゴリごとに売上を集計
サブクエリで全体の売上を取得
各カテゴリの売上 ÷ 全体の売上 × 100 でシェアを計算

※横にスクロールできます

SELECT 
    p.category AS カテゴリ,
    SUM(od.quantity * od.unit_price) AS 売上金額,
    ROUND(
        SUM(od.quantity * od.unit_price) * 100.0 / 
        (SELECT SUM(quantity * unit_price) FROM order_details),
        1
    ) AS シェア
FROM products p
INNER JOIN order_details od
    ON p.product_id = od.product_id
GROUP BY p.category
ORDER BY 売上金額 DESC;

📌 このSQLの意味

部分	意味
`SUM(od.quantity * od.unit_price)`	カテゴリ別の売上金額
`(SELECT SUM(...) FROM order_details)`	サブクエリで全体の売上を取得
`* 100.0 / ...`	パーセントに変換（100.0で小数計算）
`ROUND(..., 1)`	小数点第1位で四捨五入

📋 実行結果

カテゴリ	売上金額	シェア
果物	16540	61.9%
野菜	6340	23.7%
飲料	3840	14.4%

果物が売上の約62%を占めています！

課題 2-3：都道府県別売上（チャレンジ）

課題 2-3 チャレンジ

都道府県別の売上トップ5

やること：顧客の都道府県別に売上を集計し、売上が多い上位5都道府県を表示してください。
表示する列：都道府県、注文件数、顧客数、売上合計
ポイント：customersテーブルとordersテーブルを結合

※横にスクロールできます

SELECT 
    c.prefecture AS 都道府県,
    COUNT(o.order_id) AS 注文件数,
    COUNT(DISTINCT c.customer_id) AS 顧客数,
    SUM(o.total_amount) AS 売上合計
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY c.prefecture
ORDER BY 売上合計 DESC
LIMIT 5;

📌 COUNT(DISTINCT …)とは？

重複を除いてカウントします。

COUNT(o.order_id)：注文の件数（同じ顧客でも別々にカウント）
COUNT(DISTINCT c.customer_id)：ユニークな顧客数

📋 実行結果

都道府県	注文件数	顧客数	売上合計
東京都	6	2	9500
大阪府	3	1	4900
北海道	1	1	4500
神奈川県	1	1	1200
京都府	1	1	2000

東京都が売上トップ！顧客2人で6回注文しています。

課題 2-4：仕入先別の販売状況（チャレンジ）

課題 2-4 チャレンジ

仕入先ごとの販売実績

やること：仕入先ごとに、取扱商品数、販売数量、売上金額を集計してください。
表示する列：仕入先、取扱商品数、販売数量、売上金額
ポイント：まだ売れていない商品も含めるためLEFT JOINを使用

※横にスクロールできます

SELECT 
    p.supplier AS 仕入先,
    COUNT(DISTINCT p.product_id) AS 取扱商品数,
    COALESCE(SUM(od.quantity), 0) AS 販売数量,
    COALESCE(SUM(od.quantity * od.unit_price), 0) AS 売上金額
FROM products p
LEFT JOIN order_details od
    ON p.product_id = od.product_id
GROUP BY p.supplier
ORDER BY 売上金額 DESC;

📌 LEFT JOINを使う理由

INNER JOINだと、一度も売れていない商品が結果に含まれません。

LEFT JOINを使うことで、売れていない商品も含めて仕入先ごとの集計ができます。

COALESCEでNULLを0に変換しています。

📋 実行結果

仕入先	取扱商品数	販売数量	売上金額
農協A	4	63	9700
果物商B	2	24	7980
野菜卸C	3	41	6340
飲料メーカーD	3	21	3700

👥 5. 課題3：顧客分析

🎯 ミッション

お客様を理解してサービス向上につなげましょう！

課題 3-1：顧客の購入履歴（応用）

課題 3-1 応用

顧客ごとの購入実績を表示

やること：各顧客の購入回数、合計購入金額、平均購入金額、最終購入日を表示してください。
表示する列：顧客名、都道府県、購入回数、合計購入金額、平均購入金額、最終購入日
並び順：合計購入金額が多い順
ポイント：まだ購入していない顧客も表示するためLEFT JOINを使用

※横にスクロールできます

SELECT 
    c.customer_name AS 顧客名,
    c.prefecture AS 都道府県,
    COUNT(o.order_id) AS 購入回数,
    COALESCE(SUM(o.total_amount), 0) AS 合計購入金額,
    COALESCE(ROUND(AVG(o.total_amount)), 0) AS 平均購入金額,
    MAX(o.order_date) AS 最終購入日
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.prefecture
ORDER BY 合計購入金額 DESC;

📌 このSQLの意味

部分	意味
`LEFT JOIN orders o`	注文がない顧客も含める
`COUNT(o.order_id)`	注文回数（NULLは数えない）
`MAX(o.order_date)`	最も新しい注文日
`COALESCE(..., 0)`	NULLを0に変換

📋 実行結果（一部）

顧客名	都道府県	購入回数	合計購入金額	平均購入金額	最終購入日
田中太郎	東京都	4	5750	1438	2024-11-01
佐藤花子	大阪府	3	4900	1633	2024-11-05
伊藤恵	北海道	1	4500	4500	2024-10-22
加藤由美	大阪府	0	0	0	NULL

田中太郎さんが最優良顧客！加藤由美さんはまだ購入なし。

課題 3-2：顧客ランク分け（チャレンジ）

課題 3-2 チャレンジ

購入金額で顧客をランク分け

やること：顧客を購入金額によってランク分けし、各ランクの顧客数を集計してください。
ランク基準：

5,000円以上：Sランク（優良顧客）
3,000円以上：Aランク
1,000円以上：Bランク
1円以上：Cランク
0円：未購入

考え方：

まず顧客ごとの購入金額を計算
CASE式でランクを判定
ランクごとに顧客数を集計

ステップ1：顧客ごとの購入金額とランクを表示

※横にスクロールできます

SELECT 
    c.customer_name AS 顧客名,
    COALESCE(SUM(o.total_amount), 0) AS 購入金額,
    CASE 
        WHEN COALESCE(SUM(o.total_amount), 0) >= 5000 THEN ‘Sランク’
        WHEN COALESCE(SUM(o.total_amount), 0) >= 3000 THEN ‘Aランク’
        WHEN COALESCE(SUM(o.total_amount), 0) >= 1000 THEN ‘Bランク’
        WHEN COALESCE(SUM(o.total_amount), 0) > 0 THEN ‘Cランク’
        ELSE ‘未購入’
    END AS ランク
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY 購入金額 DESC;

📋 ステップ1の実行結果

顧客名	購入金額	ランク
田中太郎	5750	Sランク
佐藤花子	4900	Aランク
伊藤恵	4500	Aランク
高橋美咲	5200	Sランク
加藤由美	0	未購入

ステップ2：ランクごとの顧客数を集計

※横にスクロールできます

SELECT 
    CASE 
        WHEN COALESCE(SUM(o.total_amount), 0) >= 5000 THEN ‘Sランク’
        WHEN COALESCE(SUM(o.total_amount), 0) >= 3000 THEN ‘Aランク’
        WHEN COALESCE(SUM(o.total_amount), 0) >= 1000 THEN ‘Bランク’
        WHEN COALESCE(SUM(o.total_amount), 0) > 0 THEN ‘Cランク’
        ELSE ‘未購入’
    END AS ランク,
    COUNT(*) AS 顧客数
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id
ORDER BY 
    CASE 
        WHEN COALESCE(SUM(o.total_amount), 0) >= 5000 THEN 1
        WHEN COALESCE(SUM(o.total_amount), 0) >= 3000 THEN 2
        WHEN COALESCE(SUM(o.total_amount), 0) >= 1000 THEN 3
        WHEN COALESCE(SUM(o.total_amount), 0) > 0 THEN 4
        ELSE 5
    END;

📌 CASE式の書き方

条件分岐で値を変換します。上から順に評価され、最初に合致した条件の値が返されます。

CASE 
    WHEN 条件1 THEN 値1
    WHEN 条件2 THEN 値2
    ELSE デフォルト値
END

課題 3-3：未購入顧客の抽出（チャレンジ）

課題 3-3 チャレンジ

まだ購入していない顧客を見つける

やること：一度も購入していない顧客を抽出してください。
表示する列：顧客ID、顧客名、メール、登録日
活用シーン：キャンペーンメールを送って購入を促す

方法1：LEFT JOINとIS NULLを使う

※横にスクロールできます

SELECT 
    c.customer_id AS 顧客ID,
    c.customer_name AS 顧客名,
    c.email AS メール,
    c.registration_date AS 登録日
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

📌 LEFT JOIN + IS NULLのパターン

Step 11で学んだテクニックです。

LEFT JOINで全顧客を取得
注文がない顧客は、order_idがNULLになる
WHERE o.order_id IS NULLで未購入顧客だけを抽出

方法2：NOT INを使う

※横にスクロールできます

SELECT 
    customer_id AS 顧客ID,
    customer_name AS 顧客名,
    email AS メール,
    registration_date AS 登録日
FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id FROM orders
);

📋 実行結果

顧客ID	顧客名	メール	登録日
10	加藤由美	NULL	2024-03-20

加藤由美さんは登録してから一度も購入していません。キャンペーンの対象にしましょう！

課題 3-4：VIP顧客の分析（チャレンジ）

課題 3-4 チャレンジ

VIP顧客の購入傾向を分析

やること：VIP顧客（is_vip = 1）と一般顧客の購入傾向を比較してください。
表示する列：顧客タイプ、顧客数、平均購入金額、平均購入回数

※横にスクロールできます

SELECT 
    CASE 
        WHEN c.is_vip = 1 THEN ‘VIP顧客’
        ELSE ‘一般顧客’
    END AS 顧客タイプ,
    COUNT(DISTINCT c.customer_id) AS 顧客数,
    ROUND(AVG(COALESCE(customer_total.total, 0))) AS 平均購入金額,
    ROUND(AVG(COALESCE(customer_total.order_count, 0)), 1) AS 平均購入回数
FROM customers c
LEFT JOIN (
    SELECT 
        customer_id,
        SUM(total_amount) AS total,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) customer_total
    ON c.customer_id = customer_total.customer_id
GROUP BY c.is_vip;

📌 サブクエリを使う理由

まず顧客ごとの購入金額と回数を集計し、それをVIP/一般で平均します。

サブクエリ内で顧客別の集計、外側でVIP/一般の平均を計算しています。

📋 実行結果

顧客タイプ	顧客数	平均購入金額	平均購入回数
VIP顧客	3	5050	2.7
一般顧客	7	1464	1.1

VIP顧客は一般顧客の約3.5倍の購入金額！

📦 6. 課題4：在庫管理

🎯 ミッション

適切な在庫を維持して、品切れや過剰在庫を防ぎましょう！

課題 4-1：在庫状況の確認（応用）

課題 4-1 応用

カテゴリ別の在庫状況

やること：カテゴリごとに、総在庫数、在庫切れ商品数、在庫20個以下の商品数を表示してください。
表示する列：カテゴリ、総在庫数、在庫切れ商品数、在庫少商品数、総商品数

※横にスクロールできます

SELECT 
    category AS カテゴリ,
    SUM(stock) AS 総在庫数,
    COUNT(CASE WHEN stock = 0 THEN 1 END) AS 在庫切れ商品数,
    COUNT(CASE WHEN stock > 0 AND stock <= 20 THEN 1 END) AS 在庫少商品数,
    COUNT(*) AS 総商品数
FROM products
GROUP BY category
ORDER BY 総在庫数 DESC;

📌 COUNT + CASE式のテクニック

条件に合う行だけをカウントするテクニックです。

COUNT(CASE WHEN stock = 0 THEN 1 END)
stock = 0 の場合だけ「1」を返し、それをカウント
条件に合わない場合はNULLになり、COUNTされない

📋 実行結果

カテゴリ	総在庫数	在庫少商品数	総商品数
野菜	225	0	4
果物	200	2	5
飲料	155	0	3

果物カテゴリに在庫少の商品が2つあります。

課題 4-2：発注が必要な商品（チャレンジ）

課題 4-2 チャレンジ

在庫が少なく、よく売れている商品

やること：以下の条件で「発注が必要な商品」をリストアップしてください。
条件：

在庫が30個以下
かつ、注文実績がある（1回以上売れている）

表示する列：商品名、カテゴリ、現在庫、販売数量、仕入先
並び順：在庫が少ない順

※横にスクロールできます

SELECT 
    p.product_name AS 商品名,
    p.category AS カテゴリ,
    p.stock AS 現在庫,
    SUM(od.quantity) AS 販売数量,
    p.supplier AS 仕入先
FROM products p
INNER JOIN order_details od
    ON p.product_id = od.product_id
WHERE p.stock <= 30
GROUP BY p.product_id, p.product_name, p.category, p.stock, p.supplier
ORDER BY p.stock ASC;

📋 実行結果

商品名	カテゴリ	現在庫	販売数量	仕入先
メロン	果物	5	4	農協A
ぶどう	果物	20	17	果物商B
りんごジュース	飲料	25	8	飲料メーカーD
オレンジジュース	飲料	30	9	飲料メーカーD

メロンは在庫5個で緊急発注が必要！ぶどうも要注意。

📝 Step 15 のまとめ

✅ このプロジェクトで使った技術

技術	使った場面
SELECT, WHERE	データの取得と絞り込み
ORDER BY, LIMIT	並び替えとランキング
GROUP BY, HAVING	グループ化と集計
COUNT, SUM, AVG, MAX	集計関数で数値を計算
INNER JOIN, LEFT JOIN	複数テーブルの結合
サブクエリ	複雑な条件の指定
CASE式	条件分岐（ランク分けなど）
COALESCE	NULLの処理
STRFTIME	日付の処理

🎓 習得したスキル

実務で使える検索クエリの作成
経営判断に役立つ分析レポートの作成
顧客理解のためのデータ分析
在庫管理のためのデータ活用
複数テーブルを使った複雑なクエリの構築

📌 実務への応用

このプロジェクトで学んだスキルは、以下のような実務に直接活かせます。

ECサイト：商品管理、売上分析、顧客管理
小売業：在庫管理、発注管理、売上予測
マーケティング：顧客セグメント、キャンペーン効果測定
データ分析：BIツール、ダッシュボード作成

🚀 7. 次のステップ

7-1. さらにスキルアップするために

🎯 応用コースで学べること

ウィンドウ関数：ROW_NUMBER, RANK, LAG, LEADなど
CTE（共通テーブル式）：WITH句を使った複雑なクエリ
インデックス：パフォーマンス最適化
ビュー：再利用可能なクエリの作成
ストアドプロシージャ：SQLの自動化

7-2. 実務で活躍するために

💼 おすすめの学習ステップ

実際のプロジェクトでSQLを使ってみる
BIツール（Tableau, Power BI）と組み合わせる
Python/Rなどと連携させる
データベース設計の原則を学ぶ
GitHubでSQLのポートフォリオを作る

❓ よくある質問

Q1: このプロジェクトは実務に近いですか？

はい、実務に非常に近いです。ECサイトや小売業で実際に使われているクエリをベースにしています。企業のデータアナリストが日常的に行う業務を体験できます。

Q2: 難しい課題ができませんでした…

大丈夫です！最初は解答を見ながら理解を深めましょう。何度も繰り返すうちに、自然と書けるようになります。特にJOINやサブクエリは練習が必要です。

Q3: 実際のデータベースで練習するには？

SQLiteやMySQLを使って、自分でテーブルを作って練習しましょう。Kaggleの公開データセットを使うのもおすすめです。実際のデータで試すことで、さらに理解が深まります。

Q4: SQLの資格を取るべきですか？

資格はあると有利ですが、必須ではありません。実務では「実際に書けること」が重要です。ポートフォリオを作って、GitHubで公開するのも良い方法です。

Q5: 次に学ぶべきことは？

目指すキャリアによって異なります。

データ分析志望：Python（pandas）、R、統計学
Webエンジニア志望：バックエンド言語（Python, Ruby, PHP）
データベースエンジニア志望：インデックス、パフォーマンス、設計

いずれにしても、SQLは基礎として非常に重要です！

📝

学習メモ

SQL基礎 - Step 15

📋 過去のメモ一覧 ▼

🎓 Step 15: 総合演習プロジェクト

🎯 1. プロジェクト概要

1-1. 仮想ECサイト「フルーツマート」

1-2. あなたの役割

📊 2. データベース構造

2-1. 4つのテーブル

2-2. テーブル間の関係

2-3. サンプルデータの作成

2-4. データ件数を確認

🔍 3. 課題1：商品検索システム

課題 1-1：カテゴリ別商品一覧（基本）

「果物」カテゴリの商品を価格順に表示

課題 1-2：価格帯で絞り込み（応用）

価格帯別のカテゴリ分析

課題 1-3：在庫切れ間近の商品（応用）

在庫が少ない商品をリストアップ

課題 1-4：人気商品ランキング（チャレンジ）

最も売れている商品TOP5

📈 4. 課題2：売上分析レポート

課題 2-1：月別売上集計（応用）

月別の売上を集計

課題 2-2：カテゴリ別売上シェア（チャレンジ）

各カテゴリの売上シェアを計算

課題 2-3：都道府県別売上（チャレンジ）

都道府県別の売上トップ5

課題 2-4：仕入先別の販売状況（チャレンジ）

仕入先ごとの販売実績

👥 5. 課題3：顧客分析

課題 3-1：顧客の購入履歴（応用）

顧客ごとの購入実績を表示

課題 3-2：顧客ランク分け（チャレンジ）

購入金額で顧客をランク分け

課題 3-3：未購入顧客の抽出（チャレンジ）

まだ購入していない顧客を見つける

課題 3-4：VIP顧客の分析（チャレンジ）

VIP顧客の購入傾向を分析

📦 6. 課題4：在庫管理

課題 4-1：在庫状況の確認（応用）

カテゴリ別の在庫状況

課題 4-2：発注が必要な商品（チャレンジ）

在庫が少なく、よく売れている商品

📝 Step 15 のまとめ

🚀 7. 次のステップ

7-1. さらにスキルアップするために

7-2. 実務で活躍するために

❓ よくある質問

Q1: このプロジェクトは実務に近いですか？

Q2: 難しい課題ができませんでした…

Q3: 実際のデータベースで練習するには？

Q4: SQLの資格を取るべきですか？

Q5: 次に学ぶべきことは？

🎉 おめでとうございます！

学習メモ