演習 - 基本クエリ総合演習

Q: 質問：LIMIT OFFSETのページネーションで、データが追加されたり削除されたりした場合、重複や欠落が発生しますか？

回答： はい。ページネーション中にデータが変更されると、OFFSETベースのページネーションでは重複や欠落が発生する可能性があります。より安定したアプローチは「カーソルページネーション」です：WHERE id > 前ページの最後のID LIMIT 10。ただし、各レコードが一貫したソートキーを持つ必要があります。

Q: 質問： LIKE '%キーワード%' はインデックスを無効にします。大規模データセットではどうすればいいですか？

回答： ワイルドカードで始まるLIKEは通常のインデックスを使用できません。大規模データセットでは、①フルテキスト検索（FTS5、SQLiteがサポート）の使用、②アプリケーション層でのElasticsearchなどの検索エンジンの使用、③前方一致のみ必要な場合はLIKE 'キーワード%'でインデックスを活用できます。

Q: 質問：JOINクエリで2つのテーブルに同じ列名がある場合はどうすればいいですか？

回答： テーブルエイリアスまたは列エイリアスを使用して区別します。例：SELECT e.name, d.name AS dept_name FROM employees e JOIN departments d ON e.department_id = d.id;。出力をより明確にするため、列にエイリアスを付ける習慣をつけましょう。

Q: 質問：UPDATEやDELETE操作は元に戻せますか？

回答： トランザクション内（BEGIN...COMMITの間）であれば、ROLLBACKで元に戻せます。すでにCOMMITされている場合、SQLiteでは基本的に回復不可能です（バックアップがない限り）。MySQLではbinlogリプレイで回復できます。そのため、本番操作の前に必ずバックアップを取ってください。

演習：基本クエリ総合演習

最初の5レッスンでは、SQLの「読み書き」を学びました — テーブル作成、クエリ、フィルタリング、ソート、CRUD操作、データ型です。これらのスキルを組み合わせて、実際のビジネス課題を解決する時が来ました。このレッスンに新しい構文はありません — 練習のみです。

プロジェクト要件

あなたはEC企業のデータアナリストで、企業のデータベースから以下の情報を抽出する必要があります：

従業員管理：特定の部署の従業員をクエリし、給与でソートし、結果をページネーション
商品フィルタリング：価格範囲と在庫で商品をフィルタリングし、商品名であいまい検索
注文分析：最近の注文をクエリし、注文金額を計算し、注文ステータスを更新
データメンテナンス：価格の一括更新、期限切れデータのクリーンアップ、新しいデータの挿入

データベースには4つのテーブルがあります：employees、departments、orders、products。

完全なコード実装

ステップ1：データベースとテーブルの作成

SQL

-- 部署テーブルを作成
CREATE TABLE departments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    location TEXT
);

-- 従業員テーブルを作成
CREATE TABLE employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    department_id INTEGER,
    salary DECIMAL(10,2),
    hire_date DATE,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 商品テーブルを作成
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category TEXT,
    price DECIMAL(10,2),
    stock INTEGER DEFAULT 0
);

-- 注文テーブルを作成
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_name TEXT NOT NULL,
    product_id INTEGER,
    quantity INTEGER,
    order_date DATE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

ステップ2：テストデータの挿入

SQL

-- 部署データを挿入
INSERT INTO departments (name, location) VALUES
    ('Engineering', 'New York'),
    ('Marketing', 'Los Angeles'),
    ('Finance', 'New York'),
    ('HR', 'Chicago');

-- 従業員データを挿入
INSERT INTO employees (name, department_id, salary, hire_date) VALUES
    ('John', 1, 15000.00, '2023-01-15'),
    ('Jane', 1, 18000.00, '2022-06-01'),
    ('Bob', 2, 12000.00, '2023-03-20'),
    ('Alice', 3, 13000.00, '2021-11-10'),
    ('Charlie', 1, 20000.00, '2020-08-05'),
    ('Diana', 2, 11000.00, '2024-01-10'),
    ('Eve', NULL, 9000.00, '2024-05-01'),
    ('Frank', 3, 14000.00, '2022-09-15');

-- 商品データを挿入
INSERT INTO products (name, category, price, stock) VALUES
    ('iPhone 15', 'Phone', 5999.00, 100),
    ('MacBook Pro', 'Computer', 12999.00, 50),
    ('AirPods Pro', 'Accessory', 1899.00, 200),
    ('iPad Air', 'Tablet', 4799.00, 80),
    ('Apple Watch', 'Watch', 2999.00, 150),
    ('Magic Keyboard', 'Accessory', 999.00, 300),
    ('Mac Mini', 'Computer', 4499.00, 0),
    ('iPhone 14', 'Phone', 4999.00, 20);

-- 注文データを挿入
INSERT INTO orders (customer_name, product_id, quantity, order_date) VALUES
    ('Tom', 1, 1, '2024-06-01'),
    ('Lucy', 3, 2, '2024-06-02'),
    ('Mike', 2, 1, '2024-06-03'),
    ('Tom', 5, 1, '2024-06-05'),
    ('Sarah', 4, 3, '2024-06-10'),
    ('Lucy', 6, 5, '2024-06-15'),
    ('David', 1, 2, '2024-06-20'),
    ('Mike', 8, 1, '2024-06-25');

ステップ3：従業員管理クエリ

SQL

-- Q1：エンジニアリング部門の全従業員を、給与が高い順にソート
SELECT name, salary, hire_date
FROM employees
WHERE department_id = 1
ORDER BY salary DESC;

出力：

TEXT

name     salary    hire_date
-------  --------  ----------
Charlie  20000.00  2020-08-05
Jane     18000.00  2022-06-01
John     15000.00  2023-01-15

SQL

-- Q2：給与上位3名をクエリ（ページネーション：1ページ目、1ページ3件）
SELECT name, salary, department_id
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 0;

出力：

TEXT

name     salary    department_id
-------  --------  -------------
Charlie  20000.00  1
Jane     18000.00  1
John     15000.00  1

SQL

-- Q3：ページ2をクエリ（1ページ3件）
SELECT name, salary, department_id
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 3;

出力：

TEXT

name   salary    department_id
-----  --------  -------------
Frank  14000.00  3
Alice  13000.00  3
Bob    12000.00  2

SQL

-- Q4：部署が未割り当ての従業員をクエリ
SELECT name, salary
FROM employees
WHERE department_id IS NULL;

出力：

TEXT

name  salary
----  --------
Eve   9000.00

ステップ4：商品フィルタリングクエリ

SQL

-- Q5：価格が1000から5000の間の商品を、価格昇順でクエリ
SELECT name, category, price, stock
FROM products
WHERE price BETWEEN 1000 AND 5000
ORDER BY price ASC;

出力：

TEXT

name            category  price    stock
--------------  --------  -------  -----
AirPods Pro     Accessory 1899.00  200
Apple Watch     Watch     2999.00  150
Mac Mini        Computer  4499.00  0
iPad Air        Tablet    4799.00  80

SQL

-- Q6：名前に「iPhone」を含む商品をあいまい検索
SELECT name, price, stock
FROM products
WHERE name LIKE '%iPhone%';

出力：

TEXT

name        price    stock
----------  -------  -----
iPhone 15   5999.00  100
iPhone 14   4999.00  20

SQL

-- Q7：在庫がある（stock > 0）商品のうち、価格が3000未満のものをクエリ
SELECT name, price, stock
FROM products
WHERE stock > 0 AND price < 3000
ORDER BY price DESC;

出力：

TEXT

name            price    stock
--------------  -------  -----
AirPods Pro     1899.00  200
Magic Keyboard  999.00   300

ステップ5：注文分析クエリ

SQL

-- Q8：最近の5件の注文をクエリし、商品名と顧客名を表示
SELECT o.customer_name, p.name AS product_name,
       o.quantity, o.order_date
FROM orders o
JOIN products p ON o.product_id = p.id
ORDER BY o.order_date DESC
LIMIT 5;

出力：

TEXT

customer_name  product_name   quantity  order_date
-------------  -------------  --------  ----------
Mike           iPhone 14      1         2024-06-25
David          iPhone 15      2         2024-06-20
Lucy           Magic Keyboard 5         2024-06-15
Sarah          iPad Air       3         2024-06-10
Tom            Apple Watch    1         2024-06-05

SQL

-- Q9：各注文の金額（単価×数量）をクエリし、金額降順でソート
SELECT o.customer_name, p.name AS product_name,
       p.price, o.quantity,
       (p.price * o.quantity) AS total_amount
FROM orders o
JOIN products p ON o.product_id = p.id
ORDER BY total_amount DESC;

出力：

TEXT

customer_name  product_name   price     quantity  total_amount
-------------  -------------  --------  --------  ------------
Sarah          iPad Air       4799.00   3         14397.00
Mike           MacBook Pro    12999.00  1         12999.00
David          iPhone 15      5999.00   2         11998.00
Tom            iPhone 15      5999.00   1         5999.00
Lucy           Magic Keyboard 999.00    5         4995.00
Mike           iPhone 14      4999.00   1         4999.00
Tom            Apple Watch    2999.00   1         2999.00
Lucy           AirPods Pro    1899.00   2         3798.00

ステップ6：データメンテナンス操作

SQL

-- Q10：「Accessory」カテゴリのすべての商品の価格を10%引き上げ
-- まず影響範囲を確認
SELECT name, price FROM products WHERE category = 'Accessory';

調整前：

TEXT

name            price
--------------  -------
AirPods Pro     1899.00
Magic Keyboard  999.00

SQL

-- 更新を実行
UPDATE products
SET price = ROUND(price * 1.1, 2)
WHERE category = 'Accessory';

-- 結果を確認
SELECT name, price FROM products WHERE category = 'Accessory';

調整後：

TEXT

name            price
--------------  -------
AirPods Pro     2088.90
Magic Keyboard  1098.90

SQL

-- Q11：在庫切れ商品（stock = 0）を販売停止としてマーク
-- ここでは例として在庫切れ商品を削除します
-- まず確認
SELECT id, name, stock FROM products WHERE stock = 0;

確認結果：

TEXT

id  name      stock
--  --------  -----
7   Mac Mini  0

SQL

-- 在庫切れ商品を削除
DELETE FROM products WHERE stock = 0;

-- Q12：新しい商品を挿入
INSERT INTO products (name, category, price, stock)
VALUES ('AirTag', 'Accessory', 229.00, 500);

-- 最終的なすべての商品を表示
SELECT id, name, category, price, stock
FROM products
ORDER BY id;

最終商品リスト：

TEXT

id  name            category  price     stock
--  --------------  --------  --------  -----
1   iPhone 15       Phone     5999.00   100
2   MacBook Pro     Computer  12999.00  50
3   AirPods Pro     Accessory 2088.90   200
4   iPad Air        Tablet    4799.00   80
5   Apple Watch     Watch     2999.00   150
6   Magic Keyboard  Accessory 1098.90   300
8   iPhone 14       Phone     4999.00   20
9   AirTag          Accessory 229.00    500

コード解説

クエリ	使用した知識ポイント	主要テクニック
Q1-Q3	SELECT + WHERE + ORDER BY + LIMIT/OFFSET	ページネーションは`LIMIT n OFFSET (page-1)*n`を使用
Q4	WHERE + IS NULL	NULLの確認にはIS NULLを使用
Q5	BETWEEN + ORDER BY	BETWEENは両方の端点値を含む
Q6	LIKE + ワイルドカード`%`	`%キーワード%`は任意の位置にマッチ
Q7	複数のAND条件 + 比較演算子	条件を組み合わせたフィルタリング
Q8	JOIN + ORDER BY + LIMIT	複数テーブルの関連クエリ
Q9	JOIN + 式計算 + ORDER BY	SELECTでの計算
Q10	UPDATE + WHERE + ROUND	更新前にSELECTで確認
Q11	DELETE + WHERE	削除前にSELECTで確認
Q12	INSERT + 最終検証	挿入後にクエリで確認

❓ よくある質問

質問：LIMIT OFFSETのページネーションで、データが追加されたり削除されたりした場合、重複や欠落が発生しますか？ 回答： はい。ページネーション中にデータが変更されると、OFFSETベースのページネーションでは重複や欠落が発生する可能性があります。より安定したアプローチは「カーソルページネーション」です：WHERE id > 前ページの最後のID LIMIT 10。ただし、各レコードが一貫したソートキーを持つ必要があります。

質問：LIKE '%キーワード%'はインデックスを無効にします。大規模データセットではどうすればいいですか？ 回答： ワイルドカードで始まるLIKEは通常のインデックスを使用できません。大規模データセットでは、①フルテキスト検索（FTS5、SQLiteがサポート）の使用、②アプリケーション層でのElasticsearchなどの検索エンジンの使用、③前方一致のみ必要な場合はLIKE 'キーワード%'でインデックスを活用できます。

質問：JOINクエリで2つのテーブルに同じ列名がある場合はどうすればいいですか？ 回答： テーブルエイリアスまたは列エイリアスを使用して区別します。例：SELECT e.name, d.name AS dept_name FROM employees e JOIN departments d ON e.department_id = d.id;。出力をより明確にするため、列にエイリアスを付ける習慣をつけましょう。

質問：UPDATEやDELETE操作は元に戻せますか？ 回答： トランザクション内（BEGIN...COMMITの間）であれば、ROLLBACKで元に戻せます。すでにCOMMITされている場合、SQLiteでは基本的に回復不可能です（バックアップがない限り）。MySQLではbinlogリプレイで回復できます。そのため、本番操作の前に必ずバックアップを取ってください。

📖 まとめ

SELECT + WHERE + ORDER BY + LIMITを組み合わせて、複雑なクエリとページネーションを実現
NULL値にはIS NULL、あいまい検索にはLIKE、範囲クエリにはBETWEENを使用
複数テーブルのクエリにはJOINを使用。計算フィールドはSELECTで式を使用
UPDATE/DELETE前にSELECTで確認 — これが最も重要な安全習慣
ページネーションにはLIMIT + OFFSETを使用。大規模データセットではカーソルページネーションを検討
%で始まるあいまい検索はインデックスを無効にする。大規模データセットではフルテキスト検索を検討

📝 演習

演習1（⭐）

以下のタスクを完了するクエリを書いてください：

2023年に入社したすべての従業員を、入社日昇順でクエリ
「Phone」カテゴリの最も高価な商品をクエリ
顧客「Tom」のすべての注文をクエリし、商品名と数量を表示

演習2（⭐⭐）

以下のタスクを完了するクエリを書いてください：

各部署の従業員数をクエリ（ヒント：GROUP BYが必要 — 先に後のレッスンに進んで戻ってくることもできます）
すべての注文の合計金額をクエリ
「Computer」カテゴリの商品の価格を5%引き下げ、クエリで確認

演習3（⭐⭐⭐）

「在庫不足アラート」シナリオをシミュレート：

在庫が50未満のすべての商品を、在庫昇順でクエリ
これらの商品を新しく作成したlow_stock_alertテーブルに挿入
これらの商品それぞれの在庫を100増加
更新結果を確認

次のレッスン

👉 07-JOINクエリ入門

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて