午後試験で差が付く！DDL／DCL・カーソル・トリガ・インデックスを一気に極める SQL 対策講座

――DDL（Data Definition Language）は「テーブルの設計図」を描き、DCL（Data Control Language）はその設計図を「誰が、どこまで触れるか」を決める“鍵”。
応用情報技術者試験〈午後問6〉やデータベーススペシャリスト試験〈午後Ⅰ・Ⅱ〉では、CREATE TABLE → ALTER TABLE → GRANT / REVOKE といった一連の操作を、カーソル処理・トリガを交えたシナリオで解かせる出題が定番化しています。にもかかわらず「制約の貼り方」や「権限の巻き取り方」は参考書でも断片的になりがちで、得点差がつくポイントです。

本記事では DDL 20 語＋DCL 12 語を A→Z 早見表で網羅したうえで、

• 過去 5 年の出題頻度ランキングで重要度を可視化
• 図解・比較表・身近なたとえ話で暗記負荷を軽減
• 午後試験で減点されやすい書き方を “NG→OK” で対比

を順に解説します。まずは「DDL と DCL の全体像」を把握し、次に「CREATE / ALTER の制約句」「カーソル＋トリガ連携」へと段階的に深掘りする構成です。

DDL・DCLキーワード総覧│午後試験頻出ランキングと A→Z 早見表

「家を建てるなら設計図（DDL）、運営するなら鍵と入館証（DCL）」──まずは 何を定義し、誰に触らせるか という全体像を押さえましょう。

1. DDL／DCL を 30 秒で俯瞰

• DDL（Data Definition Language）：テーブル・インデックス・トリガなど “造る／直す／壊す” 系コマンド
• DCL（Data Control Language）：造ったオブジェクトを “誰に触らせ、いつ取り上げるか” を決めるコマンド
• 応用情報〈午後問6〉と DB スペシャリスト〈午後Ⅰ・Ⅱ〉では 「DDL で設計 → DCL で運用」 の流れが 8 割超の定番

2. 過去 5 年の頻出トップ 5（AP＋DB）

* 厳密には TCL ですが、午後問題では DCL とセットで問われるため掲載。

3. A→Z 早見表（概要）

A ALTER TABLE      B BACKUP CONTROLFILE    C COMMIT
D DROP INDEX        …                      T TRIGGER
U UPDATE STATISTICS V VIEW                 W WITH ADMIN OPTION

4. 身近なたとえで全体像を掴む

• 建築現場：CREATE TABLE＝基礎工事、ALTER TABLE＝リフォーム
• マンション管理人：GRANT＝鍵を配る、REVOKE＝鍵を回収
• 防犯カメラ：TRIGGER＝動きを検知して自動録画
• 巡回警備員：カーソル処理＝各階を順番に見回るルート

CREATE TABLE と ALTER TABLE の制約句完全攻略│主キー・外部キー・CHECK を図解で整理

「設計図を書き換えるときに壊れないルールをどう埋め込むか」──午後試験で毎年 20 点前後が動く最重要パートです。

1. CREATE TABLE：最初に“守り”を仕込む 3 ステップ

CREATE TABLE order_t (
  id       SERIAL,
  cust_id  INT,
  order_dt DATE NOT NULL,
  …
);

CONSTRAINT pk_order PRIMARY KEY (id)

CONSTRAINT fk_order_cust
  FOREIGN KEY (cust_id)
  REFERENCES customer(id)
  ON DELETE CASCADE

POINT： 制約名は「子テーブル_親テーブル」の順（例：fk_order_cust）で付けると、午後Ⅱの記述で識別しやすく高評価を狙えます。

2. ALTER TABLE：既存データ付きで“安全リフォーム”

3. 図で見る「制約が守る範囲」

4. 午後試験で“書ければ +2 点” テンプレ

-- 外部キー＋ON DELETE CASCADE を後付け
ALTER TABLE order_t
  ADD CONSTRAINT fk_order_cust
  FOREIGN KEY (cust_id)
  REFERENCES customer(id)
  ON DELETE CASCADE;
COMMIT;

ALTER → COMMIT をセットに書くと、採点基準の「制約追加後のトランザクション制御」を満たせます。ロールバック想定問題では SAVEPOINT を挟む応用まで押さえると満点ライン。

次セクションでは、カーソル処理 × トリガ連携で「変更を検知して即集計更新」を実装する午後Ⅱ必修シナリオを図解と擬似コードで分解します。

トリガとカーソル処理でリアルタイム整合性│午後Ⅱ必修シナリオ分解

1. なぜ「カーソル×トリガ」が午後Ⅱの定番か

• 大量データを 1 行ずつ“見回り”ながら集計・監査する実務フローを模擬
• 「INSERT された瞬間に別テーブルを更新」など、即時性と一貫性を同時に問う
• 採点観点：
  1. 明示カーソルの OPEN → FETCH → CLOSE を正順で書けるか
  2. BEFORE / AFTER トリガの発火順序を言語化できるか

2. 明示カーソル 4 ステップを例えで覚える

POINT： 午後問題では EXIT WHEN cur_%NOTFOUND; と書けるかで 3 点差が付く年度もあります。

3. BEFORE / AFTER トリガを図解

4. カーソル＋トリガ連携サンプル（PostgreSQL PL/pgSQL）

CREATE OR REPLACE FUNCTION fn_order_sum_refresh()
RETURNS TRIGGER AS $$
DECLARE
  rec order_t%ROWTYPE;
BEGIN
  FOR rec IN SELECT * FROM order_t WHERE cust_id = NEW.cust_id LOOP
    UPDATE customer_sum
       SET total = total + rec.amount
     WHERE cust_id = rec.cust_id;
  END LOOP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_after_ins
AFTER INSERT ON order_t
FOR EACH ROW
EXECUTE FUNCTION fn_order_sum_refresh();

• 解説：INSERT トリガ発火 → 関連行をカーソル LOOP で集計更新
• 試験対策：RETURN NEW / RETURN OLD の書き分けを明示すること

5. よくある減点パターン

1. トリガ無限ループ：UPDATE 先に同じトリガが付いている
2. カーソル未 CLOSE：答案コード欄が狭くても省略不可
3. BEFORE で副問い合わせ更新：整合性違反で自滅

GRANT・REVOKE と ROLE 設計│鍵の配り方・回収で失点しない権限モデル

1. 採点ポイントは「粒度 × 継承 × 取り消し順序」

2. 鍵を配る・回収する 3 ステップ（身近なたとえ：マンション管理）

GRANT SELECT, INSERT
  ON order_t
  TO clerk_role;

GRANT SELECT
  ON order_t
  TO manager_role
  WITH GRANT OPTION;

REVOKE ALL
  ON order_t
  FROM clerk_role
  CASCADE;

3. ROLE 設計 4 パターンを図で比較

4. “ロール爆発”を防ぐベストプラクティス

1. 命名規則：機能_F × 職位_R など接尾辞で可視化
2. 権限バンドル：似た操作はストアド関数にラップし EXECUTE を付与
3. 棚卸し SQL：\dp+（PostgreSQL）、DBA_TAB_PRIVS（Oracle）で現状確認を答案に添えると加点対象

5. 午後Ⅱでそのまま書けるテンプレ

-- 権限付与：機能別ロール
GRANT SELECT ON order_t TO r_reader;
GRANT INSERT, UPDATE ON order_t TO r_writer;

-- 職位別ロールに機能別ロールをまとめる
GRANT r_reader TO clerk_role;
GRANT r_reader, r_writer TO manager_role;

-- 退社時に一括回収
REVOKE r_reader, r_writer FROM clerk_role CASCADE;
COMMIT;

機能別ロール → 職位別ロール → ユーザ の 3 段階で “付与・再委任・回収” を整理すれば、採点表の全観点を網羅できます。

インデックス設計とパフォーマンス最適化│CREATE INDEX・CLUSTERED INDEX の選択基準

1. 「探し物は図書館の“索引”」──まずはインデックスの役割を 30 秒で

• B-tree インデックス：図書館の “五十音索引”。途中から抜いても整列が崩れない
• ハッシュインデックス：ロッカーの “暗証番号直行検索”。等価比較だけなら最速だが並び替え不可
• 午後問題では「範囲検索＝B-tree、完全一致＝ハッシュ」の判断根拠を言語化できるかが配点

2. CREATE INDEX 基本構文とオプション早見表

3. 出題頻度で押さえる「複合インデックス３選」

1. (顧客 ID, 注文日) ── 売上集計＋範囲検索
2. (商品カテゴリ, 単価 DESC) ── 上位 N 件抽出（TOP-N クエリ）
3. 部分インデックス WHERE status='A' ── アクティブ行だけ索引化で 80% ブロック削減

A→Z 早見法： C=CLUSTER → I=INCLUDE → P=PARTIAL で「CIP」と暗記。

4. 実務たとえで理解 ──「本棚 vs. 書庫」

• B-tree：背表紙順の開架本棚 ――途中で抜いても他の本位置を推測しやすい
• ハッシュ：鍵付き書庫ロッカー ――番号を知らないと探せない
• CLUSTER：人気書籍を入口近くの平棚に移動し I/O を 1 往復に圧縮

5. 午後試験で減点されないテンプレ

-- 範囲検索とカバーリングを両立
CREATE INDEX idx_ord_cust_dt
  ON order_t(cust_id, order_dt)
  INCLUDE (amount);

-- 直後にクラスタリングで I/O 最適化
CLUSTER order_t USING idx_ord_cust_dt;
ANALYZE order_t;

6. よくある減点ポイント集

1. SELECTIVITY（選択度）を数値で言えない
2. 書換頻度の高い列にインデックス → UPDATE/INSERT 性能劣化を説明不足
3. CLUSTER/REINDEX のタイミングを書かない → 保守計画が欠落扱い

VIEW と WITH CHECK OPTION│参照制御とマテリアライズドビューの使い分け

1. 「窓口を増やしても、奥の金庫は守る」──VIEW で何を隠し、何を見せるか

• 本表を直接触らせず、必要列・必要行だけ“のぞき窓”を開ける 論理的サブセット化
• 読み取り専用ユーザには SELECT 権限だけをビュー経由で付与し、本表への INSERT/UPDATE を遮断
• 午後問題では「列レベル権限 vs. ビュー経由権限」の比較説明が配点ポイント

2. WITH CHECK OPTION のしくみを図解

3. マテリアライズドビューとの違い（午後Ⅱで混同注意）

4. 試験頻出 VIEW テンプレ２種

1. 列フィルタ＋列レベル権限

   CREATE VIEW v_staff_public AS
     SELECT emp_id, emp_name, section
       FROM staff_t;
   GRANT SELECT ON v_staff_public TO all_staff;

2. 行フィルタ＋WITH CHECK OPTION

   CREATE VIEW v_order_active AS
     SELECT * FROM order_t WHERE status='A'
     WITH CHECK OPTION;

   CHECK OPTION により、ビュー条件を満たさない行は INSERT / UPDATE できない。

5. よくある減点パターン

• PL/pgSQL でビュー名を直接 INSERT し、WITH CHECK OPTION を忘れる
• マテリアライズドビューを即時反映用途に使用 → REFRESH 遅延説明不足で減点
• ビュー層をまたぐ権限伝播を記述しない → “最小権限”観点で減点