はじめに
応用情報技術者試験の学習を進める中で、SQLの基本用語や構文の境界があいまいになりがちだと感じました。
特に、以下は毎回「どっちがどっち？」になりやすいポイントです。
- DML（INSERT/UPDATE/DELETE） と DDL（CREATE/ALTER/DROP） の役割の違い
- 制約（PRIMARY KEY / FOREIGN KEY / NOT NULL / CHECK / ON DELETE …） の目的と選び方
- 権限管理（GRANT/REVOKE） をどの粒度で考えるか
- ウィンドウ関数（OVER句） が集約関数とどう違うか
この記事は、 私が覚えにくかった部分を、まずここだけ という範囲で 定義→補足 の順にまとめ直した「学習メモ」です。
あくまで自分用の整理メモですが、
同じように応用情報技術者試験に取り組んでいる方やSQLの基礎を体系的に学び直したい方の参考になれば幸いです。
※過去の応用情報技術者試験に関する記事はこちら
応用情報：プロジェクトマネジメント
応用情報：情報システム開発
0. まず“器と中身”の地図
┌─────────── 器(スキーマ) ───────────┐
│ テーブル/列/型/制約/インデックス… │ ← DDL (CREATE/ALTER/DROP)
└───────────┬──────────────────────┘
↓
中身(行データ) ← DML (INSERT/UPDATE/DELETE/SELECT)
- DDL：テーブルという“器”そのものを作る/直す/捨てる
- DML：器の“中身(行)”を増やす/直す/消す/読む
0-1. サンプル表（最小構成）
-- 親：部門
CREATE TABLE dept (
dept_id INTEGER PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
-- 子：社員
CREATE TABLE emp (
emp_id INTEGER PRIMARY KEY,
name VARCHAR(50) NOT NULL,
dept_id INTEGER NOT NULL,
score INTEGER,
joined DATE NOT NULL,
CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id)
REFERENCES dept(dept_id)
ON DELETE CASCADE
);
-- データ
INSERT INTO dept VALUES (1,'開発'),(2,'営業');
INSERT INTO emp (emp_id, name, dept_id, score, joined) VALUES
(101,'北海 太郎',1,100,DATE '2024-04-01'),
(102,'東京 二郎',1, 85,DATE '2024-04-02'),
(201,'大阪 花子',2, 92,DATE '2024-04-03');
簡易ER図：
dept emp
+----------+ +----------+
|dept_id PK|◀──────────┐ |emp_id PK |
|dept_name | └─ FK(emp.dept_id)→dept.dept_id
+----------+ |dept_id FK|
|name |
|score |
|joined |
+----------+
1. DML（データ操作）：INSERT / UPDATE / DELETE
INSERT（追加）
-- 値を直書き
INSERT INTO dept VALUES (3,'管理');
-- SELECT結果をそのまま入れる（実務で頻出）
INSERT INTO emp (emp_id, name, dept_id, score, joined)
SELECT 301,'福岡 三郎',2,88,CURRENT_DATE;
- 定義：行を増やす。
-
INSERT INTO ... VALUES(...)INSERT INTO ... SELECT ...の2形態だけ覚える
UPDATE（更新）
UPDATE emp
SET score = 95
WHERE emp_id = 201;
- 定義：既存行の値を変える。
- WHERE を忘れると全件更新。
-
UPDATE ... SET ... WHERE ...で覚える。
DELETE（削除）
DELETE FROM emp WHERE emp_id = 301;
- 定義：行を消す。
- 親テーブル（dept）を削除する場合は外部キーの削除規則に注意。
-
DELETE FROM ... WHERE ...で覚える。
2. DDL（定義操作）と制約
CREATE / ALTER / DROP（基本）
-- 例：返品テーブル
CREATE TABLE returns (
return_no INTEGER PRIMARY KEY,
sales_no INTEGER NOT NULL,
returned DATE NOT NULL,
reason VARCHAR(100) DEFAULT NULL
);
-- 列追加
ALTER TABLE emp ADD COLUMN email VARCHAR(120);
-- テーブル削除
DROP TABLE returns;
- 定義：器の作成/変更/削除。
- DROP TABLE は定義もデータも消滅してしまうので注意。
よく使う制約と“選び方”
- PRIMARY KEY：行一意（NULL不可）
- FOREIGN KEY：参照整合性（親がない子を禁止）
- NOT NULL：必須列
- CHECK：値の妥当性ルール（例：日付が過去でない、0≦score≦100）
選び方チェックリスト
- その列は必ず入る？ → NOT NULL
- 値の重複を許さない？ → PRIMARY KEY（またはUNIQUE）
- 別テーブルの存在が必要？ → FOREIGN KEY
- 範囲や形式を縛りたい？ → CHECK
-- CHECK：返品日は 2020-01-01 以降
ALTER TABLE returns
ADD CONSTRAINT chk_returns_date
CHECK (returned >= DATE '2020-01-01');
-- 外部キー：売上テーブルを参照（例）
ALTER TABLE returns
ADD CONSTRAINT fk_returns_sales
FOREIGN KEY (sales_no)
REFERENCES sales(sales_no)
ON DELETE CASCADE;
外部キーの削除規則（超重要）
-
ON DELETE CASCADE：親を消すと子も自動削除（連鎖）。 -
RESTRICT/NO ACTION：子が残っていれば親は消せない（安全）。
3. 権限管理（GRANT / REVOKE）
-- SELECT だけ付与
GRANT SELECT ON emp TO reporter;
-- 列単位 UPDATE（score と joined だけ）
GRANT UPDATE (score, joined) ON emp TO reporter;
-- 取り消し（FROM を忘れない）
REVOKE SELECT ON emp FROM reporter;
-- PUBLIC に広く付与（学習以外は慎重に）
GRANT ALL PRIVILEGES ON emp TO PUBLIC;
- 「誰に／どのオブジェクトに／何を」の3点で考える
- 権限は“付与したら戻す”のが基本（最小権限の原則）
4. ウィンドウ関数（OVER）— “各行に” 集計結果を付ける
行を残したまま、窓(グループ) 内での集計や順位を列として付与する。
ウィンドウ関数は行を残したまま、結果を列として付ける。
集約 vs ウィンドウ（結果の違いを比較）
(A) 集約（AVG+GROUP BY）
行が“部門数”まで減る
SELECT dept_id, AVG(score) AS avg_by_dept
FROM emp
GROUP BY dept_id;
(B) ウィンドウ（AVG OVER）
各行は残り、平均が“列で付く”
SELECT
name, dept_id, score,
AVG(score) OVER (PARTITION BY dept_id) AS avg_by_dept
FROM emp;
[集約] emp ──GROUP BY dept_id──▶ dept_idごとの1行だけ
[ウィンドウ] emp ──OVER(PARTITION BY) ▶ 行はそのまま＋平均列が付く
代表例
部門平均を各行に（PARTITION BY）
SELECT
name,
dept_id,
score,
AVG(score) OVER (PARTITION BY dept_id) AS avg_by_dept
FROM emp;
ランキング（同点の扱いで使い分け）
SELECT
name,
score,
RANK() OVER (ORDER BY score DESC) AS rank_skip_ties, -- 1,1,3...
DENSE_RANK() OVER (ORDER BY score DESC) AS rank_dense, -- 1,1,2...
ROW_NUMBER() OVER (ORDER BY score DESC) AS rownum_seq -- 1,2,3...
FROM emp;
移動平均（フレーム指定）
-- joined 昇順で、現在行＋直前2行の平均
SELECT
name,
joined,
score,
AVG(score) OVER (
ORDER BY joined
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS ma3
FROM emp
ORDER BY joined;
-
PARTITION BY：窓（グループ）を切る -
ORDER BY：窓内の並び順 -
ROWS BETWEEN ...：窓の幅（移動平均・ローリングで使用）
5. “実行順の地図”で整理（WHERE/HAVING/ウィンドウの位置）
SQL（概念上）の評価順：
FROM → WHERE → GROUP BY → HAVING → SELECT（式の評価・ウィンドウ） → ORDER BY → LIMIT
- WHERE：グループ化前に行を絞る
-
HAVING：
GROUP BYでできたグループを絞る - ウィンドウ関数：SELECTで列として付与される ⇒ WHEREでは使えない
行の絞り込み ── WHERE ──▶ 集約 ── GROUP BY ──▶ グループの絞り込み ── HAVING
│
└─▷（SELECTで各種式評価）＋ ウィンドウ関数付与
│
└─▷ 並べ替え ORDER BY
6. よくあるつまずき（まとめ）
-
NULL比較：
= NULLは常に不一致。IS NULL/IS NOT NULLを使う。 - ON DELETE CASCADE：便利だが履歴が消える。要件によりRESTRICTを選ぶ。
- DROP TABLE：器も中身も消滅。実務ではTRUNCATEや論理削除を検討。
- 権限：最小権限、付けたら戻す。PUBLICは慎重に。
おわりに
“器(DDL)と中身(DML)”の地図と実行順の一本線が見えると、
制約・権限・ウィンドウ関数の位置づけがスッと理解できると思っています。
この記事が、試験学習と実務の迷いを減らす一助になれば嬉しいです。