Lecture 1データベースとは — なぜSQLを学ぶのか

8:00

データベースとは — なぜSQLを学ぶのか

データベースが必要な理由

あなたのスマートフォンには連絡先アプリがあります。名前、電話番号、メールアドレス — これらの情報は「データ」です。連絡先が10件なら紙のメモ帳でも管理できますが、10万件になったらどうでしょうか。「東京都在住で、過去3ヶ月以内に連絡した人」を瞬時に検索したいとき、メモ帳では不可能です。

データベース（Database） とは、大量のデータを構造化して保存し、高速に検索・更新できる仕組みです。Webサービス、銀行システム、病院の電子カルテ、ECサイトの商品管理 — 現代のほぼすべてのシステムの裏側にはデータベースがあります。

Amazonで商品を検索するとき、裏では数億件の商品データベースに対してクエリ（問い合わせ）が実行されています。その応答時間はわずか数百ミリ秒です。この魔法のような仕組みを支えているのが、データベース管理システム（DBMS）とSQLです。

リレーショナルデータベースとNoSQL

データベースには大きく分けて2つの種類があります。

特徴	リレーショナルDB（RDBMS）	NoSQL
データ構造	テーブル（行と列）	ドキュメント、キー値、グラフなど
スキーマ	厳密に定義（型・制約）	柔軟（スキーマレス）
クエリ言語	SQL（標準化済み）	製品ごとに異なる
トランザクション	ACID準拠（強い一貫性）	BASE（結果的一貫性が多い）
代表製品	PostgreSQL, MySQL, Oracle	MongoDB, Redis, DynamoDB
適した用途	業務システム、金融、EC	リアルタイム分析、IoT、SNS

この講座ではリレーショナルデータベース（RDBMS）を扱います。データを「テーブル」（表）として管理し、テーブル同士を「リレーション」（関係）で結び付けるのが特徴です。

なぜSQLを学ぶべきなのか

SQL（Structured Query Language） は、リレーショナルデータベースを操作するための標準言語です。1974年にIBMで開発され、1986年にはISO/IEC 9075として国際標準化されました。50年以上の歴史を持ちながら、今なお最も重要な技術スキルの1つです。

Stack Overflow Developer Survey 2024によると、SQLは「最も使われているプログラミング言語」の上位に毎年ランクインしています。プログラマーだけでなく、データアナリスト、マーケター、経営企画、営業 — あらゆる職種でSQLスキルが求められる時代になりました。

SQLを学ぶ具体的なメリットは以下の通りです：

普遍性: PostgreSQL、MySQL、SQLite、SQL Server、Oracle — どの製品でもSQLの基本構文は共通
需要: 求人サイトで「SQL」は常にトップクラスの検索キーワード
即効性: 1時間の学習で、すぐにデータを検索・集計できるようになる
相乗効果: Python、Excel、BIツールなど他のスキルと組み合わせると効果倍増

主要なRDBMS製品

製品	特徴	主な用途	ライセンス
PostgreSQL	高機能・拡張性が高い	業務システム、Web	オープンソース（無料）
MySQL	高速・広い普及率	Web（WordPress等）	オープンソース（無料）
SQLite	サーバ不要・ファイルベース	モバイル、組み込み、学習	パブリックドメイン（無料）
SQL Server	Microsoft製・Windows統合	エンタープライズ	商用（Express版は無料）
Oracle Database	大規模・高可用性	金融・官公庁	商用（XE版は無料）

初学者には SQLite を最も推奨します。インストールが簡単で、データベースが1つのファイルとして保存されるため、管理が容易です。本格的な開発に進む際は PostgreSQL がおすすめです。

開発環境のセットアップ

方法1: SQLite + DB Browser for SQLite（最も簡単）

DB Browser for SQLite を公式サイト（https://sqlitebrowser.org/）からダウンロード
インストーラーを実行（Windows / macOS / Linux対応）
起動して「New Database」をクリック → ファイル名を practice.db として保存
「Execute SQL」タブでSQLを入力・実行できます

方法2: PostgreSQL + pgAdmin（本格開発向け）

PostgreSQL を公式サイト（https://www.postgresql.org/download/）からダウンロード
インストール時にpgAdminも同時にインストールされます
pgAdminを起動し、サーバーに接続
「Query Tool」でSQLを入力・実行できます

方法3: オンライン環境（インストール不要）

すぐに試したい方は、以下のオンラインサービスが便利です：

DB Fiddle（https://www.db-fiddle.com/） — ブラウザでPostgreSQL/MySQL/SQLiteを実行
SQLite Online（https://sqliteonline.com/） — SQLiteに特化したオンラインエディタ

最初のSQL — Hello, SQL!

環境が整ったら、以下のSQLを実行してみましょう：

-- これがあなたの最初のSQLクエリです
SELECT 'Hello, SQL!';

実行結果：

Hello, SQL!

SELECT はデータを取得するための命令です。ここではデータベースのテーブルではなく、文字列リテラル 'Hello, SQL!' を直接指定しています。SQLでは文字列をシングルクォート（'）で囲みます。

もう少し試してみましょう：

-- 計算もできます
SELECT 1 + 1;

-- 現在の日時を取得（PostgreSQLの場合）
SELECT NOW();

-- 現在の日時を取得（SQLiteの場合）
SELECT datetime('now', 'localtime');

-- 複数の値を同時に取得
SELECT 'SQL入門' AS 講座名, 2026 AS 開講年, 5 AS 全レクチャー数;

-- はコメントです。コメントは実行されず、メモとして使います。AS を使うと、結果に表示される列名を指定できます（エイリアス）。

実践ワーク

以下の課題をお使いの環境で実行してください：

SELECT 'こんにちは、SQL!'; を実行し、結果を確認する
SELECT 100 * 1.1; を実行し、消費税込み計算の結果を確認する
SELECT 'あなたの名前' AS 名前, 2026 - 1990 AS 年齢; の 'あなたの名前' と 1990 を自分の情報に書き換えて実行する
SELECT 10 / 3; と SELECT 10.0 / 3; の結果を比較し、整数除算と小数除算の違いを確認する

まとめと次回の準備

今回のポイント: - データベースは大量のデータを構造化して管理する仕組み - RDBMSはテーブル（表）でデータを管理し、SQLで操作する - SQLは国際標準（ISO/IEC 9075）で、どのRDBMS製品でも基本構文は共通 - 初学者にはSQLite + DB Browser for SQLiteがおすすめ - SELECT 文でデータの取得や計算ができる

次回: SELECT文の基本構文を本格的に学びます。テーブルからデータを取り出す方法、列の選択、エイリアス、DISTINCTなど、SQLの最も基本的な操作をマスターします。

参考文献: - PostgreSQL公式ドキュメント（https://www.postgresql.org/docs/current/） - SQLite公式サイト（https://www.sqlite.org/docs.html） - ISO/IEC 9075 SQL標準（https://www.iso.org/standard/76583.html） - Stack Overflow Developer Survey 2024（https://survey.stackoverflow.co/2024/） - DB Browser for SQLite（https://sqlitebrowser.org/）

Lecture 2SELECT文の基本 — データを取り出す

12:00

SELECT文の基本 — データを取り出す

サンプルテーブルの準備

この講座全体を通して使う employees テーブルを作成します。以下のSQLを実行してください：

-- サンプルテーブルの作成
CREATE TABLE employees (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    department TEXT NOT NULL,
    salary     INTEGER NOT NULL,
    hire_date  TEXT NOT NULL
);

-- サンプルデータの挿入
INSERT INTO employees (id, name, department, salary, hire_date) VALUES
(1,  '田中太郎',   '営業部',   350000, '2020-04-01'),
(2,  '鈴木花子',   '開発部',   420000, '2019-07-15'),
(3,  '佐藤一郎',   '営業部',   380000, '2021-01-10'),
(4,  '山田美咲',   '人事部',   360000, '2020-10-01'),
(5,  '高橋健太',   '開発部',   450000, '2018-04-01'),
(6,  '伊藤あゆみ', '営業部',   320000, '2022-04-01'),
(7,  '渡辺直人',   '開発部',   400000, '2020-04-01'),
(8,  '小林恵子',   '人事部',   340000, '2023-01-16'),
(9,  '加藤大輔',   '営業部',   370000, '2019-10-01'),
(10, '吉田さくら', '開発部',   480000, '2017-04-01');

このテーブルは架空の企業の従業員データです。10名の社員について、社員番号（id）、名前（name）、所属部署（department）、月給（salary）、入社日（hire_date）を記録しています。

SELECT文の基本構文

SELECT文は「テーブルからデータを取り出す」ための命令です。SQLの中で最も頻繁に使う構文であり、この講座の中核です。

SELECT 列名1, 列名2, ...
FROM テーブル名;

SELECT の後に取得したい列名を指定し、FROM の後にテーブル名を指定します。SQLのキーワード（SELECT, FROM など）は大文字で書く慣習がありますが、小文字でも動作します。本講座では可読性のために大文字を使います。

すべての列を取得する — SELECT *

-- 全列を取得
SELECT * FROM employees;

*（アスタリスク）はワイルドカードで、「すべての列」を意味します。結果は以下のようになります：

id | name         | department | salary | hire_date
---|--------------|------------|--------|----------
1  | 田中太郎     | 営業部     | 350000 | 2020-04-01
2  | 鈴木花子     | 開発部     | 420000 | 2019-07-15
3  | 佐藤一郎     | 営業部     | 380000 | 2021-01-10
...

注意: 本番環境では SELECT * の使用は避けましょう。不要な列まで取得するとパフォーマンスが低下します。必要な列を明示的に指定するのがベストプラクティスです。

特定の列を取得する

-- 名前と部署だけ取得
SELECT name, department FROM employees;

name         | department
-------------|----------
田中太郎     | 営業部
鈴木花子     | 開発部
佐藤一郎     | 営業部
山田美咲     | 人事部
...

列の順番は SELECT で指定した順番になります。テーブルの定義順とは関係ありません。

-- 順番を変えて取得
SELECT department, name, salary FROM employees;

列にエイリアス（別名）を付ける — AS

-- 日本語のエイリアスを付ける
SELECT
    name       AS 社員名,
    department AS 部署,
    salary     AS 月給
FROM employees;

社員名       | 部署   | 月給
-------------|--------|------
田中太郎     | 営業部 | 350000
鈴木花子     | 開発部 | 420000
...

AS キーワードで列に別名（エイリアス）を付けられます。レポート作成時や、列名が分かりにくい場合に便利です。AS は省略可能ですが、可読性のために記述することを推奨します。

-- ASを省略した書き方（動作は同じだが、ASありを推奨）
SELECT name 社員名, salary 月給 FROM employees;

重複を除外する — DISTINCT

-- 全部署を一覧で取得（重複あり）
SELECT department FROM employees;

department
----------
営業部
開発部
営業部
人事部
開発部
営業部
開発部
人事部
営業部
開発部

10行すべてが返り、部署名が重複しています。重複を除外するには DISTINCT を使います：

-- 重複を除外して一覧取得
SELECT DISTINCT department FROM employees;

department
----------
営業部
開発部
人事部

3つの一意な部署名だけが返ります。DISTINCT は複数列にも適用できます：

-- 部署と入社年の組み合わせで重複を除外
SELECT DISTINCT department, substr(hire_date, 1, 4) AS 入社年
FROM employees;

計算列 — 算術演算

SELECT文の中で計算を行い、結果を新しい列として表示できます。

-- 年収を計算（月給 × 12）
SELECT
    name       AS 社員名,
    salary     AS 月給,
    salary * 12 AS 年収
FROM employees;

社員名       | 月給   | 年収
-------------|--------|--------
田中太郎     | 350000 | 4200000
鈴木花子     | 420000 | 5040000
高橋健太     | 450000 | 5400000
...

SQLでは四則演算（+, -, *, /）と剰余（%）が使えます：

-- 手取り額の概算（月給の80%）と賞与込み年収
SELECT
    name                        AS 社員名,
    salary                      AS 月給,
    CAST(salary * 0.8 AS INTEGER)  AS 手取り概算,
    salary * 12 + salary * 4    AS 賞与込み年収
FROM employees;

salary * 12 + salary * 4 は「月給12ヶ月分 + ボーナス4ヶ月分」の計算です。このように、ビジネスの現場で必要な数値をSQLだけで算出できます。

文字列の連結

文字列を結合して、1つの列にまとめることができます。構文はRDBMS製品によって異なります：

-- SQLite / PostgreSQL: || 演算子で文字列連結
SELECT
    name || '（' || department || '）' AS 社員情報
FROM employees;

-- MySQL: CONCAT関数を使用
-- SELECT CONCAT(name, '（', department, '）') AS 社員情報
-- FROM employees;

社員情報
-----------------------
田中太郎（営業部）
鈴木花子（開発部）
佐藤一郎（営業部）
...

文字列連結は、表示用のラベルを作成するときに頻繁に使います。使用するRDBMSの構文を確認しておきましょう。

SELECT文の実行順序

SQL文は書かれた順番で実行されるわけではありません。内部的な実行順序は以下の通りです：

1. FROM    — テーブルを決定
2. WHERE   — 行をフィルタリング（次回学習）
3. SELECT  — 取得する列を決定
4. DISTINCT — 重複を除外
5. ORDER BY — 並べ替え（第4回で学習）
6. LIMIT   — 件数制限（第4回で学習）

この実行順序を理解していると、エラーの原因を特定しやすくなります。例えば、SELECT で定義したエイリアスは WHERE では使えません（WHERE の方が先に実行されるため）。

実践ワーク

以下のクエリを作成して実行してください：

-- 課題1: 社員の名前と入社日だけを取得してください
SELECT name AS 社員名, hire_date AS 入社日
FROM employees;

-- 課題2: 月給を「千円単位」で表示してください（例: 350000 → 350）
SELECT name AS 社員名, salary / 1000 AS 月給千円
FROM employees;

-- 課題3: 全社員の部署一覧を重複なしで取得してください
SELECT DISTINCT department AS 部署 FROM employees;

-- 課題4: 社員名と「月給×16ヶ月」の想定年収を表示してください（ボーナス4ヶ月と仮定）
SELECT
    name                  AS 社員名,
    salary * 16           AS 想定年収,
    salary * 16 / 10000   AS 想定年収万円
FROM employees;

-- 課題5（チャレンジ）: 名前・部署・年収を1行の文字列にまとめてください
SELECT
    name || ' / ' || department || ' / 年収' || CAST(salary * 12 AS TEXT) || '円'
    AS 社員サマリー
FROM employees;

まとめと次回の準備

今回のポイント: - SELECT 列名 FROM テーブル名 がSQLの最も基本的な構文 - SELECT * は全列取得。本番では必要な列を明示指定する - AS でエイリアス（別名）を付けて、結果を分かりやすくする - DISTINCT で重複行を除外する - SELECT 内で算術演算（+, -, *, /）や文字列連結ができる - SQL文の実行順序は書かれた順番とは異なる

次回: WHERE句を使って条件でデータを絞り込む方法を学びます。比較演算子、AND/OR/NOT、IN、BETWEEN、LIKE、IS NULLなど、実務で必須のフィルタリング技術をマスターします。

参考文献: - PostgreSQL公式ドキュメント「SELECT」（https://www.postgresql.org/docs/current/sql-select.html） - SQLite公式ドキュメント「SELECT」（https://www.sqlite.org/lang_select.html） - MDN Web Docs「SQL入門」（https://developer.mozilla.org/ja/docs/Glossary/SQL） - W3Schools SQL Tutorial（https://www.w3schools.com/sql/sql_select.asp）

Lecture 3WHERE句とフィルタリング — 条件でデータを絞る

12:00

WHERE句とフィルタリング — 条件でデータを絞る

WHERE句とは

前回のSELECT文では、テーブルの全行を取得しました。しかし実務では「営業部の社員だけ」「月給35万円以上の人だけ」といった条件でデータを絞り込む場面がほとんどです。

WHERE句 は、SELECT文に条件を追加して、条件を満たす行だけを取得するための構文です。

SELECT 列名1, 列名2, ...
FROM テーブル名
WHERE 条件;

前回作成した employees テーブルを引き続き使用します。テーブルがない場合は、前回のCREATE TABLE文とINSERT文を再度実行してください。

比較演算子

最も基本的な条件指定は、比較演算子を使ったものです。

演算子	意味	例
`=`	等しい	`department = '営業部'`
`<>` または `!=`	等しくない	`department <> '人事部'`
`<`	より小さい	`salary < 400000`
`>`	より大きい	`salary > 400000`
`<=`	以下	`salary <= 400000`
`>=`	以上	`salary >= 400000`

注意: SQLの等価比較は = が1つです。プログラミング言語の == とは異なります。

文字列の条件

-- 営業部の社員を取得
SELECT name, department, salary
FROM employees
WHERE department = '営業部';

name       | department | salary
-----------|------------|-------
田中太郎   | 営業部     | 350000
佐藤一郎   | 営業部     | 380000
伊藤あゆみ | 営業部     | 320000
加藤大輔   | 営業部     | 370000

文字列の条件はシングルクォートで囲みます。SQLでは文字列比較において大文字・小文字の扱いがRDBMS製品によって異なります。PostgreSQLは大文字と小文字を区別しますが、MySQLはデフォルトで区別しません。

数値の条件

-- 月給40万円以上の社員
SELECT name, department, salary
FROM employees
WHERE salary >= 400000;

name       | department | salary
-----------|------------|-------
鈴木花子   | 開発部     | 420000
高橋健太   | 開発部     | 450000
渡辺直人   | 開発部     | 400000
吉田さくら | 開発部     | 480000

月給40万円以上の社員は全員開発部であることが分かります。このように、WHERE句はデータの分析にも役立ちます。

日付の条件

-- 2021年以降に入社した社員
SELECT name, department, hire_date
FROM employees
WHERE hire_date >= '2021-01-01';

name       | department | hire_date
-----------|------------|----------
佐藤一郎   | 営業部     | 2021-01-10
伊藤あゆみ | 営業部     | 2022-04-01
小林恵子   | 人事部     | 2023-01-16

日付も文字列としてシングルクォートで囲みます。YYYY-MM-DD 形式であれば、文字列としての比較でも正しく日付順の比較ができます。

論理演算子 — AND, OR, NOT

複数の条件を組み合わせるには論理演算子を使います。

AND（かつ）

-- 営業部で、かつ月給35万円以上の社員
SELECT name, department, salary
FROM employees
WHERE department = '営業部'
  AND salary >= 350000;

name     | department | salary
---------|------------|-------
田中太郎 | 営業部     | 350000
佐藤一郎 | 営業部     | 380000
加藤大輔 | 営業部     | 370000

AND は両方の条件を満たす行だけを返します。

OR（または）

-- 営業部または人事部の社員
SELECT name, department, salary
FROM employees
WHERE department = '営業部'
   OR department = '人事部';

name       | department | salary
-----------|------------|-------
田中太郎   | 営業部     | 350000
佐藤一郎   | 営業部     | 380000
山田美咲   | 人事部     | 360000
伊藤あゆみ | 営業部     | 320000
小林恵子   | 人事部     | 340000
加藤大輔   | 営業部     | 370000

OR はどちらか一方の条件を満たす行を返します。

NOT（否定）

-- 開発部以外の社員
SELECT name, department, salary
FROM employees
WHERE NOT department = '開発部';

NOT は条件を反転させます。WHERE department <> '開発部' と同じ結果になりますが、NOT は複雑な条件の否定に便利です。

演算子の優先順位

AND は OR より優先順位が高いため、組み合わせるときは括弧を使って意図を明確にしましょう。

-- 意図: 「(営業部 または 人事部) かつ 月給35万以上」
-- 括弧なしだと意図通りにならない可能性がある
SELECT name, department, salary
FROM employees
WHERE (department = '営業部' OR department = '人事部')
  AND salary >= 350000;

name     | department | salary
---------|------------|-------
田中太郎 | 営業部     | 350000
佐藤一郎 | 営業部     | 380000
山田美咲 | 人事部     | 360000
加藤大輔 | 営業部     | 370000

ルール: 複数の論理演算子を使うときは、必ず括弧で優先順位を明示する。

IN — 複数の値に一致

OR を繰り返し書く代わりに、IN を使うとスッキリ書けます。

-- 営業部または人事部の社員（INを使用）
SELECT name, department, salary
FROM employees
WHERE department IN ('営業部', '人事部');

これは先ほどの OR を使ったクエリと同じ結果です。値が3つ以上になると IN の方が圧倒的に読みやすくなります。

-- NOT IN: 指定した値以外
SELECT name, department
FROM employees
WHERE department NOT IN ('開発部');

BETWEEN — 範囲指定

-- 月給35万〜40万の社員
SELECT name, department, salary
FROM employees
WHERE salary BETWEEN 350000 AND 400000;

name     | department | salary
---------|------------|-------
田中太郎 | 営業部     | 350000
佐藤一郎 | 営業部     | 380000
山田美咲 | 人事部     | 360000
渡辺直人 | 開発部     | 400000
加藤大輔 | 営業部     | 370000

BETWEEN A AND B は A以上 B以下（境界値を含む）です。salary >= 350000 AND salary <= 400000 と同じ意味ですが、BETWEEN の方が簡潔です。

日付にも使えます：

-- 2020年に入社した社員
SELECT name, hire_date
FROM employees
WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';

LIKE — パターンマッチング

文字列の部分一致検索には LIKE を使います。2種類のワイルドカードがあります。

ワイルドカード	意味	例
`%`	0文字以上の任意の文字列	`'田%'` → 田で始まる
`_`	任意の1文字	`'田_太郎'` → 田X太郎

-- 名前が「田」で始まる社員
SELECT name, department
FROM employees
WHERE name LIKE '田%';

name     | department
---------|----------
田中太郎 | 営業部

-- 名前に「子」が含まれる社員
SELECT name, department
FROM employees
WHERE name LIKE '%子%';

name     | department
---------|----------
鈴木花子 | 開発部
小林恵子 | 人事部

-- 名前が3文字の社員（_を3つ使用）
SELECT name, department
FROM employees
WHERE name LIKE '___';

% と _ は組み合わせて使えます。LIKE '%田%' は「田」が名前のどこかに含まれるすべての行にマッチします。

IS NULL / IS NOT NULL

SQLでは「値がない」状態を NULL で表します。NULLは「0」や「空文字列」とは異なる特別な値です。

-- NULLの動作確認用にデータを追加
INSERT INTO employees (id, name, department, salary, hire_date)
VALUES (11, '中村未定', '未定', 300000, '2024-04-01');

-- 注意: NULL = NULL は TRUE にならない
SELECT 1 WHERE NULL = NULL;      -- 結果なし（FALSE扱い）
SELECT 1 WHERE NULL IS NULL;     -- 結果あり（TRUE）

NULLの比較には = ではなく、必ず IS NULL または IS NOT NULL を使います。

-- NULLを含むデータの確認（実務での典型的なパターン）
-- 例: メールアドレスが未登録の社員を検索
-- SELECT name FROM employees WHERE email IS NULL;

NULLの3値論理: SQLでは TRUE, FALSE, NULL の3値論理が使われます。NULL が含まれる比較の結果は NULL（不明）になります。これは初心者がハマりやすいポイントです。

条件の組み合わせ — 実務的な例

-- 実務例1: 2020年以降入社の開発部社員で、月給40万以上
SELECT name, department, salary, hire_date
FROM employees
WHERE department = '開発部'
  AND hire_date >= '2020-01-01'
  AND salary >= 400000;

-- 実務例2: 営業部の高給者 または 開発部の新人
SELECT name, department, salary, hire_date
FROM employees
WHERE (department = '営業部' AND salary >= 370000)
   OR (department = '開発部' AND hire_date >= '2020-01-01');

-- 実務例3: 名前に「田」を含み、月給35万〜40万の範囲
SELECT name, department, salary
FROM employees
WHERE name LIKE '%田%'
  AND salary BETWEEN 350000 AND 400000;

実践ワーク

以下のクエリを自分で考えて作成してください（解答例はその下にあります）：

人事部の社員を全列取得してください
月給が35万円未満の社員の名前と月給を取得してください
2020年より前に入社した社員を取得してください
営業部で、かつ2021年以降に入社した社員を取得してください
名前に「藤」が含まれる社員を取得してください

-- 解答例
-- 1.
SELECT * FROM employees WHERE department = '人事部';

-- 2.
SELECT name, salary FROM employees WHERE salary < 350000;

-- 3.
SELECT name, hire_date FROM employees WHERE hire_date < '2020-01-01';

-- 4.
SELECT name, department, hire_date
FROM employees
WHERE department = '営業部' AND hire_date >= '2021-01-01';

-- 5.
SELECT name, department FROM employees WHERE name LIKE '%藤%';

まとめと次回の準備

今回のポイント: - WHERE 句で条件を指定し、該当する行だけを取得する - 比較演算子: =, <>, <, >, <=, >= - AND（かつ）、OR（または）、NOT（否定）で条件を組み合わせる - IN で複数の値に対する一致を簡潔に書ける - BETWEEN A AND B で範囲指定（境界値を含む） - LIKE と %/_ でパターンマッチング（部分一致検索） - NULL の比較は IS NULL / IS NOT NULL を使う（= NULL は動作しない）

次回: ORDER BY句で結果を並べ替え、LIMITで件数制限する方法を学びます。ランキングの作成やページネーション（ページ送り）など、実務で頻出する操作をマスターします。

参考文献: - PostgreSQL公式ドキュメント「比較演算子」（https://www.postgresql.org/docs/current/functions-comparison.html） - PostgreSQL公式ドキュメント「パターンマッチング」（https://www.postgresql.org/docs/current/functions-matching.html） - SQLite公式ドキュメント「Expression」（https://www.sqlite.org/lang_expr.html） - W3Schools SQL WHERE（https://www.w3schools.com/sql/sql_where.asp）

Lecture 4ORDER BYとLIMIT — 並べ替えと件数制限

10:00

ORDER BYとLIMIT — 並べ替えと件数制限

なぜ並べ替えが必要か

前回までのクエリでは、データがテーブルに格納された順番で返されました。しかし実務では「月給が高い順」「入社日が新しい順」「名前の五十音順」など、特定の基準で並べ替えたいケースがほとんどです。

重要な事実として、リレーショナルデータベースは ORDER BYを指定しない限り、結果の順序を保証しません。同じクエリを2回実行しても、異なる順序で返る可能性があります。確定した順序が必要なら、必ずORDER BYを記述してください。

ORDER BY — 基本構文

SELECT 列名1, 列名2, ...
FROM テーブル名
WHERE 条件
ORDER BY 並べ替え列 [ASC | DESC];

ASC（Ascending）: 昇順（小さい → 大きい）。デフォルト値なので省略可能
DESC（Descending）: 降順（大きい → 小さい）

昇順（ASC） — デフォルト

-- 月給が低い順に表示
SELECT name, department, salary
FROM employees
ORDER BY salary ASC;

name       | department | salary
-----------|------------|-------
伊藤あゆみ | 営業部     | 320000
小林恵子   | 人事部     | 340000
田中太郎   | 営業部     | 350000
山田美咲   | 人事部     | 360000
加藤大輔   | 営業部     | 370000
佐藤一郎   | 営業部     | 380000
渡辺直人   | 開発部     | 400000
鈴木花子   | 開発部     | 420000
高橋健太   | 開発部     | 450000
吉田さくら | 開発部     | 480000

ASC は省略可能です。ORDER BY salary と書いても同じ結果になります。

降順（DESC）

-- 月給が高い順に表示
SELECT name, department, salary
FROM employees
ORDER BY salary DESC;

name       | department | salary
-----------|------------|-------
吉田さくら | 開発部     | 480000
高橋健太   | 開発部     | 450000
鈴木花子   | 開発部     | 420000
渡辺直人   | 開発部     | 400000
佐藤一郎   | 営業部     | 380000
...

文字列の並べ替え

-- 入社日が新しい順（日付の降順）
SELECT name, department, hire_date
FROM employees
ORDER BY hire_date DESC;

日付（YYYY-MM-DD 形式の文字列）も正しくソートされます。文字列は辞書順（アルファベット順・文字コード順）で並びます。

複数列での並べ替え

-- 部署名で昇順ソート → 同じ部署内では月給の降順
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

name       | department | salary
-----------|------------|-------
吉田さくら | 開発部     | 480000
高橋健太   | 開発部     | 450000
鈴木花子   | 開発部     | 420000
渡辺直人   | 開発部     | 400000
山田美咲   | 人事部     | 360000
小林恵子   | 人事部     | 340000
佐藤一郎   | 営業部     | 380000
加藤大輔   | 営業部     | 370000
田中太郎   | 営業部     | 350000
伊藤あゆみ | 営業部     | 320000

最初に department で昇順ソートされ、同じ部署内では salary の降順で並びます。複数列の並べ替えは、レポート作成やランキング表示で頻繁に使う技法です。

NULLの並べ替え位置

NULLが含まれる列をソートすると、NULLの位置はRDBMS製品によって異なります。

RDBMS	NULLの位置（ASC時）
PostgreSQL	最後（NULLS FIRST で変更可能）
MySQL	最初
SQLite	最初
SQL Server	最初

PostgreSQLでは明示的に制御できます：

-- PostgreSQL: NULLを最初に表示
SELECT name, salary
FROM employees
ORDER BY salary ASC NULLS FIRST;

-- PostgreSQL: NULLを最後に表示
SELECT name, salary
FROM employees
ORDER BY salary ASC NULLS LAST;

SQLiteやMySQLでは CASE 式を使って制御します（後述）。

LIMIT — 件数制限

データが大量にあるとき、最初の数件だけ取得したいケースがあります。LIMIT を使うと取得件数を制限できます。

-- 上位3名を取得（月給の高い順）
SELECT name, department, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

name       | department | salary
-----------|------------|-------
吉田さくら | 開発部     | 480000
高橋健太   | 開発部     | 450000
鈴木花子   | 開発部     | 420000

注意: LIMIT はSQLの国際標準（ISO）には含まれていません。PostgreSQL、MySQL、SQLiteでは使えますが、SQL Serverでは TOP、Oracleでは FETCH FIRST を使います。

-- SQL Server の場合
-- SELECT TOP 3 name, department, salary
-- FROM employees
-- ORDER BY salary DESC;

-- Oracle / 標準SQL の場合
-- SELECT name, department, salary
-- FROM employees
-- ORDER BY salary DESC
-- FETCH FIRST 3 ROWS ONLY;

OFFSET — ページネーション

OFFSET を LIMIT と組み合わせると、結果の一部をスキップできます。Webアプリケーションの「ページ送り」機能に使います。

-- 月給ランキング: 1ページ目（1〜3位）
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 0;

-- 月給ランキング: 2ページ目（4〜6位）
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 3;

-- 月給ランキング: 3ページ目（7〜9位）
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 6;

LIMIT 3 OFFSET 3 は「3件スキップして、次の3件を取得」という意味です。ページ番号を p（1始まり）、1ページあたりの件数を n とすると、公式は以下の通りです：

LIMIT n OFFSET (p - 1) * n

例えば3ページ目、1ページ5件なら：LIMIT 5 OFFSET 10

注意: OFFSETが大きくなるとパフォーマンスが低下します。大量データのページネーションでは「カーソルベースページネーション」（WHERE id > 前ページ最後のID）の方が効率的です。

TOP Nクエリ — 実務パターン

ランキングやダッシュボードでよく使うパターンです。

-- 月給トップ5
SELECT name, department, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

-- 最も古参の社員3名
SELECT name, department, hire_date
FROM employees
ORDER BY hire_date ASC
LIMIT 3;

-- 部署ごとの最高月給者を確認（WHERE + ORDER BY + LIMIT）
SELECT name, salary
FROM employees
WHERE department = '営業部'
ORDER BY salary DESC
LIMIT 1;

CASE式 — 条件付きロジック

CASE 式はSQLの中で条件分岐を行う機能です。プログラミング言語の if-else に相当します。

-- 月給レベルを判定
SELECT
    name,
    salary,
    CASE
        WHEN salary >= 450000 THEN 'A（高）'
        WHEN salary >= 380000 THEN 'B（中）'
        WHEN salary >= 340000 THEN 'C（標準）'
        ELSE 'D（低）'
    END AS 給与レベル
FROM employees
ORDER BY salary DESC;

name       | salary | 給与レベル
-----------|--------|----------
吉田さくら | 480000 | A（高）
高橋健太   | 450000 | A（高）
鈴木花子   | 420000 | B（中）
渡辺直人   | 400000 | B（中）
佐藤一郎   | 380000 | B（中）
加藤大輔   | 370000 | C（標準）
山田美咲   | 360000 | C（標準）
田中太郎   | 350000 | C（標準）
小林恵子   | 340000 | C（標準）
伊藤あゆみ | 320000 | D（低）

CASE式はORDER BYの中でも使えます。これにより、カスタムの並び順を実現できます：

-- 部署を「開発部→営業部→人事部」の順で表示（カスタムソート）
SELECT name, department, salary
FROM employees
ORDER BY
    CASE department
        WHEN '開発部' THEN 1
        WHEN '営業部' THEN 2
        WHEN '人事部' THEN 3
        ELSE 4
    END,
    salary DESC;

WHERE + ORDER BY + LIMIT の組み合わせ

3つの句を組み合わせた実践的なクエリです。

-- 営業部の月給トップ2
SELECT name, salary, hire_date
FROM employees
WHERE department = '営業部'
ORDER BY salary DESC
LIMIT 2;

-- 2020年以降入社で、月給が低い順に上位5名
SELECT name, department, salary, hire_date
FROM employees
WHERE hire_date >= '2020-01-01'
ORDER BY salary ASC
LIMIT 5;

-- 開発部の古参順（入社日が早い順）
SELECT name, hire_date, salary
FROM employees
WHERE department = '開発部'
ORDER BY hire_date ASC;

実践ワーク

以下のクエリを自分で作成してください：

全社員を入社日の新しい順に表示してください
月給が高い上位3名の名前・部署・月給を取得してください
営業部の社員を名前の昇順で表示してください
全社員の月給ランキング（2ページ目、1ページ3件）を取得してください
CASE式を使って、入社年が2020年以前なら「ベテラン」、それ以降なら「若手」と表示してください

-- 解答例
-- 1.
SELECT name, department, hire_date
FROM employees ORDER BY hire_date DESC;

-- 2.
SELECT name, department, salary
FROM employees ORDER BY salary DESC LIMIT 3;

-- 3.
SELECT name, salary, hire_date
FROM employees WHERE department = '営業部' ORDER BY name ASC;

-- 4.
SELECT name, salary
FROM employees ORDER BY salary DESC LIMIT 3 OFFSET 3;

-- 5.
SELECT
    name,
    hire_date,
    CASE
        WHEN hire_date <= '2020-12-31' THEN 'ベテラン'
        ELSE '若手'
    END AS 区分
FROM employees
ORDER BY hire_date ASC;

まとめと次回の準備

今回のポイント: - ORDER BY で結果を並べ替える。ASC（昇順）がデフォルト、DESC で降順 - 複数列の並べ替えは ORDER BY 列1 ASC, 列2 DESC のようにカンマ区切り - LIMIT n で取得件数を制限する（TOP Nクエリ） - LIMIT n OFFSET m でページネーションを実現（m件スキップして次のn件） - CASE 式でSQLの中に条件分岐を書ける（SELECT句でもORDER BY句でも使用可能） - ORDER BYがないSELECT文の結果順序は保証されない

次回: INSERT、UPDATE、DELETEでデータの追加・変更・削除を学びます。CREATE TABLEによるテーブル定義、データ型、制約、トランザクション（BEGIN/COMMIT/ROLLBACK）など、データ操作の安全な方法を身につけます。

参考文献: - PostgreSQL公式ドキュメント「ORDER BY」（https://www.postgresql.org/docs/current/sql-select.html#SQL-ORDERBY） - PostgreSQL公式ドキュメント「LIMIT and OFFSET」（https://www.postgresql.org/docs/current/sql-select.html#SQL-LIMIT） - PostgreSQL公式ドキュメント「条件式」（https://www.postgresql.org/docs/current/functions-conditional.html） - SQLite公式ドキュメント「SELECT - ORDER BY」（https://www.sqlite.org/lang_select.html#orderby） - W3Schools SQL ORDER BY（https://www.w3schools.com/sql/sql_orderby.asp）

Lecture 5INSERT・UPDATE・DELETE — データの追加・変更・削除

12:00

INSERT・UPDATE・DELETE — データの追加・変更・削除

SELECTとDMLの違い

ここまでの4回はすべて SELECT（データの読み取り）でした。今回はデータを変更する操作を学びます。SQLのデータ操作言語（DML: Data Manipulation Language）には以下の4つがあります：

命令	操作	危険度
SELECT	データの読み取り	低（データを変更しない）
INSERT	データの追加	中
UPDATE	データの変更	高
DELETE	データの削除	高

UPDATE と DELETE は使い方を誤ると、テーブル全体のデータを一瞬で破壊する可能性があります。この講義では安全な使い方を徹底的に学びます。

CREATE TABLE — テーブルの作成

DMLの前に、テーブルを自分で作成する方法を学びましょう。テーブル定義はDDL（Data Definition Language）に分類されます。

-- 商品テーブルの作成
CREATE TABLE products (
    id          INTEGER     PRIMARY KEY,
    name        TEXT        NOT NULL,
    category    TEXT        NOT NULL,
    price       INTEGER     NOT NULL DEFAULT 0,
    stock       INTEGER     NOT NULL DEFAULT 0,
    is_active   INTEGER     NOT NULL DEFAULT 1,
    created_at  TEXT        NOT NULL DEFAULT (datetime('now', 'localtime'))
);

主要なデータ型

データ型	説明	例
`INTEGER`	整数	1, 42, -100
`TEXT` / `VARCHAR(n)`	文字列	'田中太郎', 'Tokyo'
`REAL` / `NUMERIC`	浮動小数点 / 固定小数点	3.14, 99.99
`DATE`	日付	'2026-01-15'
`BOOLEAN`	真偽値	TRUE / FALSE（SQLiteではINTEGER 0/1）

注意: SQLiteではデータ型は柔軟に扱われます（型アフィニティ）。PostgreSQLやMySQLではより厳密です。

主要な制約

制約	説明
`PRIMARY KEY`	主キー。テーブル内で一意、NULLを許可しない
`NOT NULL`	NULLを許可しない
`DEFAULT 値`	値が指定されなかったときのデフォルト値
`UNIQUE`	テーブル内で一意（重複を許可しない）
`CHECK(条件)`	値が条件を満たすことを保証する

-- 制約の例
CREATE TABLE users (
    id       INTEGER PRIMARY KEY,
    email    TEXT    NOT NULL UNIQUE,
    age      INTEGER CHECK(age >= 0 AND age <= 150),
    status   TEXT    NOT NULL DEFAULT 'active'
);

INSERT INTO — データの追加

1行挿入

-- 全列を指定して挿入
INSERT INTO products (id, name, category, price, stock, is_active)
VALUES (1, 'ノートPC', '電子機器', 89800, 25, 1);

列名を明示的に指定することで、列の順番を気にせず安全に挿入できます。created_at はDEFAULT値（現在日時）が自動的に設定されます。

複数行を一度に挿入

-- 複数行を一括挿入
INSERT INTO products (id, name, category, price, stock, is_active) VALUES
(2,  'マウス',           '周辺機器', 2980,   100, 1),
(3,  'キーボード',       '周辺機器', 7980,   50,  1),
(4,  'モニター 27型',    '電子機器', 39800,  15,  1),
(5,  'USBケーブル',      'ケーブル', 580,    200, 1),
(6,  'HDMIケーブル',     'ケーブル', 1280,   150, 1),
(7,  'Webカメラ',        '周辺機器', 4980,   30,  1),
(8,  'ヘッドセット',     '周辺機器', 6980,   40,  1),
(9,  'SSD 1TB',          'ストレージ', 12800, 20, 1),
(10, 'モバイルバッテリー', '電子機器', 3980,  60,  1);

一括挿入は1行ずつ挿入するよりもパフォーマンスが良く、実務では頻繁に使います。

挿入結果の確認

-- 挿入したデータを確認
SELECT * FROM products ORDER BY id;

ベストプラクティス: INSERT後は必ずSELECTで結果を確認する習慣をつけましょう。

UPDATE — データの変更

基本構文

UPDATE テーブル名
SET 列名1 = 新しい値1, 列名2 = 新しい値2, ...
WHERE 条件;

特定の行を更新

-- マウスの価格を改定（2980 → 2480）
UPDATE products
SET price = 2480
WHERE id = 2;

-- 確認
SELECT * FROM products WHERE id = 2;

-- 複数列を同時に更新
UPDATE products
SET price = 3280, stock = 80
WHERE name = 'マウス';

WHERE句を使った条件付き更新

-- ケーブル類を一括で10%値下げ
UPDATE products
SET price = CAST(price * 0.9 AS INTEGER)
WHERE category = 'ケーブル';

-- 確認
SELECT name, category, price FROM products WHERE category = 'ケーブル';

-- 在庫が30個以下の商品を非アクティブにする
UPDATE products
SET is_active = 0
WHERE stock <= 30;

危険: WHERE句なしのUPDATE

-- !!!絶対にやってはいけない例!!!
-- WHERE句がないと、全行が更新される
-- UPDATE products SET price = 0;
-- ↑ 全商品の価格が0円になってしまう！

鉄則: UPDATE文を書くときは、まずWHERE句を確認するSELECT文を実行してから、UPDATE文に書き換える。

-- ステップ1: まずSELECTで対象を確認
SELECT * FROM products WHERE category = 'ケーブル';
-- → 2件が表示される。意図通りか確認

-- ステップ2: 確認できたらUPDATEに書き換える
UPDATE products
SET price = CAST(price * 0.9 AS INTEGER)
WHERE category = 'ケーブル';

この「SELECT → 確認 → UPDATE」のワークフローを必ず守ってください。

DELETE — データの削除

基本構文

DELETE FROM テーブル名
WHERE 条件;

特定の行を削除

-- id=10の商品を削除
DELETE FROM products
WHERE id = 10;

-- 確認
SELECT * FROM products ORDER BY id;

条件付き削除

-- 非アクティブな商品を削除
DELETE FROM products
WHERE is_active = 0;

危険: WHERE句なしのDELETE

-- !!!絶対にやってはいけない例!!!
-- WHERE句がないと、全行が削除される
-- DELETE FROM products;
-- ↑ 全商品が消えてしまう！

鉄則: DELETE文もUPDATE文と同様に、まずSELECTで対象を確認してから実行する。

-- ステップ1: 削除対象を確認
SELECT * FROM products WHERE is_active = 0;
-- → 削除される行を目視確認

-- ステップ2: 確認できたらDELETEに書き換え
DELETE FROM products WHERE is_active = 0;

TRUNCATE vs DELETE

テーブルの全行を削除する方法は2つあります。

方法	構文	特徴
DELETE	`DELETE FROM products;`	ログに記録、ROLLBACK可能、遅い
TRUNCATE	`TRUNCATE TABLE products;`	ログなし、ROLLBACK不可（製品による）、高速

注意: SQLiteにはTRUNCATEコマンドがありません。DELETE FROM テーブル名; で代用します。PostgreSQLとMySQLでは両方使えます。

トランザクション — 安全なデータ操作

トランザクションとは、複数のSQL文を「ひとまとまりの処理」として扱う仕組みです。「全部成功」か「全部取り消し」のどちらかを保証します。

基本的なトランザクション

-- トランザクション開始
BEGIN;

-- 処理1: 在庫を減らす
UPDATE products SET stock = stock - 1 WHERE id = 1;

-- 処理2: 注文テーブルに記録（仮の例）
-- INSERT INTO orders (product_id, quantity) VALUES (1, 1);

-- すべて成功したら確定
COMMIT;

ロールバック（取り消し）

-- トランザクション開始
BEGIN;

-- 間違った更新をしてしまった
UPDATE products SET price = 0 WHERE category = '電子機器';

-- まずい！取り消す！
ROLLBACK;

-- 確認: 価格は元のまま
SELECT name, price FROM products WHERE category = '電子機器';

ROLLBACK を実行すると、BEGIN 以降のすべての変更が取り消されます。これがトランザクションの最大の安全網です。

実務でのトランザクション活用

銀行の振り込みを例に考えましょう。「Aさんの口座から1万円引く」と「Bさんの口座に1万円足す」は、必ず両方成功するか、両方失敗する必要があります。片方だけ実行されると、お金が消失するか、無から生まれてしまいます。

BEGIN;

-- Aさんの口座から引く
UPDATE accounts SET balance = balance - 10000 WHERE name = 'Aさん';

-- Bさんの口座に足す
UPDATE accounts SET balance = balance + 10000 WHERE name = 'Bさん';

-- 両方成功したら確定
COMMIT;
-- もし途中でエラーが起きたら
-- ROLLBACK;

この「すべて成功 or すべて取り消し」をACID特性と呼びます： - Atomicity（原子性）: 処理は全部実行されるか、全部取り消される - Consistency（一貫性）: データは常に整合性のある状態を保つ - Isolation（分離性）: 同時実行されるトランザクションは互いに影響しない - Durability（永続性）: COMMITされたデータは永続的に保存される

安全なデータ操作のまとめ

実務でUPDATE/DELETEを実行する際のチェックリストです：

まずSELECTで対象を確認 — WHERE句が意図通りか確認する
BEGINでトランザクションを開始 — いつでもROLLBACKできるようにする
UPDATE/DELETEを実行 — 実行結果の行数を確認する
SELECTで結果を確認 — データが意図通りに変更されたか確認する
問題なければCOMMIT、問題があればROLLBACK

-- 安全な更新の実践例
BEGIN;

-- 対象確認
SELECT name, price FROM products WHERE category = '周辺機器';

-- 更新実行
UPDATE products
SET price = CAST(price * 1.05 AS INTEGER)
WHERE category = '周辺機器';

-- 結果確認
SELECT name, price FROM products WHERE category = '周辺機器';

-- 問題なければ確定
COMMIT;
-- 問題があれば取り消し
-- ROLLBACK;

実践ワーク

以下の操作を順番に実行してください。必ずトランザクションを使い、各ステップでSELECTによる確認を入れてください：

-- 課題1: productsテーブルに新商品を追加
BEGIN;
INSERT INTO products (id, name, category, price, stock, is_active)
VALUES (11, 'ワイヤレスマウス', '周辺機器', 4980, 45, 1);
SELECT * FROM products WHERE id = 11;
COMMIT;

-- 課題2: 全「周辺機器」カテゴリの在庫を5個ずつ増やす
BEGIN;
SELECT name, stock FROM products WHERE category = '周辺機器';
UPDATE products SET stock = stock + 5 WHERE category = '周辺機器';
SELECT name, stock FROM products WHERE category = '周辺機器';
COMMIT;

-- 課題3: 価格が1000円未満の商品を削除する
BEGIN;
SELECT * FROM products WHERE price < 1000;
DELETE FROM products WHERE price < 1000;
SELECT COUNT(*) AS 残り件数 FROM products;
COMMIT;

-- 課題4（チャレンジ）: 新しいテーブルを作成して練習
CREATE TABLE books (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    author TEXT NOT NULL,
    price  INTEGER NOT NULL CHECK(price > 0),
    genre  TEXT NOT NULL DEFAULT '未分類'
);

INSERT INTO books (id, title, author, price, genre) VALUES
(1, '入門SQL',       '山田太郎', 2800, '技術書'),
(2, 'データベース設計', '佐藤花子', 3200, '技術書'),
(3, 'SQL実践入門',    '鈴木一郎', 2500, '技術書');

SELECT * FROM books;

まとめと次回の準備

今回のポイント: - CREATE TABLE でテーブルを定義する。データ型（INTEGER, TEXT, REAL, DATE）と制約（PRIMARY KEY, NOT NULL, DEFAULT）を指定する - INSERT INTO でデータを追加する。複数行の一括挿入も可能 - UPDATE SET ... WHERE でデータを変更する。WHERE句の省略は全行更新になる - DELETE FROM ... WHERE でデータを削除する。WHERE句の省略は全行削除になる - UPDATE/DELETEの前に必ずSELECTで対象を確認するワークフローを徹底する - BEGIN / COMMIT / ROLLBACK でトランザクションを管理し、安全にデータ操作を行う - ACID特性がRDBMSのデータ整合性を保証する

今後の学習に向けて: この5回でSQLの基礎（SELECT、WHERE、ORDER BY、LIMIT、INSERT、UPDATE、DELETE）を習得しました。次のステップとして、GROUP BY（集計）、JOIN（テーブル結合）、サブクエリ、インデックス、ビューなどの中級トピックに進むことをおすすめします。

参考文献: - PostgreSQL公式ドキュメント「INSERT」（https://www.postgresql.org/docs/current/sql-insert.html） - PostgreSQL公式ドキュメント「UPDATE」（https://www.postgresql.org/docs/current/sql-update.html） - PostgreSQL公式ドキュメント「DELETE」（https://www.postgresql.org/docs/current/sql-delete.html） - PostgreSQL公式ドキュメント「トランザクション」（https://www.postgresql.org/docs/current/tutorial-transactions.html） - SQLite公式ドキュメント「CREATE TABLE」（https://www.sqlite.org/lang_createtable.html） - W3Schools SQL INSERT INTO（https://www.w3schools.com/sql/sql_insert.asp）

Lecture 6集計関数とGROUP BY — データを集計する

12:00

集計関数とGROUP BY — データを集計する

なぜ集計が重要なのか

データベースに何万行ものデータが格納されていても、生データをそのまま眺めても意味はありません。ビジネスで必要なのは「先月の売上合計はいくらか」「部署ごとの平均年収はいくらか」といった集計された情報です。Excelのピボットテーブルに相当する操作を、SQLではたった数行で実現できます。

この講義では、SQLの集計関数（COUNT, SUM, AVG, MIN, MAX）と、データをグループ化するGROUP BY句を学びます。これはデータ分析の現場で最も頻繁に使われるSQL構文です。

サンプルデータの準備

以下のテーブルを使って説明します。実際にSQL環境で作成して試してみてください：

CREATE TABLE sales (
    id         INTEGER PRIMARY KEY,
    product    VARCHAR(50) NOT NULL,
    category   VARCHAR(30) NOT NULL,
    amount     INTEGER NOT NULL,
    quantity   INTEGER NOT NULL,
    sale_date  DATE NOT NULL,
    region     VARCHAR(20)
);

INSERT INTO sales (id, product, category, amount, quantity, sale_date, region) VALUES
(1,  'ノートPC',     '電子機器', 89000,  2, '2025-01-15', '東京'),
(2,  'マウス',       '電子機器', 2500,   5, '2025-01-20', '大阪'),
(3,  'デスク',       '家具',     35000,  1, '2025-01-22', '東京'),
(4,  'チェア',       '家具',     28000,  3, '2025-02-03', '名古屋'),
(5,  'キーボード',   '電子機器', 8000,   4, '2025-02-10', '東京'),
(6,  'モニター',     '電子機器', 45000,  2, '2025-02-15', '大阪'),
(7,  'ペン',         '文具',     300,   20, '2025-03-01', NULL),
(8,  'ノート',       '文具',     500,   15, '2025-03-05', '東京'),
(9,  'ノートPC',     '電子機器', 89000,  1, '2025-03-10', '名古屋'),
(10, 'デスクライト', '家具',     12000,  2, '2025-03-15', '大阪');

5つの集計関数

SQLには5つの基本的な集計関数があります。それぞれの役割を確認しましょう：

-- COUNT: 行数を数える
SELECT COUNT(*) AS 全件数 FROM sales;
-- 結果: 10

-- SUM: 合計を計算する
SELECT SUM(amount * quantity) AS 売上合計 FROM sales;

-- AVG: 平均を計算する
SELECT AVG(amount) AS 平均単価 FROM sales;

-- MIN: 最小値を取得する
SELECT MIN(amount) AS 最安値 FROM sales;

-- MAX: 最大値を取得する
SELECT MAX(amount) AS 最高値 FROM sales;

複数の集計関数を1つのSELECTで同時に使うことも可能です：

SELECT
    COUNT(*)       AS 件数,
    SUM(amount * quantity) AS 売上合計,
    AVG(amount)    AS 平均単価,
    MIN(amount)    AS 最安値,
    MAX(amount)    AS 最高値
FROM sales;

COUNTの2つの使い方

COUNT(*)とCOUNT(列名)は動作が異なります。これは実務で頻出の知識です：

-- COUNT(*): NULLを含めてすべての行を数える
SELECT COUNT(*) AS 全件数 FROM sales;
-- 結果: 10

-- COUNT(region): NULLを除いた行数を数える
SELECT COUNT(region) AS 地域あり件数 FROM sales;
-- 結果: 9（regionがNULLの1行を除外）

-- COUNT(DISTINCT category): 重複を除いたユニーク値の数
SELECT COUNT(DISTINCT category) AS カテゴリ数 FROM sales;
-- 結果: 3（電子機器, 家具, 文具）

NULLと集計関数の関係

集計関数はNULLの扱いに注意が必要です。COUNT(*)以外の集計関数はNULL値を無視します：

-- regionカラムにはNULLが1件ある
SELECT
    COUNT(*)       AS 全行数,      -- 10（NULLも含む）
    COUNT(region)  AS 地域あり,    -- 9（NULLを除外）
    COUNT(*) - COUNT(region) AS 地域なし  -- 1
FROM sales;

NULLを特定の値に置き換えたい場合はCOALESCE関数を使います：

-- NULLの地域を「未設定」に置き換える
SELECT
    COALESCE(region, '未設定') AS 地域,
    COUNT(*) AS 件数
FROM sales
GROUP BY COALESCE(region, '未設定');

COALESCEは引数を左から順に評価し、最初のNULLでない値を返します。複数の候補を指定することも可能です：COALESCE(region, area, '不明')のように。

GROUP BY — グループごとの集計

GROUP BYはデータを指定した列の値でグループ化し、各グループに対して集計関数を適用します。これがSQLの集計機能の核心です：

-- カテゴリ別の売上件数と売上合計
SELECT
    category AS カテゴリ,
    COUNT(*) AS 件数,
    SUM(amount * quantity) AS 売上合計
FROM sales
GROUP BY category;

結果：

カテゴリ | 件数 | 売上合計
---------|------|----------
電子機器 |    5 |  370500
家具     |    3 |  143000
文具     |    2 |   13500

複数カラムでのGROUP BY

複数の列でグループ化すると、より詳細な集計ができます：

-- カテゴリ × 地域 のクロス集計
SELECT
    category AS カテゴリ,
    region   AS 地域,
    COUNT(*) AS 件数,
    SUM(amount * quantity) AS 売上合計
FROM sales
WHERE region IS NOT NULL
GROUP BY category, region
ORDER BY category, 売上合計 DESC;

HAVING — グループに対する条件

WHERE句は「個々の行」に対する条件、HAVING句は「グループ化した結果」に対する条件です。この違いは面接でもよく聞かれるポイントです：

-- 売上合計が10万円以上のカテゴリだけ抽出
SELECT
    category AS カテゴリ,
    SUM(amount * quantity) AS 売上合計
FROM sales
GROUP BY category
HAVING SUM(amount * quantity) >= 100000;

WHEREとHAVINGの使い分け

-- WHERE: GROUP BY の前に行をフィルタリング
-- HAVING: GROUP BY の後にグループをフィルタリング

-- 2025年2月以降の売上で、カテゴリ別に合計5万円以上
SELECT
    category,
    SUM(amount * quantity) AS total
FROM sales
WHERE sale_date >= '2025-02-01'    -- 行レベルの条件（WHERE）
GROUP BY category
HAVING SUM(amount * quantity) >= 50000;  -- グループレベルの条件（HAVING）

SQL文の実行順序を理解すると、WHEREとHAVINGの違いが明確になります：

1. FROM    → テーブルを指定
2. WHERE   → 行を絞り込み
3. GROUP BY → グループ化
4. HAVING  → グループを絞り込み
5. SELECT  → 表示する列を決定
6. ORDER BY → 並び替え

実務で使える集計クエリ

月別売上レポート

-- 月別の売上推移
SELECT
    DATE_FORMAT(sale_date, '%Y-%m') AS 月,
    COUNT(*)                        AS 取引件数,
    SUM(amount * quantity)          AS 売上合計,
    AVG(amount * quantity)          AS 平均取引額,
    MAX(amount * quantity)          AS 最大取引額
FROM sales
GROUP BY DATE_FORMAT(sale_date, '%Y-%m')
ORDER BY 月;

補足: DATE_FORMATはMySQL固有の関数です。PostgreSQLではTO_CHAR(sale_date, 'YYYY-MM')、SQLiteではstrftime('%Y-%m', sale_date)を使います。

地域別の販売実績

-- 地域ごとの売上と平均単価（売上上位順）
SELECT
    COALESCE(region, '未設定') AS 地域,
    COUNT(*)                   AS 取引件数,
    SUM(quantity)              AS 総販売数,
    SUM(amount * quantity)     AS 売上合計,
    ROUND(AVG(amount), 0)     AS 平均単価
FROM sales
GROUP BY region
HAVING COUNT(*) >= 2
ORDER BY 売上合計 DESC;

実践ワーク

以下の課題に取り組んでみましょう。すべてsalesテーブルに対するクエリです：

基本: 全商品の売上合計と平均単価を求めてください
GROUP BY: カテゴリ別に「件数」「売上合計」「平均単価」を集計してください
HAVING: 件数が3件以上のカテゴリだけを表示してください
応用: 月別・カテゴリ別のクロス集計を作成し、売上合計の降順で並べてください
COALESCE: 地域がNULLのデータを「オンライン」に置き換えて、地域別の件数を集計してください

まとめと次回の準備

今回のポイント: - 5つの集計関数: COUNT, SUM, AVG, MIN, MAX - COUNT(*)はNULL含む全行、COUNT(列名)はNULLを除外 - GROUP BYでグループ化、複数カラムでクロス集計 - WHEREは行の条件、HAVINGはグループの条件 - COALESCEでNULLを任意の値に置換 - SQL実行順序: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

次回: テーブル結合（JOIN）を学びます。1つのテーブルだけでは表現できない複雑なデータ構造を、複数のテーブルを「つなげる」ことで実現します。正規化されたデータベースを扱うための必須スキルです。

参考文献: - PostgreSQL公式ドキュメント「集約関数」（https://www.postgresql.org/docs/current/functions-aggregate.html） - MySQL公式リファレンス「GROUP BY」（https://dev.mysql.com/doc/refman/8.0/ja/group-by-modifiers.html） - ミック『SQL 第2版ゼロからはじめるデータベース操作』（翔泳社、2016年） - W3Schools SQL Tutorial「SQL GROUP BY」（https://www.w3schools.com/sql/sql_groupby.asp）

Lecture 7テーブル結合（JOIN） — 複数テーブルをつなげる

12:00

テーブル結合（JOIN） — 複数テーブルをつなげる

なぜJOINが必要なのか — 正規化という設計思想

実際の業務データベースでは、すべての情報を1つのテーブルに詰め込むことはしません。例えば、注文データに毎回顧客の住所や電話番号をコピーすると、住所変更時にすべての注文レコードを更新しなければなりません。これはデータの不整合（矛盾）を生む原因です。

そこで、データを「顧客テーブル」「商品テーブル」「注文テーブル」のように分割して管理します。これを正規化と呼びます。分割したテーブルを必要に応じて「つなげる」操作がJOIN（結合）です。

JOINはSQLの中で最も重要な概念の一つであり、実務で書くSQLの大半がJOINを含みます。

サンプルテーブルの準備

ECサイトを想定した3つのテーブルを作成します：

-- 顧客テーブル
CREATE TABLE customers (
    customer_id   INTEGER PRIMARY KEY,
    name          VARCHAR(50) NOT NULL,
    email         VARCHAR(100),
    city          VARCHAR(30)
);

INSERT INTO customers VALUES
(1, '田中太郎',   'tanaka@example.com',  '東京'),
(2, '鈴木花子',   'suzuki@example.com',  '大阪'),
(3, '佐藤一郎',   'sato@example.com',    '名古屋'),
(4, '高橋美咲',   'takahashi@example.com','福岡'),
(5, '伊藤健太',   NULL,                   '札幌');

-- 商品テーブル
CREATE TABLE products (
    product_id    INTEGER PRIMARY KEY,
    product_name  VARCHAR(100) NOT NULL,
    price         INTEGER NOT NULL,
    category      VARCHAR(30)
);

INSERT INTO products VALUES
(101, 'ノートPC',     98000,  '電子機器'),
(102, 'ワイヤレスマウス', 3500, '電子機器'),
(103, 'オフィスチェア',  45000, '家具'),
(104, 'USBハブ',       2800,  '電子機器'),
(105, 'デスクライト',   8500,  '家具');

-- 注文テーブル
CREATE TABLE orders (
    order_id      INTEGER PRIMARY KEY,
    customer_id   INTEGER,
    product_id    INTEGER,
    quantity      INTEGER NOT NULL,
    order_date    DATE NOT NULL
);

INSERT INTO orders VALUES
(1001, 1, 101, 1, '2025-01-10'),
(1002, 1, 102, 2, '2025-01-15'),
(1003, 2, 103, 1, '2025-02-01'),
(1004, 3, 101, 1, '2025-02-10'),
(1005, 3, 104, 3, '2025-02-15'),
(1006, 2, 105, 2, '2025-03-01'),
(1007, 1, 103, 1, '2025-03-05'),
(1008, NULL, 102, 1, '2025-03-10');

注目点として、顧客ID=4（高橋美咲）と顧客ID=5（伊藤健太）は注文がなく、注文ID=1008はcustomer_idがNULLです。この「一致しないデータ」がJOINの種類による差を理解する鍵になります。

INNER JOIN — 一致するデータだけ

INNER JOINは両方のテーブルに一致するデータがある行だけを返します。最も基本的なJOINです：

-- 注文に顧客名を結合
SELECT
    o.order_id,
    c.name       AS 顧客名,
    o.order_date AS 注文日
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

テキストベースのベン図で表すと：

  Customers        Orders
  ┌───────┐     ┌───────┐
  │       │█████│       │
  │       │█████│       │  ← INNER JOIN: 重なる部分だけ
  │       │█████│       │
  └───────┘     └───────┘
  高橋,伊藤       order 1008
  は除外           は除外

結果: 7行（customer_id=4,5の顧客と、customer_idがNULLの注文1008は除外）

テーブル別名（エイリアス）

orders oやcustomers cのように短い別名を付けることで、SQLが格段に読みやすくなります。特に3つ以上のテーブルを結合するときに効果を発揮します。

LEFT JOIN — 左テーブルを基準に

LEFT JOINは左側のテーブルの全行を保持し、右側に一致しない場合はNULLで埋めます：

-- 全顧客と、もしあれば注文情報を結合
SELECT
    c.name        AS 顧客名,
    o.order_id,
    o.order_date  AS 注文日
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

  Customers        Orders
  ┌───────┐     ┌───────┐
  │███████│█████│       │
  │███████│█████│       │  ← LEFT JOIN: 左テーブル全部 + 一致する右側
  │███████│█████│       │
  └───────┘     └───────┘
  全顧客を保持     order 1008
                   は除外

結果に高橋美咲と伊藤健太も含まれます（order_id, order_dateはNULL）。これにより「注文したことがない顧客」を発見できます：

-- 注文がない顧客を探す（LEFT JOINの典型パターン）
SELECT c.name AS 未注文顧客
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
-- 結果: 高橋美咲, 伊藤健太

RIGHT JOIN — 右テーブルを基準に

RIGHT JOINはLEFT JOINの逆で、右側テーブルの全行を保持します：

-- 全注文と、もしあれば顧客情報を結合
SELECT
    o.order_id,
    c.name       AS 顧客名,
    o.order_date
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

  Customers        Orders
  ┌───────┐     ┌───────┐
  │       │█████│███████│
  │       │█████│███████│  ← RIGHT JOIN: 一致する左側 + 右テーブル全部
  │       │█████│███████│
  └───────┘     └───────┘
  高橋,伊藤       全注文を保持
  は除外          (1008も含む)

実務ではRIGHT JOINよりLEFT JOINが圧倒的に多く使われます。テーブルの順序を入れ替えればLEFT JOINで同じ結果を得られるためです。

FULL OUTER JOIN — 両方を保持

FULL OUTER JOINは両方のテーブルの全行を保持し、一致しない部分はNULLで埋めます：

SELECT
    c.name       AS 顧客名,
    o.order_id,
    o.order_date
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

  Customers        Orders
  ┌───────┐     ┌───────┐
  │███████│█████│███████│
  │███████│█████│███████│  ← FULL OUTER JOIN: 両方全部
  │███████│█████│███████│
  └───────┘     └───────┘
  高橋,伊藤も     order 1008も
  含む             含む

注意: MySQLはFULL OUTER JOINを直接サポートしていません。LEFT JOINとRIGHT JOINをUNIONで組み合わせて代用します。

CROSS JOIN — 全組み合わせ

CROSS JOINは2つのテーブルのすべての組み合わせ（直積）を返します。5行 × 5行 = 25行になります：

-- サイズとカラーの全組み合わせ（商品バリエーション生成に便利）
SELECT s.size_name, c.color_name
FROM (SELECT 'S' AS size_name UNION SELECT 'M' UNION SELECT 'L') s
CROSS JOIN (SELECT '赤' AS color_name UNION SELECT '青' UNION SELECT '白') c;
-- 結果: 9行（3サイズ × 3カラー）

自己結合（Self Join）

同じテーブルを自分自身と結合する手法です。階層構造のデータ（社員と上司の関係など）で活躍します：

CREATE TABLE employees (
    emp_id     INTEGER PRIMARY KEY,
    name       VARCHAR(50),
    manager_id INTEGER
);

INSERT INTO employees VALUES
(1, '山田社長', NULL),
(2, '田中部長', 1),
(3, '鈴木課長', 2),
(4, '佐藤主任', 3);

-- 社員と上司の名前を同時に表示
SELECT
    e.name  AS 社員名,
    m.name  AS 上司名
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

複数テーブルの結合

実務では3つ以上のテーブルを結合することが日常的です。注文テーブルを起点に、顧客名と商品名を同時に取得しましょう：

-- 注文明細: 顧客名・商品名・金額を一覧表示
SELECT
    o.order_id                      AS 注文ID,
    c.name                          AS 顧客名,
    p.product_name                  AS 商品名,
    p.price                         AS 単価,
    o.quantity                      AS 数量,
    p.price * o.quantity            AS 小計,
    o.order_date                    AS 注文日
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN products p  ON o.product_id  = p.product_id
ORDER BY o.order_date;

3テーブル結合のポイントは、結合条件（ON句）を正確に書くことです。間違えるとCROSS JOINのように大量の行が生成されてしまいます。

結合 + 集計の組み合わせ

JOINと前回学んだGROUP BYを組み合わせると、強力なレポートを作成できます：

-- 顧客別の購入金額ランキング
SELECT
    c.name                          AS 顧客名,
    COUNT(o.order_id)               AS 注文回数,
    SUM(p.price * o.quantity)       AS 購入総額
FROM customers c
INNER JOIN orders o   ON c.customer_id = o.customer_id
INNER JOIN products p ON o.product_id  = p.product_id
GROUP BY c.customer_id, c.name
ORDER BY 購入総額 DESC;

JOINの種類まとめ

JOIN種類	返す行	主な用途
INNER JOIN	両方に一致する行のみ	関連データの結合
LEFT JOIN	左テーブルの全行 + 一致する右	「存在しない」データの発見
RIGHT JOIN	右テーブルの全行 + 一致する左	LEFT JOINで代用可能
FULL OUTER JOIN	両方の全行	完全なデータ照合
CROSS JOIN	全組み合わせ（直積）	組み合わせ生成
Self JOIN	同一テーブル同士	階層・比較

実践ワーク

上記のサンプルテーブル（customers, products, orders）を使って以下の課題に取り組んでください：

INNER JOIN: 全注文について「顧客名」「商品名」「注文日」を表示してください
LEFT JOIN: 注文がない顧客の一覧を取得してください
集計+JOIN: カテゴリ別の売上合計を算出し、売上順で表示してください
複数JOIN: 顧客の都市別に、購入件数と購入総額を集計してください
応用: 2025年2月以降の注文について、顧客別の購入商品一覧を表示してください（注文日の新しい順）

まとめと次回の準備

今回のポイント: - JOINは正規化されたテーブルを「つなげる」操作 - INNER JOIN: 一致するデータだけ取得（最もよく使う） - LEFT JOIN: 左テーブルの全行を保持（一致しない部分はNULL） - テーブル別名（エイリアス）で可読性を向上 - 3つ以上のテーブル結合も基本は同じ（ON句を正確に） - JOIN + GROUP BYで実務レベルのレポートが作成可能

次回: サブクエリとCTE（Common Table Expression）を学びます。「クエリの中にクエリを書く」ことで、1つのSQLでは表現できなかった複雑な集計や条件指定が可能になります。

参考文献: - PostgreSQL公式ドキュメント「テーブル式」（https://www.postgresql.org/docs/current/queries-table-expressions.html） - MySQL公式リファレンス「JOIN構文」（https://dev.mysql.com/doc/refman/8.0/ja/join.html） - ミック『SQL 第2版ゼロからはじめるデータベース操作』（翔泳社、2016年） - Visual Representation of SQL Joins（https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/）

Lecture 8サブクエリとCTE — クエリの中にクエリを書く

12:00

サブクエリとCTE — クエリの中にクエリを書く

サブクエリとは何か

SQLでは、SELECT文の中に別のSELECT文を埋め込むことができます。この内側のクエリをサブクエリ（副問い合わせ）と呼びます。「全商品の平均価格より高い商品を抽出する」のように、ある集計結果を条件として使いたい場面で威力を発揮します。

サブクエリは大きく分けて4種類あります。それぞれの特徴と使い方を実践的に学んでいきましょう。

前回までに作成したcustomers、products、ordersテーブルを引き続き使用します。

スカラサブクエリ — 1つの値を返す

スカラサブクエリは1行1列、つまり単一の値を返すサブクエリです。比較演算子の右辺に使えます：

-- 平均価格より高い商品を抽出
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

内側のSELECT AVG(price) FROM productsが平均価格（31560）を返し、外側のWHEREがその値と比較します。サブクエリを使わない場合、まずAVGを調べて、その結果を手動でWHERE句に書く必要がありますが、サブクエリなら一発で完結します。

SELECT句でも使えます：

-- 各商品の価格と、全体平均との差を表示
SELECT
    product_name,
    price,
    price - (SELECT AVG(price) FROM products) AS 平均との差額
FROM products;

INサブクエリ — 値リストとの照合

INサブクエリは複数行を返し、その値リストに含まれるかどうかを判定します：

-- 注文されたことがある商品だけ抽出
SELECT product_name, price
FROM products
WHERE product_id IN (SELECT DISTINCT product_id FROM orders);

-- 注文されたことがない商品（NOT IN）
SELECT product_name, price
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id FROM orders
    WHERE product_id IS NOT NULL
);

重要: NOT INを使うとき、サブクエリの結果にNULLが含まれると全体がNULLになり、1行も返りません。WHERE product_id IS NOT NULLを付けるか、後述のNOT EXISTSを使うのが安全です。

EXISTSサブクエリ — 存在チェック

EXISTSは「条件に合う行が存在するかどうか」だけを判定します。値そのものは使いません：

-- 注文が1件以上ある顧客を抽出
SELECT c.name, c.city
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

SELECT 1の1は何でも構いません。EXISTSは「行が存在するか」だけを見るため、何を返すかは無関係です。

INとEXISTSの使い分け

状況	推奨	理由
サブクエリ結果が少量	IN	シンプルで読みやすい
サブクエリ結果が大量	EXISTS	インデックスを活用しやすい
NULLが含まれうる	EXISTS	NOT INのNULL問題を回避

実務では、データ量が少ないうちはINで十分です。パフォーマンスが問題になったらEXISTSへの書き換えを検討しましょう。

相関サブクエリ — 外側を参照する

相関サブクエリは、外側のクエリの値をサブクエリ内で参照します。外側の行ごとにサブクエリが実行されるイメージです：

-- 各顧客の最新注文を取得
SELECT
    c.name,
    o.order_id,
    o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date = (
    SELECT MAX(o2.order_date)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id  -- 外側の o.customer_id を参照
);

このクエリは、外側のordersテーブルの各行について「同じcustomer_idの中で最新のorder_date」をサブクエリで求め、一致する行だけを返します。

CTE（Common Table Expression） — WITH句で名前を付ける

CTEはWITH句を使ってサブクエリに名前を付ける構文です。長いサブクエリの可読性を劇的に改善します：

-- サブクエリ版（読みにくい）
SELECT *
FROM (
    SELECT
        customer_id,
        SUM(quantity) AS total_qty
    FROM orders
    GROUP BY customer_id
) sub
WHERE sub.total_qty >= 3;

-- CTE版（読みやすい）
WITH customer_orders AS (
    SELECT
        customer_id,
        SUM(quantity) AS total_qty
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM customer_orders
WHERE total_qty >= 3;

CTEの利点は読みやすさだけではありません。同じCTEを1つのクエリ内で複数回参照できます：

WITH order_summary AS (
    SELECT
        customer_id,
        COUNT(*)           AS order_count,
        SUM(p.price * o.quantity) AS total_spent
    FROM orders o
    INNER JOIN products p ON o.product_id = p.product_id
    WHERE o.customer_id IS NOT NULL
    GROUP BY customer_id
)
SELECT
    c.name,
    os.order_count,
    os.total_spent,
    ROUND(os.total_spent * 1.0 / os.order_count, 0) AS 平均注文額
FROM order_summary os
INNER JOIN customers c ON os.customer_id = c.customer_id
ORDER BY os.total_spent DESC;

複数CTEの連結

CTEはカンマで区切って複数定義できます。後のCTEは前のCTEを参照可能です：

WITH
-- CTE1: 顧客別注文集計
customer_stats AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(quantity) AS total_qty
    FROM orders
    WHERE customer_id IS NOT NULL
    GROUP BY customer_id
),
-- CTE2: 全体平均（CTE1を参照）
avg_stats AS (
    SELECT
        AVG(order_count) AS avg_orders,
        AVG(total_qty)   AS avg_qty
    FROM customer_stats
)
-- メインクエリ: 平均以上の顧客を抽出
SELECT
    c.name,
    cs.order_count,
    cs.total_qty
FROM customer_stats cs
INNER JOIN customers c ON cs.customer_id = c.customer_id
CROSS JOIN avg_stats a
WHERE cs.order_count >= a.avg_orders;

再帰CTE — 階層データの探索

再帰CTEは自分自身を参照するCTEで、組織図やカテゴリツリーのような階層データを扱うのに使います：

-- 組織階層を再帰CTEで展開
WITH RECURSIVE org_tree AS (
    -- 基底ケース: 最上位（社長）
    SELECT emp_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 再帰ケース: 部下を展開
    SELECT e.emp_id, e.name, e.manager_id, t.level + 1
    FROM employees e
    INNER JOIN org_tree t ON e.manager_id = t.emp_id
)
SELECT
    REPEAT('  ', level - 1) || name AS 組織図,
    level AS 階層
FROM org_tree
ORDER BY level, emp_id;

補足: MySQLではRECURSIVEキーワードが必要です。PostgreSQLでも同じ構文が使えます。SQLiteはバージョン3.8.3以降対応しています。

ウィンドウ関数入門 — 集計しながら行を保持

通常の集計関数（SUM, COUNTなど）はGROUP BYで行を畳みますが、ウィンドウ関数は集計結果を各行に付加しつつ、元の行を保持します：

-- ROW_NUMBER: 注文に連番を振る
SELECT
    order_id,
    customer_id,
    order_date,
    ROW_NUMBER() OVER (ORDER BY order_date) AS 通番
FROM orders;

-- RANK: 商品の価格ランキング
SELECT
    product_name,
    price,
    RANK() OVER (ORDER BY price DESC) AS 価格順位
FROM products;

-- SUM OVER: 累積売上（Running Total）
SELECT
    order_id,
    order_date,
    p.price * o.quantity AS 売上,
    SUM(p.price * o.quantity) OVER (ORDER BY o.order_date) AS 累積売上
FROM orders o
INNER JOIN products p ON o.product_id = p.product_id
ORDER BY o.order_date;

PARTITION BYで分割

PARTITION BYを使うと、グループごとにウィンドウ関数を適用できます：

-- 顧客ごとに注文の連番を振る
SELECT
    c.name,
    o.order_date,
    p.product_name,
    ROW_NUMBER() OVER (
        PARTITION BY o.customer_id
        ORDER BY o.order_date
    ) AS 顧客内注文番号
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN products p  ON o.product_id  = p.product_id;

ウィンドウ関数は次回以降のレクチャーでもたびたび登場するので、関数名() OVER (ORDER BY ...)の基本構文を覚えておいてください。

サブクエリ vs CTE — 使い分けの指針

観点	サブクエリ	CTE
可読性	ネストが深いと読みにくい	上から下へ読める
再利用	毎回書く必要あり	同一クエリ内で複数回参照可
再帰	不可	再帰CTEで階層データ対応
パフォーマンス	DBによっては最適化されやすい	DBによっては実体化される
推奨場面	単純な1回きりの条件	複雑な多段集計

実務では、短いサブクエリはそのまま使い、複雑になったらCTEに書き換えるのがバランスの良いアプローチです。

実践ワーク

以下の課題に取り組んでみましょう：

スカラサブクエリ: 全商品の平均価格を超える商品の名前と価格を表示してください
INサブクエリ: 一度も注文されていない商品の一覧を取得してください
EXISTS: 2025年3月に注文がある顧客の名前を表示してください
CTE: 顧客別の注文回数と購入総額を集計し、購入総額でランキングをつけてください
ウィンドウ関数: 商品カテゴリごとの価格ランキングを、RANK()を使って表示してください

まとめと次回の準備

今回のポイント: - スカラサブクエリ: 1つの値を返し、比較演算に使う - IN/EXISTS: 値の存在チェック。NULLにはEXISTSが安全 - 相関サブクエリ: 外側のクエリの値を内側で参照 - CTE（WITH句）: サブクエリに名前を付けて可読性を向上 - 再帰CTE: 階層データの探索 - ウィンドウ関数: 行を保持したまま集計（ROW_NUMBER, RANK, SUM OVER）

次回: テーブル設計の基本（正規化とER図）を学びます。ここまでは「データの取得」が中心でしたが、次回は「どうテーブルを設計するか」という視点でデータベースの構造を考えます。

参考文献: - PostgreSQL公式ドキュメント「WITH句」（https://www.postgresql.org/docs/current/queries-with.html） - PostgreSQL公式ドキュメント「ウィンドウ関数」（https://www.postgresql.org/docs/current/tutorial-window.html） - MySQL公式リファレンス「WITH（CTE）」（https://dev.mysql.com/doc/refman/8.0/ja/with.html） - ミック『達人に学ぶSQL徹底指南書第2版』（翔泳社、2018年）

Lecture 9テーブル設計の基本 — 正規化とER図

12:00

テーブル設計の基本 — 正規化とER図

なぜテーブル設計が重要なのか

ここまでの講義では、既存のテーブルからデータを取得する方法を学んできました。しかし実務では「テーブルをどう設計するか」が、その後のすべてのSQL操作の難易度を決定します。

設計が悪いと何が起きるか。例えば、顧客情報と注文情報を1つのテーブルに入れた場合：

注文ID | 顧客名   | 顧客住所     | 商品名   | 単価  | 数量
-------|---------|-------------|---------|------|-----
1001   | 田中太郎 | 東京都渋谷区  | ノートPC | 98000 | 1
1002   | 田中太郎 | 東京都渋谷区  | マウス   | 3500  | 2
1003   | 鈴木花子 | 大阪市北区   | チェア   | 45000 | 1

この設計には3つの深刻な問題があります：

更新異常: 田中太郎が引っ越したら、彼のすべての注文行を更新する必要がある
挿入異常: 注文がない新規顧客を登録できない（注文IDがNULLになる）
削除異常: 鈴木花子の注文を削除すると、鈴木花子の顧客情報も消える

これらの問題を解決するのが正規化（Normalization）です。

第1正規形（1NF） — 繰り返しの排除

第1正規形のルールは「各セルに1つの値だけを入れる」ことです。以下は1NF違反の例です：

-- 1NF違反: 1つのセルに複数の値
社員ID | 名前   | 電話番号
------|--------|------------------------
1     | 田中   | 090-1111-2222, 03-3333-4444
2     | 鈴木   | 080-5555-6666

1NFに修正する方法は2つあります：

-- 方法1: 列を分ける（電話番号の数が固定なら）
CREATE TABLE employees_v1 (
    emp_id   INTEGER PRIMARY KEY,
    name     VARCHAR(50),
    phone_mobile VARCHAR(15),
    phone_office VARCHAR(15)
);

-- 方法2: 行を分ける（電話番号の数が可変なら、こちらが推奨）
CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY,
    name   VARCHAR(50)
);

CREATE TABLE employee_phones (
    phone_id INTEGER PRIMARY KEY,
    emp_id   INTEGER REFERENCES employees(emp_id),
    phone_type VARCHAR(10),  -- 'mobile', 'office'
    phone_number VARCHAR(15)
);

方法2のように別テーブルに分離する設計が、拡張性の面で優れています。

第2正規形（2NF） — 部分関数従属の排除

2NFは「複合主キーの一部にだけ依存するカラムを排除する」ことです。以下は2NF違反の例です：

-- 2NF違反: 商品名は商品IDだけで決まる（注文IDは不要）
注文ID | 商品ID | 商品名     | 数量
------|--------|-----------|-----
1001  | 101    | ノートPC   | 1
1002  | 102    | マウス     | 2

主キーが（注文ID, 商品ID）の複合キーの場合、「商品名」は商品IDだけで決まります。これを部分関数従属と呼び、2NFでは排除すべきとされます：

-- 2NFに修正: テーブルを分離
CREATE TABLE products (
    product_id   INTEGER PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price        INTEGER NOT NULL
);

CREATE TABLE order_items (
    order_id   INTEGER,
    product_id INTEGER REFERENCES products(product_id),
    quantity   INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

第3正規形（3NF） — 推移的関数従属の排除

3NFは「主キー以外のカラム間の依存を排除する」ことです：

-- 3NF違反: 部署名は部署IDで決まる（社員IDではない）
社員ID | 社員名 | 部署ID | 部署名
------|--------|--------|--------
1     | 田中   | D01    | 営業部
2     | 鈴木   | D01    | 営業部
3     | 佐藤   | D02    | 開発部

社員ID → 部署ID → 部署名という連鎖的な依存（推移的関数従属）があります。部署名が変わったとき、複数行を更新する必要があります：

-- 3NFに修正
CREATE TABLE departments (
    dept_id   VARCHAR(10) PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    emp_id   INTEGER PRIMARY KEY,
    name     VARCHAR(50) NOT NULL,
    dept_id  VARCHAR(10) REFERENCES departments(dept_id)
);

正規化のまとめ表

正規形	ルール	排除するもの
1NF	各セルに単一の値	繰り返しグループ
2NF	1NF + 部分関数従属なし	複合キーの一部への依存
3NF	2NF + 推移的関数従属なし	非キーカラム間の依存

実務では3NFまで満たせば十分です。それ以上（BCNF, 4NF, 5NF）は理論的に重要ですが、過度な正規化はJOINの増加でパフォーマンスを悪化させます。

主キーと外部キー

主キー（PRIMARY KEY） はテーブル内の各行を一意に識別するカラムです。2つの重要な制約があります：

一意性（UNIQUE）: 重複できない
非NULL: NULLを許容しない

-- 自動採番（各RDBMSで構文が異なる）
-- PostgreSQL
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

-- MySQL
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

外部キー（FOREIGN KEY） は他のテーブルの主キーを参照するカラムです。データの整合性を保証します：

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date  DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 存在しない顧客IDを指定するとエラーになる
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (9999, 999, '2025-01-01');
-- ERROR: 外部キー制約違反

ER図の基本 — テーブル間の関係を可視化する

ER図（Entity-Relationship Diagram）はテーブル間の関係を視覚的に表現する図です。3種類のカーディナリティ（多重度）を理解しましょう：

1対1（1:1）

[ユーザー] 1 ──── 1 [ユーザー詳細]
  user_id             user_id (FK/PK)
  name                address
  email               phone

1人のユーザーに1つの詳細情報。カラム数が多すぎるテーブルを分割する場合や、アクセス頻度の異なるデータを分離する場合に使います。

1対多（1:N）

[顧客] 1 ──── * [注文]
  customer_id        order_id
  name               customer_id (FK)
                     order_date

1人の顧客が複数の注文を持つ、最も一般的な関係です。「多」側に外部キーを置きます。

多対多（N:M）

[学生] * ──── * [講座]
  student_id         course_id
  name               title

多対多の関係は直接テーブルで表現できないため、中間テーブル（交差テーブル）を使います：

-- 中間テーブルで多対多を表現
CREATE TABLE student_courses (
    student_id INTEGER REFERENCES students(student_id),
    course_id  INTEGER REFERENCES courses(course_id),
    enrolled_at DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id)
);

インデックス — 検索を高速化する

インデックスは本の索引と同じ概念です。特定のカラムにインデックスを作成すると、そのカラムでの検索が劇的に高速化されます：

-- 基本的なインデックスの作成
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);

-- 複合インデックス（複数カラムの組み合わせ）
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- インデックスの削除
DROP INDEX idx_orders_date;

インデックスを付けるべきカラム

付けるべき	不要なケース
WHERE句で頻繁に使うカラム	テーブルが小さい（数百行以下）
JOINの結合キー	頻繁にINSERT/UPDATEするカラム
ORDER BYで使うカラム	カーディナリティが低い（性別など）
外部キーカラム	テーブル全体を常にスキャンする場合

注意: インデックスはSELECTを高速化しますが、INSERT/UPDATE/DELETEは遅くなります。インデックスのデータも更新する必要があるためです。闇雲に付けず、よく使うクエリパターンに合わせて設計しましょう。

制約 — データの品質を守る

テーブル設計では、アプリケーション側だけでなくデータベース側でもデータの正しさを担保します：

CREATE TABLE products (
    product_id   INTEGER PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,           -- NULL禁止
    sku          VARCHAR(20) UNIQUE,              -- 重複禁止
    price        INTEGER CHECK (price > 0),       -- 値の範囲制限
    category     VARCHAR(30) DEFAULT 'その他',     -- デフォルト値
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

制約	構文	目的
NOT NULL	`column_name type NOT NULL`	NULL値を禁止
UNIQUE	`column_name type UNIQUE`	重複値を禁止
CHECK	`CHECK (条件)`	値の範囲・条件を制限
DEFAULT	`DEFAULT 値`	値省略時のデフォルト
FOREIGN KEY	`REFERENCES table(column)`	外部キー制約

実践ワーク: ECサイトのスキーマ設計

以下の要件を満たすテーブル設計を考え、CREATE TABLE文を書いてみましょう：

要件: オンライン書店のデータベース - 顧客（名前、メール、住所、登録日） - 書籍（タイトル、著者、出版社、価格、ISBN） - 注文（顧客、注文日、合計金額、ステータス） - 注文明細（注文、書籍、数量、小計） - 書籍カテゴリ（カテゴリ名）— 1つの書籍は複数カテゴリに属しうる

課題： 1. 上記の5エンティティに対するCREATE TABLE文を書いてください（適切な主キー、外部キー、制約を付ける） 2. 書籍とカテゴリの多対多関係を中間テーブルで解決してください 3. 各テーブルに適切なインデックスを追加してください 4. この設計が第3正規形を満たしているか確認してください

まとめと次回の準備

今回のポイント: - 正規化はデータの冗長性と更新異常を排除する設計手法 - 1NF: セルに単一値、2NF: 部分関数従属の排除、3NF: 推移的関数従属の排除 - 主キーは行を一意に識別、外部キーはテーブル間の関係を定義 - ER図のカーディナリティ: 1:1, 1:N, N:M（中間テーブルで解決） - インデックスはSELECTを高速化するがINSERT/UPDATEにはコスト - 制約（NOT NULL, UNIQUE, CHECK, DEFAULT）でデータ品質を担保

次回: 総合演習として、ECサイトのデータベースを一から構築し、実務レベルのデータ分析クエリを多数実践します。これまでの全知識を総動員する最終回です。

参考文献: - 増永良文『リレーショナルデータベース入門第3版』（サイエンス社、2017年） - 奥野幹也『詳解MySQL 5.7 — 止まらぬ進化に乗り遅れないためのテクニカルガイド』（翔泳社、2016年） - PostgreSQL公式ドキュメント「テーブル作成」（https://www.postgresql.org/docs/current/sql-createtable.html） - MySQL公式リファレンス「CREATE INDEX」（https://dev.mysql.com/doc/refman/8.0/ja/create-index.html） - Use The Index, Luke — SQLインデックス解説サイト（https://use-the-index-luke.com/ja）

Lecture 10総合演習 — ECサイトのデータ分析

15:00

総合演習 — ECサイトのデータ分析

最終プロジェクト: ECサイトのデータベース構築と分析

この講座の集大成として、ECサイトのデータベースを一から構築し、実務で必要とされるデータ分析クエリを実践します。ここまで学んだすべての知識 — SELECT、WHERE、集計関数、GROUP BY、JOIN、サブクエリ、CTE、ウィンドウ関数、テーブル設計 — を総動員します。

使用する知識の対応表：

知識	使う場面
CREATE TABLE / INSERT	データベースの構築
SELECT / WHERE / ORDER BY	基本的なデータ抽出
集計関数 / GROUP BY / HAVING	売上レポートの作成
JOIN（INNER / LEFT）	テーブル間のデータ結合
サブクエリ / CTE	複雑な条件やステップ分割
ウィンドウ関数	ランキング・累積計算
インデックス / 制約	パフォーマンスとデータ品質

Step 1: データベースの構築

5つのテーブルで構成されるECサイトデータベースを構築します。正規化された設計に注目してください：

-- カテゴリテーブル
CREATE TABLE categories (
    category_id   INTEGER PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE
);

-- 顧客テーブル
CREATE TABLE customers (
    customer_id   INTEGER PRIMARY KEY,
    name          VARCHAR(50) NOT NULL,
    email         VARCHAR(100) UNIQUE NOT NULL,
    city          VARCHAR(30),
    registered_at DATE NOT NULL
);

-- 商品テーブル
CREATE TABLE products (
    product_id    INTEGER PRIMARY KEY,
    product_name  VARCHAR(100) NOT NULL,
    category_id   INTEGER NOT NULL REFERENCES categories(category_id),
    price         INTEGER NOT NULL CHECK (price > 0),
    stock         INTEGER NOT NULL DEFAULT 0
);

-- 注文テーブル
CREATE TABLE orders (
    order_id      INTEGER PRIMARY KEY,
    customer_id   INTEGER NOT NULL REFERENCES customers(customer_id),
    order_date    DATE NOT NULL,
    status        VARCHAR(20) DEFAULT 'confirmed'
        CHECK (status IN ('confirmed', 'shipped', 'delivered', 'cancelled'))
);

-- 注文明細テーブル（多対多の中間テーブル）
CREATE TABLE order_items (
    item_id       INTEGER PRIMARY KEY,
    order_id      INTEGER NOT NULL REFERENCES orders(order_id),
    product_id    INTEGER NOT NULL REFERENCES products(product_id),
    quantity      INTEGER NOT NULL CHECK (quantity > 0),
    unit_price    INTEGER NOT NULL CHECK (unit_price > 0)
);

-- パフォーマンス用インデックス
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);
CREATE INDEX idx_products_category ON products(category_id);

設計のポイント： - order_items.unit_priceは注文時点の価格を記録する（商品テーブルの価格は変わりうる） - orders.statusはCHECK制約で有効な値を制限 - 外部キーで参照整合性を保証 - 頻繁にJOINや検索に使うカラムにインデックスを設定

Step 2: サンプルデータの投入

現実的なデータを投入します。1年分の取引データを想定しています：

-- カテゴリ
INSERT INTO categories VALUES
(1, '電子機器'), (2, '書籍'), (3, '家具'), (4, '食品'), (5, '衣類');

-- 顧客（8名）
INSERT INTO customers VALUES
(1, '田中太郎',   'tanaka@example.com',   '東京',   '2024-01-15'),
(2, '鈴木花子',   'suzuki@example.com',   '大阪',   '2024-02-20'),
(3, '佐藤一郎',   'sato@example.com',     '名古屋', '2024-03-10'),
(4, '高橋美咲',   'takahashi@example.com', '福岡',   '2024-04-05'),
(5, '伊藤健太',   'ito@example.com',       '札幌',   '2024-05-01'),
(6, '渡辺由美',   'watanabe@example.com',  '東京',   '2024-06-15'),
(7, '山本大輔',   'yamamoto@example.com',  '大阪',   '2024-07-20'),
(8, '中村さくら', 'nakamura@example.com',  '東京',   '2024-08-01');

-- 商品（15品）
INSERT INTO products VALUES
(101, 'ノートPC',         1, 98000,  50),
(102, 'ワイヤレスマウス', 1, 3500,  200),
(103, 'メカニカルキーボード', 1, 12000, 100),
(104, '4Kモニター',       1, 55000,  30),
(105, 'Pythonの教科書',   2, 3200,  150),
(106, 'SQL入門',          2, 2800,  180),
(107, 'データサイエンス実践', 2, 3800, 120),
(108, 'オフィスチェア',   3, 45000,  40),
(109, 'スタンディングデスク', 3, 68000, 25),
(110, 'デスクライト',     3, 8500,   80),
(111, 'コーヒー豆 1kg',   4, 2500,  300),
(112, 'プロテインバー 12本', 4, 3600, 200),
(113, 'Tシャツ',          5, 2500,  500),
(114, 'パーカー',         5, 5800,  150),
(115, 'スニーカー',       5, 12000, 100);

-- 注文（20件、2024年7月〜2025年3月）
INSERT INTO orders VALUES
(1001, 1, '2024-07-10', 'delivered'),
(1002, 2, '2024-07-25', 'delivered'),
(1003, 1, '2024-08-05', 'delivered'),
(1004, 3, '2024-08-20', 'delivered'),
(1005, 4, '2024-09-01', 'delivered'),
(1006, 2, '2024-09-15', 'delivered'),
(1007, 5, '2024-10-01', 'delivered'),
(1008, 1, '2024-10-20', 'delivered'),
(1009, 3, '2024-11-10', 'delivered'),
(1010, 6, '2024-11-25', 'shipped'),
(1011, 2, '2024-12-05', 'delivered'),
(1012, 7, '2024-12-20', 'delivered'),
(1013, 1, '2025-01-10', 'delivered'),
(1014, 4, '2025-01-25', 'shipped'),
(1015, 3, '2025-02-05', 'shipped'),
(1016, 8, '2025-02-15', 'confirmed'),
(1017, 5, '2025-02-28', 'confirmed'),
(1018, 1, '2025-03-05', 'confirmed'),
(1019, 2, '2025-03-10', 'confirmed'),
(1020, 6, '2025-03-15', 'cancelled');

-- 注文明細（各注文に1〜3商品）
INSERT INTO order_items VALUES
(1, 1001, 101, 1, 98000),
(2, 1001, 102, 2, 3500),
(3, 1002, 108, 1, 45000),
(4, 1002, 110, 1, 8500),
(5, 1003, 105, 2, 3200),
(6, 1003, 106, 1, 2800),
(7, 1004, 101, 1, 98000),
(8, 1004, 103, 1, 12000),
(9, 1005, 113, 3, 2500),
(10, 1005, 114, 1, 5800),
(11, 1006, 107, 1, 3800),
(12, 1006, 111, 2, 2500),
(13, 1007, 109, 1, 68000),
(14, 1008, 104, 1, 55000),
(15, 1008, 102, 1, 3500),
(16, 1009, 106, 3, 2800),
(17, 1009, 112, 2, 3600),
(18, 1010, 101, 1, 98000),
(19, 1010, 103, 2, 12000),
(20, 1011, 108, 1, 45000),
(21, 1012, 113, 2, 2500),
(22, 1012, 115, 1, 12000),
(23, 1013, 107, 1, 3800),
(24, 1013, 111, 3, 2500),
(25, 1014, 104, 1, 55000),
(26, 1015, 109, 1, 68000),
(27, 1015, 110, 2, 8500),
(28, 1016, 105, 1, 3200),
(29, 1016, 106, 1, 2800),
(30, 1017, 112, 3, 3600),
(31, 1017, 111, 1, 2500),
(32, 1018, 115, 1, 12000),
(33, 1018, 114, 2, 5800),
(34, 1019, 101, 1, 98000),
(35, 1019, 102, 3, 3500),
(36, 1020, 113, 1, 2500);

Step 3: 基本分析クエリ

クエリ1: 月別売上レポート

-- 月ごとの売上推移（キャンセルを除く）
SELECT
    DATE_FORMAT(o.order_date, '%Y-%m') AS 月,
    COUNT(DISTINCT o.order_id)          AS 注文件数,
    SUM(oi.quantity)                    AS 販売点数,
    SUM(oi.unit_price * oi.quantity)    AS 売上合計,
    ROUND(AVG(oi.unit_price * oi.quantity), 0) AS 平均明細額
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status != 'cancelled'
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY 月;

クエリ2: 顧客別購入総額ランキング

WITH customer_spending AS (
    SELECT
        c.customer_id,
        c.name,
        c.city,
        COUNT(DISTINCT o.order_id) AS 注文回数,
        SUM(oi.unit_price * oi.quantity) AS 購入総額
    FROM customers c
    INNER JOIN orders o ON c.customer_id = o.customer_id
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status != 'cancelled'
    GROUP BY c.customer_id, c.name, c.city
)
SELECT
    RANK() OVER (ORDER BY 購入総額 DESC) AS 順位,
    name   AS 顧客名,
    city   AS 都市,
    注文回数,
    購入総額
FROM customer_spending;

クエリ3: カテゴリ別の売上構成比

WITH category_sales AS (
    SELECT
        cat.category_name,
        SUM(oi.unit_price * oi.quantity) AS 売上合計
    FROM order_items oi
    INNER JOIN products p ON oi.product_id = p.product_id
    INNER JOIN categories cat ON p.category_id = cat.category_id
    INNER JOIN orders o ON oi.order_id = o.order_id
    WHERE o.status != 'cancelled'
    GROUP BY cat.category_name
)
SELECT
    category_name AS カテゴリ,
    売上合計,
    ROUND(売上合計 * 100.0 / SUM(売上合計) OVER (), 1) AS 構成比
FROM category_sales
ORDER BY 売上合計 DESC;

クエリ4: 商品別販売実績（売上上位10）

SELECT
    p.product_name  AS 商品名,
    cat.category_name AS カテゴリ,
    SUM(oi.quantity)              AS 販売数量,
    SUM(oi.unit_price * oi.quantity) AS 売上合計,
    COUNT(DISTINCT oi.order_id)   AS 注文件数
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN categories cat ON p.category_id = cat.category_id
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.status != 'cancelled'
GROUP BY p.product_id, p.product_name, cat.category_name
ORDER BY 売上合計 DESC
LIMIT 10;

Step 4: 応用分析クエリ

クエリ5: 四半期ごとの前期比成長率

WITH quarterly_sales AS (
    SELECT
        CONCAT(YEAR(o.order_date), '-Q',
               QUARTER(o.order_date)) AS 四半期,
        SUM(oi.unit_price * oi.quantity) AS 売上
    FROM orders o
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status != 'cancelled'
    GROUP BY YEAR(o.order_date), QUARTER(o.order_date)
    ORDER BY YEAR(o.order_date), QUARTER(o.order_date)
)
SELECT
    四半期,
    売上,
    LAG(売上) OVER (ORDER BY 四半期) AS 前四半期売上,
    CASE
        WHEN LAG(売上) OVER (ORDER BY 四半期) IS NOT NULL
        THEN ROUND((売上 - LAG(売上) OVER (ORDER BY 四半期))
             * 100.0 / LAG(売上) OVER (ORDER BY 四半期), 1)
        ELSE NULL
    END AS 成長率
FROM quarterly_sales;

クエリ6: 都市別の売上と顧客数

SELECT
    c.city                              AS 都市,
    COUNT(DISTINCT c.customer_id)       AS 顧客数,
    COUNT(DISTINCT o.order_id)          AS 注文件数,
    SUM(oi.unit_price * oi.quantity)    AS 売上合計,
    ROUND(SUM(oi.unit_price * oi.quantity) * 1.0
          / COUNT(DISTINCT c.customer_id), 0) AS 顧客単価
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status != 'cancelled'
GROUP BY c.city
ORDER BY 売上合計 DESC;

クエリ7: 注文が2回以上の優良顧客（リピーター分析）

SELECT
    c.name    AS 顧客名,
    c.city    AS 都市,
    COUNT(DISTINCT o.order_id) AS 注文回数,
    MIN(o.order_date) AS 初回注文日,
    MAX(o.order_date) AS 最終注文日,
    DATEDIFF(MAX(o.order_date), MIN(o.order_date)) AS 利用期間日数
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status != 'cancelled'
GROUP BY c.customer_id, c.name, c.city
HAVING COUNT(DISTINCT o.order_id) >= 2
ORDER BY 注文回数 DESC;

クエリ8: 累積売上の推移

WITH daily_sales AS (
    SELECT
        o.order_date AS 日付,
        SUM(oi.unit_price * oi.quantity) AS 日別売上
    FROM orders o
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status != 'cancelled'
    GROUP BY o.order_date
)
SELECT
    日付,
    日別売上,
    SUM(日別売上) OVER (ORDER BY 日付) AS 累積売上
FROM daily_sales
ORDER BY 日付;

クエリ9: RFM分析の基礎

RFM分析は顧客をRecency（最終購入日）、Frequency（購入頻度）、Monetary（購入金額）の3軸で評価するマーケティング手法です：

WITH rfm_raw AS (
    SELECT
        c.customer_id,
        c.name,
        DATEDIFF('2025-03-31', MAX(o.order_date)) AS recency_days,
        COUNT(DISTINCT o.order_id)                  AS frequency,
        SUM(oi.unit_price * oi.quantity)             AS monetary
    FROM customers c
    INNER JOIN orders o ON c.customer_id = o.customer_id
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status != 'cancelled'
    GROUP BY c.customer_id, c.name
),
rfm_scored AS (
    SELECT
        name,
        recency_days,
        frequency,
        monetary,
        NTILE(3) OVER (ORDER BY recency_days ASC)  AS r_score,
        NTILE(3) OVER (ORDER BY frequency DESC)     AS f_score,
        NTILE(3) OVER (ORDER BY monetary DESC)      AS m_score
    FROM rfm_raw
)
SELECT
    name       AS 顧客名,
    recency_days AS 最終購入からの日数,
    frequency  AS 購入回数,
    monetary   AS 購入総額,
    CONCAT(r_score, f_score, m_score) AS RFMスコア,
    CASE
        WHEN r_score = 3 AND f_score = 3 AND m_score = 3 THEN '最優良顧客'
        WHEN r_score >= 2 AND f_score >= 2 THEN '優良顧客'
        WHEN r_score = 3 AND f_score = 1 THEN '新規顧客'
        WHEN r_score = 1 AND f_score >= 2 THEN '離反リスク'
        ELSE '一般顧客'
    END AS セグメント
FROM rfm_scored
ORDER BY monetary DESC;

クエリ10: 未購入商品の在庫レポート

-- 一度も注文されていない商品
SELECT
    p.product_name AS 商品名,
    cat.category_name AS カテゴリ,
    p.price        AS 価格,
    p.stock        AS 在庫数,
    p.price * p.stock AS 在庫金額
FROM products p
INNER JOIN categories cat ON p.category_id = cat.category_id
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id
)
ORDER BY 在庫金額 DESC;

講座全体の振り返り

回	テーマ	核心概念
1-5	SQL基礎	SELECT, WHERE, ORDER BY, データ型, 演算子
6	集計関数とGROUP BY	COUNT, SUM, AVG, HAVING, COALESCE
7	テーブル結合	INNER/LEFT/RIGHT JOIN, 複数テーブル結合
8	サブクエリとCTE	スカラ/IN/EXISTS, WITH句, ウィンドウ関数
9	テーブル設計	正規化(1NF-3NF), 主キー/外部キー, ER図, インデックス
10	総合演習	ECサイト構築, 実務レベルのデータ分析

実践ワーク

上記のECデータベースを自分の環境に構築し、以下の分析クエリを自力で書いてみましょう：

カテゴリ別・月別の売上クロス集計を作成してください
各顧客の最初に購入した商品を表示してください（ROW_NUMBERを使用）
売上が全体の50%を超える商品カテゴリを特定してください
注文ステータスごとの件数と売上合計を集計してください
2025年1月以降の新規注文と、前回注文からの日数を計算してください

次のステップ — この先の学習ロードマップ

SQL入門を修了した後の学習パスは、役割や興味によって分岐します：

データ分析・BI方面: ウィンドウ関数の深掘り → ストアドプロシージャ → Tableau/Power BI連携 → データウェアハウス設計

バックエンド開発方面: トランザクション → ストアドプロシージャ → ORM（SQLAlchemy, Prisma, Active Record）→ マイグレーション管理

データベース管理（DBA）方面: 実行計画（EXPLAIN） → パーティショニング → レプリケーション → バックアップ・リストア → 特定RDBMS（PostgreSQL, MySQL）の深い知識

具体的なRDBMSの学習: - PostgreSQL: JSON型、配列型、CTEの最適化、拡張機能（PostGIS, pgvector） - MySQL: InnoDB内部構造、レプリケーション設定、パフォーマンススキーマ - SQLite: 組み込みDB、モバイルアプリ開発、軽量テスト環境

推薦書籍: - ミック『SQL 第2版ゼロからはじめるデータベース操作』（翔泳社）— 入門書の定番 - ミック『達人に学ぶSQL徹底指南書第2版』（翔泳社）— 中級者の必読書 - Bill Karwin『SQLアンチパターン』（オライリー）— やってはいけないSQL設計を学ぶ

SQLは「知っている」と「使える」の間に大きな溝があります。この講座で構築したECデータベースを自分なりに拡張し、実際にクエリを書いて手を動かすことが上達への最短ルートです。

まとめと次回の準備

今回のポイント: - 正規化されたテーブル設計（5テーブル、外部キー、制約、インデックス） - 月別/カテゴリ別/顧客別の実務レベル集計クエリ - CTE + ウィンドウ関数（RANK, LAG, NTILE, SUM OVER）の実践 - RFM分析による顧客セグメンテーション - EXISTS/NOT EXISTSによる在庫分析

参考文献: - ミック『SQL 第2版ゼロからはじめるデータベース操作』（翔泳社、2016年） - ミック『達人に学ぶSQL徹底指南書第2版』（翔泳社、2018年） - Bill Karwin『SQLアンチパターン』（オライリー・ジャパン、2013年） - PostgreSQL公式ドキュメント（https://www.postgresql.org/docs/current/） - MySQL公式リファレンスマニュアル（https://dev.mysql.com/doc/refman/8.0/ja/） - W3Schools SQL Tutorial（https://www.w3schools.com/sql/）

SQL入門：データベース操作の基本をマスター

Lecture 1データベースとは — なぜSQLを学ぶのか

データベースとは — なぜSQLを学ぶのか

データベースが必要な理由

リレーショナルデータベースとNoSQL

なぜSQLを学ぶべきなのか

主要なRDBMS製品

開発環境のセットアップ

方法1: SQLite + DB Browser for SQLite（最も簡単）

方法2: PostgreSQL + pgAdmin（本格開発向け）

方法3: オンライン環境（インストール不要）

最初のSQL — Hello, SQL!

実践ワーク

まとめと次回の準備

Lecture 2SELECT文の基本 — データを取り出す

SELECT文の基本 — データを取り出す

サンプルテーブルの準備

SELECT文の基本構文

すべての列を取得する — SELECT *

特定の列を取得する

列にエイリアス（別名）を付ける — AS

重複を除外する — DISTINCT

計算列 — 算術演算

文字列の連結

SELECT文の実行順序

実践ワーク

まとめと次回の準備

Lecture 3WHERE句とフィルタリング — 条件でデータを絞る

WHERE句とフィルタリング — 条件でデータを絞る

WHERE句とは

比較演算子

文字列の条件

数値の条件

日付の条件

論理演算子 — AND, OR, NOT

AND（かつ）

OR（または）

NOT（否定）

演算子の優先順位

IN — 複数の値に一致

BETWEEN — 範囲指定

LIKE — パターンマッチング

IS NULL / IS NOT NULL

条件の組み合わせ — 実務的な例

実践ワーク

まとめと次回の準備

Lecture 4ORDER BYとLIMIT — 並べ替えと件数制限

ORDER BYとLIMIT — 並べ替えと件数制限

なぜ並べ替えが必要か

ORDER BY — 基本構文

昇順（ASC） — デフォルト

降順（DESC）

文字列の並べ替え

複数列での並べ替え

NULLの並べ替え位置

LIMIT — 件数制限

OFFSET — ページネーション

TOP Nクエリ — 実務パターン

CASE式 — 条件付きロジック

WHERE + ORDER BY + LIMIT の組み合わせ

実践ワーク

まとめと次回の準備

Lecture 5INSERT・UPDATE・DELETE — データの追加・変更・削除

INSERT・UPDATE・DELETE — データの追加・変更・削除

SELECTとDMLの違い

CREATE TABLE — テーブルの作成

主要なデータ型

主要な制約

INSERT INTO — データの追加

1行挿入

複数行を一度に挿入

挿入結果の確認

UPDATE — データの変更

基本構文

特定の行を更新

WHERE句を使った条件付き更新

危険: WHERE句なしのUPDATE

DELETE — データの削除

基本構文

特定の行を削除