本記事では、SQLとE-R図の概要を知っている方向けに、過去問で出題されたSQLをE-R図と合わせて理解するためのものです。DDL、DCL、DMLに分けて、問題を解きながら概念をまとめます。
基本情報技術者試験で出題されるSQLが大まかにわかる方向けです。
今回の内容は以下の動画でも解説しています。
独自の解釈により、不適切な表現がある可能性があります。不適切な表現があった場合、その都度修正していきます。
SQL SELECT文における句の実行順序
SQLクエリの句の論理的な実行順序は以下のとおりです。
論理的な実行順序（概要）
| 順 | SQL句 | 説明 |
|---|---|---|
| 1 | FROM句 | テーブルを指定します。 |
| 2 | JOIN句 | 複数テーブルの結合方法を指定します。 |
| 3 | WHERE句 | 行の抽出条件を指定します。 |
| 4 | GROUP BY句 | 行をグループ化します。 |
| 5 | HAVING句 | グループ毎の集計結果に基づいて、さらにデータを絞り込む条件を指定します。 |
| 6 | SELECT句 | 出力する列を指定します。 |
| 7 | ORDER BY句 | 結果セットをソートします。 |
データ定義言語（DDL）
データ定義言語（DDL：Data Definition Language）は、データベースの構造を定義・変更するためのSQLのカテゴリです。DDLを用いることで、データベース自体や、データベース内に存在するテーブル、ビュー、インデックスといった様々なオブジェクトの作成、削除、定義変更を行うことができます。
テーブルの新規作成 (CREATE TABLE)
例1:SQL
以下のSQL文は、料金プランテーブルを定義し、料金プランコード、通信事業者コード、料金プラン名、基本料金、通話単価、通信単価などのカラムを持ちます。 料金プランコードは主キー、通信事業者コードは外部キーとして設定されており、通信事業者テーブルを参照します。
-- 料金プランテーブルを作成するSQL文です
CREATE TABLE 料金プラン (
-- 料金プランコードカラムを定義します。CHAR(8)型、NOT NULL制約
料金プランコード CHAR(8) NOT NULL,
-- 通信事業者コードカラムを定義します。CHAR(4)型、DEFAULT '1234' (デフォルト値設定)、NOT NULL制約
通信事業者コード CHAR(4) DEFAULT '1234' NOT NULL, -- k: CHAR(4) DEFAULT '1234' NOT NULL
-- 料金プラン名カラムを定義します。VARCHAR(30)型（可変長文字列型）、NOT NULL制約
料金プラン名 VARCHAR(30) NOT NULL,
-- 基本料金カラムを定義します。DECIMAL(5, 0)型（正確な小数点数を扱う際のデータ型）、NOT NULL制約
基本料金 DECIMAL(5, 0) NOT NULL,
-- 通話単価カラムを定義します。DECIMAL(5, 2)型、NOT NULL制約
通話単価 DECIMAL(5, 2) NOT NULL,
-- 通信単価カラムを定義します。DECIMAL(5, 4)型、NOT NULL制約
通信単価 DECIMAL(5, 4) NOT NULL,
-- 料金プランコードを主キーとして設定します。
PRIMARY KEY (料金プランコード), -- 主キー
-- 通信事業者コードを外部キーとして設定し、通信事業者テーブルの通信事業者コードカラムを参照します。
FOREIGN KEY (通信事業者コード) REFERENCES 通信事業者 (通信事業者コード) -- 外部キー
);
例1:E-R図
以下のE-R図は、この料金プランテーブルと通信事業者テーブルの関係性を図示しており、料金プランが通信事業者によって提供されることを表しています。
一般化したSQL文
CREATE TABLE文は、データベース内に新しいテーブルを作成するためのSQL文です。 テーブルはカラム定義と制約から構成され、カラムごとにデータ型と制約を指定します。 主キー、外部キー、検査制約などをオプションで設定することで、データの整合性と品質を確保できます。
CREATE TABLE テーブル名 (
-- カラム定義1
カラム名_1 データ型 制約,
-- カラム定義2
カラム名_2 データ型 制約,
-- ... (他のカラム定義)
カラム名_N データ型 制約,
-- 主キー制約 (オプション)
PRIMARY KEY (主キーとなるカラム名),
-- 外部キー制約 (オプション、複数指定可能)
FOREIGN KEY (外部キーとなるカラム名) REFERENCES 参照テーブル名 (参照カラム名),
-- 検査制約 (オプション、複数指定可能)
CHECK (検査条件)
-- ... (その他のテーブル制約)
);
※データ型：INTEGER, VARCHAR, DATEなど
※制約：NOT NULL, UNIQUEなど
テーブル定義の変更 (ALTER TABLE)
ALTER TABLE文は、データベースのテーブル定義を変更するためのSQL文です。 この文を使用することで、列の追加や削除、データ型の変更、さらには制約の追加といった操作が可能です。
例1:SQL
以下のSQLは、クーポン明細テーブルに制約を追加するものです。
制約の名前は「クーポン明細_IX1」で、UNIQUE制約という種類です。
このUNIQUE制約により、「クーポンコード」「獲得会員コード」「獲得制限_1枚限り」の組み合わせが一意になるように設定されます。
ALTER TABLE クーポン明細 -- テーブル「クーポン明細」に対して変更を加える
ADD CONSTRAINT クーポン明細_IX1 -- 制約を追加する。制約名は「クーポン明細_IX1」とする
UNIQUE ( -- UNIQUE制約（一意制約）を定義する。対象列は以下
クーポンコード, -- 対象列1: クーポンコード
獲得会員コード, -- 対象列2: 獲得会員コード
獲得制限_1枚限り -- 対象列3: 獲得制限_1枚限り
); -- UNIQUE制約定義 終了
テーブル制約の一般化
以下のSQLは、既存のテーブルに制約を追加するALTER TABLE文の構文を説明しています。
制約タイプには、一意制約のUNIQUE、主キー制約のPRIMARY KEY、外部キー制約のFOREIGN KEY、条件制約のCHECKなどがあり、それぞれ構文と定義が異なります。
FOREIGN KEY制約では、参照整合性を保つためのON DELETEやON UPDATEオプションで、連動動作や操作拒否などを設定できます。
ALTER TABLE テーブル名
ADD CONSTRAINT 制約名
[制約タイプ] (制約定義);
テーブル制約の制約タイプ
・UNIQUE: `UNIQUE (カラム名_1, ...)`
・PRIMARY KEY: `PRIMARY KEY (カラム名_1, ...)`
・FOREIGN KEY: `FOREIGN KEY (外部キーカラム) REFERENCES 親テーブル (参照カラム) (オプション：ON DELETE [参照動作] または ON UPDATE [参照動作]`)
参照動作: `ON DELETE`, `ON UPDATE` に続けて指定。
・CASCADE: 連動削除/更新
・RESTRICT, NO ACTION: 操作拒否
・SET NULL: NULL設定
・SET DEFAULT: デフォルト値設定
・CHECK: `CHECK (検査条件式)`
テーブル制約の例
例:
ALTER TABLE 商品テーブル
ADD CONSTRAINT 商品テーブル_UQ_商品コード
UNIQUE (商品コード);
例:
ALTER TABLE 注文テーブル
ADD CONSTRAINT 注文テーブル_PK
PRIMARY KEY (注文ID);
例:
ALTER TABLE 注文明細テーブル
ADD CONSTRAINT 注文明細テーブル_FK_商品テーブル
FOREIGN KEY (商品コード)
REFERENCES 商品テーブル (商品コード)
ON DELETE RESTRICT
ON UPDATE CASCADE;
例:
ALTER TABLE 従業員テーブル
ADD CONSTRAINT 従業員テーブル_CK_給与正の値
CHECK (給与 >= 0);
ビューの作成 (CREATE VIEW)
例1:SQL
以下のSQL文は、処方箋別確保在庫という名前のビューを作成するものです。
ビューの内容は、発行日と有効日の範囲内で、かつ薬剤処方日が未登録の処方箋明細を集計し、薬剤コードごとの大人1日あたりの処方量の合計を算出します。
これにより、現在有効な処方箋に基づいて、薬剤ごとの必要な在庫量を把握することができます。
-- ビューの作成を開始するSQL文
CREATE VIEW 処方箋別確保在庫 (薬剤コード, 確保量_大人1日) AS
-- ビューとして表示する列を選択するSELECT文
SELECT
T3.薬剤コード, -- 薬剤コード: 集計対象の薬剤コード (サブクエリT3から取得)
SUM(T3.処方量_大人1日) -- 大人1日あたりの処方量を合計する集計関数。
-- データ取得元のテーブルを定義するFROM句 (ここではサブクエリを使用)
FROM
(
-- サブクエリ開始: 有効な処方箋明細を取得する
SELECT
T2.薬剤コード, -- 薬剤コード: 処方箋明細テーブルT2から薬剤コードを取得
T2.処方量_大人1日 -- 処方量_大人1日: 処方箋明細テーブルT2から大人1日あたりの処方量を取得
FROM
処方箋 T1, -- 処方箋テーブル (処方箋のヘッダー情報を持つテーブル)
処方箋明細 T2 -- 処方箋明細テーブル (処方箋ごとの薬剤情報を持つテーブル)
WHERE
T1.処方箋 ID = T2.処方箋 ID -- 処方箋テーブルT1と処方箋明細テーブルT2を処方箋IDで結合 (紐付け)
AND T1.発行年月日 <= CURRENT_DATE -- 発行年月日が今日以前の処方箋に絞り込む (過去に発行された処方箋)
AND T1.有効年月日 >= CURRENT_DATE -- 有効年月日が今日以降の処方箋に絞り込む (現在有効な処方箋)
AND T1.薬剤処方年月日 IS NULL -- 薬剤処方年月日がNULLの処方箋に絞り込む (薬剤処方日が未登録、つまりまだ処方処理が完了していない処方箋)
-- サブクエリ終了
) T3
-- 集計処理を行うGROUP BY句
GROUP BY
T3.薬剤コード; -- 薬剤コードごとにグループ化 (薬剤コードが同じ処方箋明細をまとめる)
例1:E-R図
処方箋と処方箋明細のエンティティとその関係性を表したE-R図です。
「処方箋」エンティティは処方箋IDを主キーとし、発行医師コードや発行日などの属性を持ちます。「処方箋明細」エンティティは処方箋IDと薬剤コードを主キーとし、処方量_大人1日などの属性を持ちます。
処方箋一件に対して複数の処方箋明細が存在する、1対多の関係性が定義されています。
例2:SQL
以下のSQL文は、HRスキーマの入室管理用社員というビューを作成するものです。
ビューの内容は、HRスキーマの社員テーブルから社員ID、氏名、勤務区分を取得します。
-- ビューの作成を開始するSQL文
CREATE VIEW HR.入室管理用社員 (社員 ID, 氏名, 勤務区分) AS
-- ビューとして表示する列を選択するSELECT文
SELECT
社員 ID, -- 社員ID: 社員テーブルから社員IDを取得し、ビューの "社員 ID" 列に表示
氏名, -- 氏名: 社員テーブルから氏名を取得し、ビューの "氏名" 列に表示
勤務区分 -- 勤務区分: 社員テーブルから勤務区分を取得し、ビューの "勤務区分" 列に表示
-- データ取得元のテーブルを指定
FROM
HR.社員; -- HRスキーマの社員テーブルからデータを取得
例3:SQL
以下のSQL文は、HRスキーマの入室管理用社員ビューを、組織長氏名も含むように更新するものです。
社員テーブルをT1, T2、組織テーブルをT3として結合し、社員情報に加えて所属組織の組織長の氏名を取得しています。
これにより、入室管理に必要な社員情報と組織長名を一括で参照できるようになります。
-- ビューの作成を開始するSQL文
CREATE VIEW HR.入室管理用社員 (社員 ID, 氏名, 勤務区分, 組織長氏名) AS
-- ビューとして表示する列を選択する
SELECT
T1.社員 ID, -- 社員ID: 社員テーブルT1から社員IDを取得
T1.氏名, -- 氏名: 社員テーブルT1から氏名を取得
T1.勤務区分, -- 勤務区分: 社員テーブルT1から勤務区分を取得
T2.氏名 -- 組織長氏名: 社員テーブルT2から組織長の氏名を取得
-- データ取得元のテーブルを指定
FROM
HR.社員 T1, -- 社員テーブル (社員情報を取得するためにT1という別名をつける)
HR.社員 T2, -- 社員テーブル (組織長情報を取得するためにT2という別名をつける)
HR.組織 T3 -- 組織テーブル (組織情報を取得するためにT3という別名をつける)
-- テーブル間の結合条件やデータ抽出条件を指定
WHERE
T1.所属組織 ID = T3.組織 ID -- 社員テーブルT1の所属組織IDと組織テーブルT3の組織IDを結合 (社員がどの組織に所属しているかを特定)
AND T3.組織長の社員 ID = T2.社員 ID; -- 組織テーブルT3の組織長の社員IDと社員テーブルT2の社員IDを結合 (組織の組織長を特定)
例3:E-R図
このE-R図は、社員と組織のエンティティとそれらの間の関係性を表しています。
組織エンティティは組織長となる社員を参照し、また上位組織を自己参照する構造を持っています。
これにより、組織階層と社員の所属関係を表現できるデータモデルとなっています。
インデックスの作成 (CREATE INDEX)
一般化したSQL
以下のSQL文は、テーブルにインデックスを作成するCREATE INDEX文の構文を示しています。
UNIQUEオプションを付けることで一意制約のあるインデックスを作成でき、省略した場合は非一意インデックスとなります。
CREATE [UNIQUE] INDEX インデックス名
ON テーブル名 (カラム名 [ASC|DESC] [, カラム名 [ASC|DESC]] ...);
例:
CREATE INDEX カテゴリ_価格_desc_index
ON 商品テーブル (カテゴリ ASC, 価格 DESC);
データ制御言語（DCL）
データ制御言語（DCL：Data Control Language）は、データベースへのアクセス制御やトランザクション制御を行うためのSQLのカテゴリです。DCLを用いることで、ユーザーやロールに対する権限の付与・剥奪、トランザクションの確定・取消といった操作を行い、データベースのセキュリティとデータの整合性を維持することができます。
ユーザーへの権限付与 (GRANT)
例1:SQL
以下のSQL文は、契約テーブルに対するSELECT権限をADMINユーザーに付与するものです。ただし、権限が付与されるカラムは契約IDと暗証番号のみに限定されています。
-- SELECT権限を付与するSQL文です
GRANT SELECT
-- 契約テーブルの特定のカラムを指定します
(契約ID,
暗証番号)
-- 権限を付与する対象のテーブルを指定します
ON 契約
-- 権限を付与するユーザーまたはロールを指定します
TO ADMIN;
例2:SQL
以下のSQLはSELECT権限を付与するGRANT文です。権限の対象はHRスキーマの入室管理用社員ビューです。このSQLによりROOM_APユーザーが入室管理用社員ビューをSELECT可能になります。
-- SELECT権限付与SQL文: 特定のユーザーにSELECT権限を付与する
GRANT SELECT
-- どのオブジェクトに対する権限か: HR.入室管理用社員ビューに対する権限
ON HR.入室管理用社員
-- 権限を付与する対象のユーザー: ROOM_APユーザーに権限を付与
TO ROOM_AP;
一般化したSQL
このSQLは各種権限を付与するGRANT文の構文を示しています。 {権限の種類} にはSELECT, INSERT, UPDATE, DELETEなどを指定でき、カラムを限定することも可能です。 {オブジェクト名} にテーブル名やビュー名を指定し、TO {ユーザーまたはロール名} で権限を付与する対象を指定します。
GRANT {権限の種類} -- 付与する権限の種類 (SELECT, INSERT, UPDATE, DELETE など)
-- 【オプション: カラム指定】特定のカラムに権限を絞る場合のみ記述
[(カラム名_1,
カラム名_2,
...)]
-- 権限を付与する対象のオブジェクトを指定 (テーブル、ビューなど)
ON {オブジェクト名} -- 例: テーブル名、ビュー名
-- 権限を付与するユーザーまたはロールを指定
TO {ユーザーまたはロール名};
データ操作言語（DML）
データ操作言語（DML：Data Manipulation Language）は、データベースに格納されたデータを操作するためのSQLのカテゴリです。DMLを用いることで、データの取得（検索）、追加、更新、削除といった、データベースにおける基本的なデータ操作を行うことができます。
レコードの取得・検索 (SELECT)
例1:SQL
以下のSQL文は、国民の祝日と会社記念日を組み合わせて日付順に取得するクエリです。 年度開始日と年度終了日をパラメータとして、指定期間内の祝日と会社記念日を抽出しています。 UNION ALLで二つのSELECT文の結果を結合し、最終結果を日付で昇順にソートします。
SELECT
祝日 AS 日付, -- 祝日テーブルの「祝日」列を「日付」という別名で選択
祝日名 AS 日付名 -- 祝日テーブルの「祝日名」列を「日付名」という別名で選択
FROM
国民の祝日 -- データ取得元のテーブルとして「国民の祝日」テーブルを指定
WHERE
祝日 BETWEEN :年度開始日 AND :年度終了日 -- 祝日列が指定された年度開始日と年度終了日の間にあるレコードを抽出
UNION ALL -- 上記のSELECT結果と以下のSELECT結果を重複を含めて結合
SELECT
会社記念日 AS 日付, -- 会社記念日テーブルの「会社記念日」列を「日付」という別名で選択
会社記念日名 AS 日付名 -- 会社記念日テーブルの「会社記念日名」列を「日付名」という別名で選択
FROM
会社記念日 -- データ取得元のテーブルとして「会社記念日」テーブルを指定
WHERE
会社番号 = :会社番号 -- 会社番号列が指定された会社番号と一致するレコードを抽出
AND 会社記念日 BETWEEN :年度開始日 AND :年度終了日 -- かつ、会社記念日列が指定された年度開始日と年度終了日の間にあるレコードを抽出
ORDER BY
日付; -- 最終的な結果セットを「日付」列で昇順に並び替え
E-R図
このE-R図は会社記念日と国民の祝日のデータ構造を表しています。 会社記念日と国民の祝日それぞれがエンティティとして定義され、複合主キーを持ちます。 各エンティティは日付と名称を持ち、日付に関連する情報を管理するシンプルな構造であることがわかります。
BETWEEN演算子
以下のSQL文は、BETWEEN演算子を使った条件指定の構文を示しています。 BETWEEN演算子は、WHERE句で指定したカラムの値が、開始値と終了値の範囲内にあるレコードを抽出します。 日付カラムだけでなく、数値や文字列カラムにも使用可能です。
WHERE
日付カラム BETWEEN 開始日 AND 終了日 -- 日付カラムが指定された開始日と終了日の間にあるレコードを抽出
例2:SQL
このSQLは一時テーブルからデータを取得し組織あたりの平均目標値と平均実績値を算出するSELECT文です。 組織ごと_目標実績集計_一時テーブルから全カラムを取得し、目標組織集計と実績組織集計を対象従業員数で割った値を計算しています。 これにより組織ごとの目標と実績を従業員数で平均化した値を確認でき、組織単位でのパフォーマンス比較などが容易になります。
SELECT
A.*, -- 組織ごと_目標実績集計_一時テーブルの全てのカラムを取得
目標組織集計 / A.対象従業員数, -- 目標組織集計を対象従業員数で割った値 (組織あたりの平均目標値)
実績組織集計 / A.対象従業員数 -- 実績組織集計を対象従業員数で割った値 (組織あたりの平均実績値)
FROM
組織ごと_目標実績集計_一時 A -- 組織ごと_目標実績集計_一時テーブルをエイリアスAとして参照
ORDER BY
A.組織コード, -- 組織コードでソート
A.KPIコード; -- さらにKPIコードでソート
例3:SQL
このSQLは一括購入IDを基に一括購入数量を取得するクエリです。 一括購入テーブルから一括購入IDが指定されたパラメータと一致するレコードを検索します。 取得されるのは該当レコードの一括購入数量カラムの値です。
-- 一括購入IDに基づいて一括購入数量を取得するクエリ
SELECT
一括購入数量 -- 取得するカラム: 一括購入数量
FROM
一括購入 -- 対象テーブル: 一括購入テーブル
WHERE
一括購入ID = :一括購入ID; -- 絞り込み条件: 一括購入IDがパラメータと一致するレコード
例4:SQL
このSQLは一括購入IDに紐づく一括購入割当レコード数を取得するクエリです。 一括購入割当テーブルから、パラメータで指定された一括購入IDに一致するレコードを絞り込みます。 COUNT(*)関数により、該当するレコードの件数が結果として返されます。
-- 一括購入IDに紐づく一括購入割当レコードの件数を取得するクエリ
SELECT
COUNT(*) -- 取得するカラム: レコード件数
FROM
一括購入割当 -- 対象テーブル: 一括購入割当テーブル
WHERE
一括購入ID = :一括購入ID; -- 絞り込み条件: 一括購入IDがパラメータと一致するレコード
例5:SQL
このSQLはROOMスキーマの入室許可テーブルからレコード数をカウントするクエリです。 特定の社員IDと室IDで絞り込み、さらに許可期間が今日を含むレコードを対象としています。 このクエリは、指定された社員が特定の部屋に今日入室許可されている件数を取得します。
SELECT
COUNT(*) -- 該当するレコードの件数を数える関数。このクエリの結果として、条件に合致する入室許可の件数が返される。
FROM
ROOM.入室許可 -- 入室許可テーブル: 社員ごとの入室許可情報を持つテーブル
WHERE
社員 ID = :社員 ID -- 特定の社員IDで絞り込む条件。":社員 ID" はパラメータであり、実行時に具体的な社員IDが設定される。
AND 室 ID = :室 ID -- 特定の室IDで絞り込む条件。":室 ID" はパラメータであり、実行時に具体的な室IDが設定される。
AND 入室許可開始年月日 <= :今日 -- 入室許可期間の開始日が今日以前である条件。":今日" はパラメータであり、実行時の日付が設定される。今日以前に許可が開始されている必要がある。
AND 入室許可終了年月日 >= :今日 -- 入室許可期間の終了日が今日以降である条件。":今日" はパラメータであり、実行時の日付が設定される。今日以降も許可が有効である必要がある。
; -- SQL文の終端を表す記号
JOIN句
JOIN句はSQLで複数テーブルを結合するために使用します。 テーブル結合によって、関連するデータを一つの結果セットとして取得できます。
例1:SQL
このSQLは従業員テーブルと部署テーブルを内部結合し、関連する従業員と部署の情報を取得するクエリです。 結合条件は会社番号と部署番号が一致することです。 指定された会社番号と管理者番号でデータを絞り込み、部署番号と従業員番号でソートして結果を返します。
SELECT
DEP.部署番号, -- 部署テーブル (DEP) から部署番号を選択
DEP.部署名, -- 部署テーブル (DEP) から部署名を選択
EMP.従業員番号, -- 従業員テーブル (EMP) から従業員番号を選択
EMP.従業員氏名 -- 従業員テーブル (EMP) から従業員氏名を選択
FROM
従業員 EMP -- FROM句: データ取得元として従業員テーブルを指定
INNER JOIN
部署 DEP -- 従業員テーブルに 部署テーブルを内部結合
ON EMP.会社番号 = DEP.会社番号 -- JOIN条件1: 従業員テーブルの会社番号と部署テーブルの会社番号が一致する
AND EMP.部署番号 = DEP.部署番号 -- JOIN条件2: 従業員テーブルの部署番号と部署テーブルの部署番号が一致する
WHERE
EMP.会社番号 = :会社番号 -- WHERE条件1: 従業員テーブルの会社番号がパラメータ「:会社番号」と一致するレコードに絞り込む
AND DEP.管理者番号 = :管理者番号 -- WHERE条件2: 部署テーブルの管理者番号がパラメータ「:管理者番号」と一致するレコードに絞り込む
ORDER BY
DEP.部署番号, -- ORDER BY句: 結果セットを部署番号で昇順にソート (第一ソートキー)
EMP.従業員番号 -- さらに従業員番号で昇順にソート (第二ソートキー)
;
このE-R図は、従業員と部署のエンティティ間の関係を示しています。従業員は必ず一つの部署に所属し、一人の従業員が複数の部署を管理できます。
E-R図
例2:SQL
このSQLクエリは、特定の社員が購入した全ての書籍IDを抽出します。社員が直接購入した書籍IDと、一括購入によって割り当てられた書籍IDをUNION句で結合し、重複を除いて取得します。これにより、社員の購入履歴を網羅的に把握できます。
-- 社員が購入した書籍IDと、一括購入で割り当てられた書籍IDを結合して取得するクエリ
-- 1つ目のSELECT文: 社員書籍購入テーブルから社員が直接購入した書籍IDを取得
SELECT
sk.書籍ID -- 取得するカラム: 書籍ID (社員書籍購入テーブルから)
FROM
社員書籍購入 sk -- 対象テーブル: 社員書籍購入テーブル (エイリアス: sk)
WHERE
sk.企業ID = :企業ID -- 絞り込み条件: 企業IDがパラメータと一致
AND sk.社員ID = :社員ID -- 絞り込み条件: 社員IDがパラメータと一致
UNION -- 2つのSELECT文の結果を重複を除いて結合
-- 2つ目のSELECT文: 一括購入テーブルと一括購入割当テーブルを結合して、社員に割り当てられた書籍IDを取得
SELECT
ik.書籍ID -- 取得するカラム: 書籍ID (一括購入テーブルから)
FROM
一括購入 ik -- 対象テーブル: 一括購入テーブル (エイリアス: ik)
INNER JOIN -- 内部結合: 一致するレコードのみ結合
一括購入割当 iw ON ik.一括購入ID = iw.一括購入ID -- 結合条件: 一括購入IDが一致するレコード
WHERE
ik.企業ID = :企業ID -- 絞り込み条件: 一括購入テーブルの企業IDがパラメータと一致
AND iw.社員ID = :社員ID; -- 絞り込み条件: 一括購入割当テーブルの社員IDがパラメータと一致
E-R図
このER図は、社員による書籍購入と一括購入に関するデータベース設計を示しています。社員、社員書籍購入、一括購入、一括購入割当の4つのエンティティで構成され、それぞれのエンティティは主キーと属性を持ちます。社員は書籍を個別購入または一括購入割当によって書籍を入手でき、購入情報は関連テーブルで管理されます。
例3:SQL
このSQLクエリは、貸出予約と実績データから、遅延返却の発生件数を集計するものです。まず、サブクエリで貸出予約テーブルから日付を抽出し、貸出実績テーブルと結合して遅延返却があったレコードを絞り込みます。最後に、貸出予定年月日、駐車場ID、車種ID、会員IDごとにグループ化し、遅延返却件数を算出します。
SELECT
R.貸出予定年月日, -- SELECT句: 貸出予定年月日 (集計キーの一部)
R.駐車場ID, -- SELECT句: 駐車場ID (集計キーの一部)
R.車種ID, -- SELECT句: 車種ID (集計キーの一部)
R.会員ID, -- SELECT句: 会員ID (集計キーの一部)
COUNT(*) AS 遅延返却発生件数 -- SELECT句: 遅延返却発生件数 (集計結果)
FROM
(
SELECT
Y.貸出予約コード, -- サブクエリSELECT句
Y.駐車場ID, -- サブクエリSELECT句
Y.車種ID, -- サブクエリSELECT句
Y.会員ID, -- サブクエリSELECT句
TIMESTAMP_TO_DATE(Y.貸出予定時刻) AS 貸出予定年月日, -- サブクエリSELECT句: 貸出予定時刻を日付型に変換して貸出予定年月日として取得
Y.返却予定時刻 -- サブクエリSELECT句
FROM
貸出予約 Y -- サブクエリFROM句: 貸出予約テーブルを参照 (エイリアス: Y)
) R -- FROM句: サブクエリの結果にエイリアス R を付与
INNER JOIN
貸出実績 J ON R.貸出予約コード = J.貸出予約コード -- 貸出実績テーブルと 貸出予約テーブルを貸出予約コードで結合
WHERE
R.返却予定時刻 < J.返却実績時刻 -- WHERE句: 遅延返却を判定する条件 (返却予定時刻 < 返却実績時刻)
GROUP BY
R.貸出予定年月日, -- GROUP BY句: 貸出予定年月日でグループ化
R.駐車場ID, -- GROUP BY句: 駐車場IDでグループ化
R.車種ID, -- GROUP BY句: 車種IDでグループ化
R.会員ID; -- GROUP BY句: 会員IDでグループ化
E-R図
このER図は、レンタカーの貸出予約と実績を管理するデータベース設計です。「貸出予約」エンティティは予約情報を管理し、予約コード、駐車場、車種、会員、予約・予定時刻などの属性を持ちます。「貸出実績」エンティティは実績情報を管理し、実績コード、予約コード、車両、実績時刻、料金などの属性を持ち、貸出予約と関連付けられます。
WITH句
WITH句 は、SQLクエリ内で一時的な名前付き結果セット（共通テーブル式: CTE）を定義するための構文です。これにより、複雑なクエリを小さな部品に分割し、構造化して記述できます。
WITH RECURSIVE句 は、WITH句の拡張機能で、再帰的な処理を可能にします。これは、階層構造を持つデータ（組織図、部品構成、グラフ構造など）を扱う場合に非常に強力なツールとなります。
例1:SQL
このSQLクエリは、指定されたカテゴリIDとその上位カテゴリを再帰的に取得し、これらのカテゴリに属する出品情報を抽出します。WITH RECURSIVE句で定義された「指定カテゴリ」共通テーブル式は、指定カテゴリIDを起点に、上位カテゴリIDを辿って再帰的に親カテゴリを取得します。メインクエリでは、「指定カテゴリ」と出品テーブルを結合し、指定された価格範囲、商品状態、出品状況、キーワードに合致する出品情報を絞り込みます。
WITH RECURSIVE 指定カテゴリ (カテゴリID, カテゴリ名, 上位カテゴリID) AS (
-- 再帰的な共通テーブル式 (CTE) 「指定カテゴリ」を定義
SELECT
A.カテゴリID,
A.カテゴリ名,
A.上位カテゴリID
FROM
カテゴリ A
WHERE
A.カテゴリID = :カテゴリID
-- パラメータ「:カテゴリID」で指定されたカテゴリIDを持つカテゴリを初期値として選択 (再帰の開始点)
UNION ALL
-- UNION ALL は、結果セットを重複を含めて結合 (UNION は重複を排除)
SELECT
B.カテゴリID,
B.カテゴリ名,
B.上位カテゴリID
FROM
カテゴリ B
INNER JOIN 指定カテゴリ C ON B.上位カテゴリID = C.カテゴリID
-- カテゴリテーブルB と CTE「指定カテゴリ」C を結合 (Bの「上位カテゴリID」がCの「カテゴリID」と一致するレコードを結合)
-- これにより、指定されたカテゴリの親カテゴリを再帰的に取得していく
)
-- メインクエリ (CTE「指定カテゴリ」の結果を利用)
SELECT
*
FROM
出品
INNER JOIN 指定カテゴリ
ON 出品.カテゴリID = 指定カテゴリ.カテゴリID
-- 出品の「カテゴリID」が「指定カテゴリ」の「カテゴリID」と一致するレコードを結合
WHERE
出品.出品価格 BETWEEN :下限価格 AND :上限価格
-- 出品価格がパラメータ「:下限価格」と「:上限価格」の範囲内である
AND 出品.商品状態 = :商品状態
-- 商品状態がパラメータ「:商品状態」と一致する
AND 出品.出品状況 = :出品状況
-- 出品状況がパラメータ「:出品状況」と一致する
AND (出品.商品名 LIKE '%' || :キーワード || '%' OR 出品.商品説明 LIKE '%' || :キーワード || '%')
-- 商品名 または 商品説明 にパラメータ「:キーワード」が含まれる (部分一致検索)
E-R図
このE-R図は、出品システムにおけるカテゴリと出品情報を管理するデータベース設計を示すE-R図です。E-R図では、カテゴリが階層構造を持ち、出品情報がカテゴリに紐づけられる関係性が表現されています。
以下のSQL構文は、LIKE演算子を用いて文字列の部分一致検索を行うためのものです。「'%' || :キーワード || '%'」という記述により、パラメータ 「:キーワード」 の前後にワイルドカード 「%」 を結合し、検索パターンを動的に生成しています。このパターンをLIKE演算子に適用することで、指定されたキーワードが文字列の任意の位置に含まれるレコードを効率的に抽出できます。
LIKE '%' || :キーワード || '%'
例2:SQL
このSQLクエリは、過去半年間の処方歴と今回の処方内容を基に、薬剤の併用禁忌をチェックするものです。WITH句で過去半年間に処方された薬剤と今回の処方薬剤をリスト化し、UNION句でそれらを結合してチェック対象薬剤リストを作成します。メインクエリでは、薬剤併用情報テーブルを用いて、作成されたリスト内の薬剤ペアに禁忌情報が存在するかをEXISTS句で確認し、併用禁忌の可能性のある薬剤情報を抽出します。
WITH チェック対象薬剤 AS ( -- チェック対象薬剤 CTE 開始
-- チェック対象薬剤を特定するための共通テーブル式 (CTE)
SELECT
B1.薬剤コード
FROM
処方箋明細 B1,
(
-- --- サブクエリ: 過去一定期間の処方箋IDを抽出 ---
SELECT
A1.処方箋ID
FROM
外来受診 A1,
処方箋 A2
WHERE
A1.受診者ID = :受診者ID
AND A1.処方箋ID = A2.処方箋ID
AND A2.発行年月日 >= TO_DATE(:半年前年月日)
) B2
WHERE
B1.処方箋ID = B2.処方箋ID
UNION
-- --- SELECT句: 今回の処方箋の薬剤コードを取得 ---
SELECT
C1.薬剤コード
FROM
処方箋明細 C1
WHERE
C1.処方箋ID = :処方箋 ID
) -- チェック対象薬剤 CTE 終了
-- --- SELECT句: 薬剤併用情報を検索し、併用禁忌をチェック ---
SELECT
*
FROM
薬剤併用情報 T1
WHERE EXISTS (
-- --- サブクエリ: チェック対象薬剤の組み合わせを生成 ---
SELECT
T2.薬剤コード１,
T2.薬剤コード２
FROM
(
-- --- サブクエリ: チェック対象薬剤のクロス結合 ---
SELECT
U1.薬剤コード AS 薬剤コード１,
U2.薬剤コード AS 薬剤コード２
FROM
チェック対象薬剤 U1
CROSS JOIN チェック対象薬剤 U2
) T2
WHERE
T1.薬剤コード１ = T2.薬剤コード１
AND T1.薬剤コード２ = T2.薬剤コード２
);
E-R図
このE-R図は、医療機関における外来受診と処方箋、そして薬剤に関するデータベース構造を表しています。 外来受診情報を中心に、処方箋、処方箋明細、薬剤、薬剤併用情報の各エンティティが関連付けられ、患者の受診から薬剤処方、禁忌情報までを管理する仕組みを示しています。 各エンティティは属性と主キーを持ち、リレーションシップを通じてデータが効率的に管理・参照できるように設計されています。
WINDOW句
WINDOW句 は、ウィンドウ関数が「仮想的な行の集合」（ウィンドウフレーム）を操作して計算を行うための「計算方法（ルール）」を定義する構文要素。WINDOW句自体が値を出すわけではなく、OVER句 とウィンドウ関数 (AVG, SUM など) が組み合わさることで、定義されたルールに従って特定の値を計算し、結果として表示します。SELECT 文の構文の一部であり、SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY などと並ぶ、SELECT 文を構成する要素の一つです。
例1:SQL
このSQLクエリは、在庫推移状況テーブルから、ウィンドウ関数を用いて集計処理を行うものです。 倉庫コードと商品コードでグループ化し、直近7日間の平均在庫数と期間内売上個数を算出しています。 結果として、日付、倉庫コード、商品コードと、計算された平均在庫数、期間内売上個数がリスト表示されます。
SELECT
年, -- 年
月, -- 月
日, -- 日
倉庫コード, -- 倉庫コード
商品コード, -- 商品コード
AVG(在庫数) OVER 期間定義 AS 平均在庫数, -- 期間定義に基づいて計算された平均在庫数を「平均在庫数」という別名で取得
SUM(売上個数) OVER 期間定義 AS 期間内売上個数 -- 期間定義に基づいて計算された期間内売上個数を「期間内売上個数」という別名で取得
FROM
在庫推移状況 -- 在庫推移状況テーブルからデータを取得
WINDOW
期間定義 AS ( -- 「期間定義」という名前でウィンドウ定義
PARTITION BY -- パーティション定義：倉庫コードと商品コードが同じレコードをグループ化
倉庫コード,
商品コード
ORDER BY -- ソート順定義：年、月、日 の昇順でレコードをソート
年,
月,
日 ASC
ROWS BETWEEN -- フレーム定義：現在行を含む過去6行を分析対象とする
6 PRECEDING AND CURRENT ROW
);
WINDOW句は、ウィンドウ関数の計算範囲を定義するウィンドウ定義に名前を与える構文です。 WINDOW ウィンドウ名 AS ( ... ) の形式で定義し、PARTITION BY句やORDER BY句、フレーム句などで詳細な計算範囲を設定できます。 定義したウィンドウ名はOVER句で再利用でき、SQLの可読性と保守性を向上させます。
WINDOW
ウィンドウ名 AS ( -- ウィンドウ関数で使用するウィンドウの定義に「ウィンドウ名」という名前を付ける
[PARTITION BY -- 【オプション】パーティション定義：グループ化するカラムを指定
パーティションカラム_1,
パーティションカラム_2,
...]
[ORDER BY -- 【オプション】ソート順定義：ソート順を指定するカラムと順序 (ASC/DESC) を指定
ソートカラム_1 [ASC|DESC],
ソートカラム_2 [ASC|DESC],
...]
[フレーム句] -- 【オプション】フレーム定義：分析対象とする行の範囲を指定 (ROWS/RANGE BETWEEN など)
);
WINDOW句の一般化されたアルゴリズム表
| 手順 | 処理内容 | 記載例 |
|---|---|---|
| 1 | テーブルから 1行ずつ 処理を開始。 | |
| 2 | パーティション特定: 現在行の パーティションキー をもとに、同じキーを持つ行で区分け。 |
PARTITION BY パーティションカラム_1, ... 例：倉庫コード と 商品コード の 組み合わせ が同じ行を、一つのまとまり (パーティション) として扱う。 |
| 3 | パーティション内ソート: パーティション内の行を ソートキー で順序付け。 |
ORDER BY ソートカラム_1 [ASC or DESC], ... 例：年, 月, 日 の昇順でソート。 |
| 4 | フレーム決定: 現在行を基準に、フレーム句 に従って計算対象範囲 (ウィンドウフレーム) を決定。 |
フレーム句 (例: ROWS BETWEEN ... AND ...) 例：ROWS BETWEEN 6 PRECEDING AND CURRENT ROW で過去7日間を範囲指定。 |
| 5 | ウィンドウ関数計算: 決定されたフレーム内のデータに対し、指定されたウィンドウ関数 を計算。 |
ウィンドウ関数 (例: AVG(集計対象カラム)) 例：AVG(在庫数) で平均在庫数を計算。 |
相関副問い合わせ
サブクエリが、外側のクエリ（親クエリ、メインクエリ）の行に依存して実行される副問い合わせのことです。
例1:SQL
以下のSQL文は、指定された施設と部屋種別において、特定期間中に予約がない部屋を検索します。 NOT EXISTS句で予約明細テーブルをサブクエリし、期間中に予約がある部屋を除外しています。 最後に、GROUP BY と HAVING句で施設IDと部屋種別IDごとに部屋数を集計し、指定された部屋数以上の部屋を抽出します。
-- SELECT文: 条件に合致する施設IDと部屋種別ID、部屋数を取得するSQL文
SELECT
施設ID, -- 施設ID: 部屋テーブルから施設IDを取得 (グループ化キー)
部屋種別ID, -- 部屋種別ID: 部屋テーブルから部屋種別IDを取得 (グループ化キー)
COUNT(*) -- COUNT(*): グループごとの部屋数を数える集計関数 (HAVING句で部屋数による絞り込みに使用)
FROM
部屋 -- 部屋テーブル: 部屋の情報を格納するテーブル
WHERE
NOT EXISTS ( -- NOT EXISTS句: サブクエリに該当する部屋が存在しないことを条件とする (予約がない部屋を抽出)
-- サブクエリ開始: 指定期間中に予約がある部屋を検索する
SELECT
* -- 予約明細テーブルからすべてのカラムを選択 (EXISTS句ではカラムの内容は評価されないため、* で可)
FROM
予約明細 -- 予約明細テーブル: 部屋の予約情報を格納するテーブル
WHERE
予約明細.部屋ID = 部屋.部屋ID -- 相関条件1: 外側のクエリ (部屋テーブル) の部屋IDと、内側のクエリ (予約明細テーブル) の部屋IDを紐付ける (部屋ごとの予約状況をチェックするため)
AND 予約明細.宿泊日 >= :チェックイン日付 -- 予約明細の宿泊日が、パラメータ ":チェックイン日付" 以降である条件 (指定期間の開始日以降の予約)
AND 予約明細.宿泊日 < :チェックアウト日付 -- 予約明細の宿泊日が、パラメータ ":チェックアウト日付" より前である条件 (指定期間の終了日より前の予約。チェックアウト日は含まない)
-- サブクエリ終了: 指定期間中に予約がある部屋が存在するかどうかを判定
)
AND 施設ID = :施設ID -- WHERE句の条件1: 施設IDがパラメータ ":施設ID" と一致する部屋に絞り込む (特定の施設を指定)
AND 部屋種別ID = :部屋種別ID -- WHERE句の条件2: 部屋種別IDがパラメータ ":部屋種別ID" と一致する部屋に絞り込む (特定の部屋種別を指定)
GROUP BY
施設ID, -- GROUP BY句: 施設IDと部屋種別IDでグループ化 (施設IDと部屋種別IDの組み合わせごとに集計)
部屋種別ID -- GROUP BY句
HAVING
COUNT(*) >= :部屋数; -- HAVING句: グループ化された部屋数 (COUNT(*)) がパラメータ ":部屋数" 以上であるグループのみを抽出
E-R図
このE-R図は、宿泊施設の部屋と予約に関するデータベース設計を示しています。 部屋エンティティと予約明細エンティティは部屋IDを介して関連付けられ、一部屋に対して複数の予約明細が存在しうる関係性を示唆しています。
例2:SQL
このSQLクエリは、予約明細テーブルから、予約ID、予約明細ID、部屋ID、宿泊日を取得します。ただし、同じ部屋IDかつ同じ宿泊日の予約明細の中で、最小の予約IDよりも大きい予約IDを持つレコードのみが選択されます。相関サブクエリを利用して、各部屋ID・宿泊日ペアにおける最小予約IDを特定し、それより後の予約明細を抽出する仕組みです。
-- SELECT文: 予約明細テーブルから特定のレコードを選択するSQL文
SELECT
t1.予約 ID, -- 予約明細テーブル t1 から予約IDを取得
t1.予約明細 ID, -- 予約明細テーブル t1 から予約明細IDを取得
t1.部屋 ID, -- 予約明細テーブル t1 から部屋IDを取得
t1.宿泊日 -- 予約明細テーブル t1 から宿泊日を取得
FROM
予約明細 t1 -- メインクエリで参照する予約明細テーブル
WHERE
t1.予約 ID > ( -- WHERE句: 予約IDがサブクエリの結果より大きいレコードを絞り込む
-- 相関サブクエリ開始: 同じ部屋IDと宿泊日の予約明細の中で最小の予約IDを取得
SELECT
MIN(t2.予約 ID) -- 同じ部屋IDと宿泊日の予約明細の中で最小の予約IDを求める集計関数
FROM
予約明細 t2 -- サブクエリ内で参照する予約明細テーブル (t1 と同じテーブルだが別名で区別)
WHERE
t1.部屋 ID = t2.部屋 ID -- 相関条件1: メインクエリの t1 とサブクエリの t2 で部屋IDが一致するレコードを絞り込む
-- (同じ部屋に関する予約明細を比較するため)
AND t1.宿泊日 = t2.宿泊日 -- 相関条件2: メインクエリの t1 とサブクエリの t2 で宿泊日が一致するレコードを絞り込む
-- (同じ宿泊日の予約明細を比較するため)
-- サブクエリ終了: 同じ部屋IDと宿泊日の予約明細の中で最小の予約IDが返される
); -- WHERE句終了: メインクエリの予約IDがサブクエリの結果 (最小予約ID) より大きいレコードが最終的に選択される
この ER図は、予約明細テーブルの構造を表しています。主キーは予約明細IDで、外部キーとして予約IDと部屋IDを持っています。さらに、宿泊日と宿泊料カラムが存在し、予約明細が部屋と宿泊日に紐づいていることを示唆しています。
相関副問い合わせの考え方 - EXISTS の場合
| ステップ | 処理内容 | 備考 |
|---|---|---|
| 1 | 外側のクエリの行を取得 | 外側のクエリ（メインクエリ）から、まだ処理されていない最初の行を取り出します。 |
| 2 | 副問い合わせを実行 (相関) | ステップ1で取得した外側のクエリの現在の行を参照しながら、副問い合わせを実行します。 |
| 3 | 副問い合わせの結果を評価 (EXISTS) | EXISTSは副問い合わせの結果セットの行数のみをチェックし、具体的な値は評価しません。 副問い合わせが1行以上であればTRUE、0行であればFALSEとなります。 |
| 4 | 行を結果セットに追加するか判定 | ステップ3の評価結果に基づいて、現在の外側のクエリの行を最終的な結果セットに含めるかどうかを決定します。EXISTS が 真 (TRUE) の場合、その行は結果セットに追加されます。EXISTS が 偽 (FALSE) の場合は追加されません。 |
| 5 | 次の行を処理 | 外側のクエリにまだ処理されていない行が存在する場合、ステップ1に戻り、次の行に対してステップ2から4の処理を繰り返します。 |
| 6 | 終了 (結果セットを返す) | 外側のクエリのすべての行を処理し終えたら、アルゴリズムは終了します。最終的に、ステップ4で結果セットに追加された行が集められ、クエリの結果として返されます。 |
レコードの追加 (INSERT)
INSERT 文は、テーブルに新しい行（レコード）を挿入するためのSQL文です。
例1:SQL
以下のSQL文は、所属テーブルと役職テーブルを結合し、指定された集計年月日時点で特定の職務区分の役職に就いている従業員の従業員コードと所属組織コードを抽出しています。抽出されたデータは、一時テーブルである従業員_所属_一時テーブルに挿入されます。WHERE句では、集計年月日が所属期間内であること、役職コードが一致すること、そして職務区分が'02'であることが条件として指定されています。
-- 従業員_所属_一時テーブルにデータを挿入する
INSERT INTO 従業員_所属_一時 (従業員コード, 組織コード)
SELECT
-- SELECT句: 従業員コードと所属組織コードを選択する
A.従業員コード, -- 所属テーブル(A)から従業員コードを選択
A.所属組織コード -- 所属テーブル(A)から所属組織コードを選択
FROM
-- FROM句: データ取得元のテーブルを指定
所属 AS A, -- 所属テーブルをエイリアスAとして指定
役職 AS B -- 役職テーブルをエイリアスBとして指定
WHERE
-- WHERE句: データ抽出の条件を指定
TO_DATE(:集計年月日) BETWEEN A.所属開始年月日 AND A.所属終了年月日 -- 集計年月日が所属期間内である条件 (所属テーブルAの所属開始年月日〜所属終了年月日の間)
AND A.役職コード = B.役職コード -- 所属テーブル(A)の役職コードと役職テーブル(B)の役職コードが一致する条件 (テーブル結合条件)
AND B.職務区分 = '02' -- 役職テーブル(B)の職務区分が'02'である条件 (役職の絞り込み条件)
E-R図
このE-R図は、従業員の所属情報と役職情報を管理するデータベース構造を示しています。 所属テーブルは従業員の所属期間、組織、役職を記録し、従業員コードと所属開始年月日を主キーとしています。役職テーブルは役職コードを主キーとし、役職名と職務区分を管理し、所属テーブルと役職コードで関連付けられています。
例2:SQL
以下のSQL文は、月別個人目標テーブルから、指定された年度開始年月から集計年月までの期間における、従業員別かつKPIコード別の月別目標値の合計を計算しています。集計結果は、従業員コード、KPIコード、そして計算された目標個人集計値としてまとめられます。最後に、これらの集計されたデータは、従業員ごと_目標集計_一時テーブルに新しいレコードとして挿入されます。
-- 従業員ごと_目標集計_一時テーブルにデータを挿入する
INSERT INTO 従業員ごと_目標集計_一時 (従業員コード, KPIコード, 目標個人集計)
SELECT
-- SELECT句: 従業員コード、KPIコード、月別目標値の合計を選択する
従業員コード, -- 月別個人目標テーブルから従業員コードを選択
KPIコード, -- 月別個人目標テーブルからKPIコードを選択
SUM(月別目標値) -- 月別個人目標テーブルの月別目標値の合計を計算 (集計関数)
FROM
-- FROM句: データ取得元のテーブルを指定
月別個人目標 -- 月別個人目標テーブルを指定
WHERE
-- WHERE句: データ抽出の条件を指定
年月 BETWEEN :年度開始年月 AND :集計年月 -- 年月が年度開始年月〜集計年月の期間内である条件 (期間絞り込み)
GROUP BY
-- GROUP BY句: 集計のグループ化を指定
従業員コード, -- 従業員コードでグループ化
KPIコード -- KPIコードでグループ化
例3:SQL
以下のSQL文は、日別個人実績テーブルから、指定された年度開始年月から集計年月までの期間における、従業員別かつKPIコード別の日別実績値の合計を計算します。 集計された実績値は、従業員コードとKPIコードごとにグループ化され、従業員ごとのKPI別実績集計としてまとめられます。 最後に、集計結果を従業員ごと_実績集計_一時テーブルに、従業員コード、KPIコード、実績個人集計として挿入します。
-- 従業員ごと_実績集計_一時 テーブルにデータを挿入します。
INSERT INTO 従業員ごと_実績集計_一時
-- 挿入するカラムを列挙します (従業員コード, KPIコード, 実績個人集計)。
(従業員コード, KPIコード, 実績個人集計)
-- データを選択する SELECT ステートメントを開始します。
SELECT
-- 従業員コードを選択します。
従業員コード,
-- KPIコードを選択します。
KPIコード,
-- 日別実績値 の合計を計算します (実績個人集計 として挿入されます)。
SUM(日別実績値)
-- データ取得元のテーブルを指定します (日別個人実績 テーブル)。
FROM
日別個人実績
-- データ抽出の条件を指定する WHERE 句を開始します。
WHERE
-- 年月日 が指定された期間内にあるレコードを抽出します。
年月日 BETWEEN TO_DATE(:年度開始年月日) AND TO_DATE(:集計年月日)
-- 結果をグループ化する GROUP BY 句を開始します。
GROUP BY
従業員コード,
-- 従業員コードとKPIコードの組み合わせごとに集計。
KPIコード
例4:SQL
このSQLクエリは、従業員ごとの目標と実績を集計した一時テーブルと従業員所属情報を組み合わせ、組織単位でのKPI目標と実績を集計します。 組織コードとKPIコードごとに、目標個人集計と実績個人集計の合計を算出し、さらに各組織・KPIペアに該当する従業員数をカウントします。 集計結果は組織ごと_目標実績集計_一時テーブルに格納され、組織レベルでの目標達成状況分析に活用されます。
-- 組織ごと_目標実績集計_一時 テーブルにデータを挿入する処理を開始します。
INSERT INTO 組織ごと_目標実績集計_一時
-- 挿入先のカラムリストを指定します (組織コード, KPIコード, 目標組織集計, 実績組織集計, 対象従業員数)。
(組織コード, KPIコード, 目標組織集計, 実績組織集計, 対象従業員数)
-- データを選択するための SELECT ステートメントを開始します。
SELECT
-- 組織コードをテーブル A (従業員_所属_一時) から選択します。
A.組織コード,
-- KPIコードをテーブル B (従業員ごと_目標集計_一時) から選択します。
B.KPIコード,
-- テーブル B (従業員ごと_目標集計_一時) の 目標個人集計 カラムの合計を計算します (組織全体の目標集計)。
SUM(B.目標個人集計),
-- テーブル C (従業員ごと_実績集計_一時) の 実績個人集計 カラムの合計を計算します (組織全体の実績集計)。
-- COALESCE関数で C.実績個人集計 が NULL の場合に 0 に置き換えます。
SUM(COALESCE(C.実績個人集計, 0)),
-- 対象となる従業員数をカウントします (各組織とKPIの組み合わせに該当する従業員数)。
COUNT(*)
-- データ取得元のテーブルを指定します (従業員_所属_一時 テーブルをエイリアス A として使用)。
FROM
従業員_所属_一時 A
-- 従業員_所属_一時 (A) と 従業員ごと_目標集計_一時 (B) を INNER JOIN で結合します。
INNER JOIN
従業員ごと_目標集計_一時 B
-- JOIN の条件: テーブル A と テーブル B の 従業員コード が一致する行を結合します。
ON A.従業員コード = B.従業員コード
-- INNER JOIN 後のテーブルに LEFT OUTER JOIN で 従業員ごと_実績集計_一時 (C) を結合します。
LEFT OUTER JOIN
従業員ごと_実績集計_一時 C
-- JOIN の条件 1: テーブル B と テーブル C の 従業員コード が一致する行を結合します。
ON B.従業員コード = C.従業員コード
-- JOIN の条件 2: テーブル B と テーブル C の KPIコード が一致する行を結合します (AND で条件を追加)。
AND B.KPIコード = C.KPIコード
-- 結果をグループ化します。
GROUP BY
A.組織コード,
-- (組織コードとKPIコードの組み合わせごとに集計
B.KPIコード
このE-R図は、組織ごとの目標実績集計を行うために使用される一時テーブル間の関連性を示しています。 従業員_所属_一時テーブルは従業員の所属組織を、従業員ごと_目標集計_一時テーブルと従業員ごと_実績集計_一時テーブルはそれぞれ従業員ごとのKPI目標と実績を集計した結果を保持します。
例5:SQL
以下のSQL文は、新しいクーポンを発行し、その情報をクーポン明細テーブルに登録する処理を行います。 指定されたクーポンコードに基づき、発行済みのクーポン枚数を参照し、連番を付与して新しいクーポンを発行します。 発行時には、発行上限枚数を超えないことを確認し、クーポン管理テーブルから獲得制限等の情報を取得して、クーポン明細レコードを生成します。
INSERT INTO クーポン明細 -- クーポン明細テーブルにデータを挿入する
(クーポンコード, クーポン発行連番, 獲得会員コード, 獲得制限_1枚限り) -- 挿入対象列: クーポンコード, クーポン発行連番, 獲得会員コード, 獲得制限_1枚限り
WITH -- CTE (Common Table Expression, 共通テーブル式) の定義開始
発行済枚数取得 AS ( -- CTE名: 発行済枚数取得
SELECT -- 発行済枚数を取得するSELECT句
COALESCE(MAX(クーポン発行連番), 0) AS 発行済枚数 -- 発行済枚数として、クーポン発行連番の最大値を取得。NULLの場合は0とする
FROM -- FROM句: データ取得元はクーポン明細テーブル
クーポン明細
WHERE -- WHERE句: 絞り込み条件
クーポンコード = :クーポンコード -- クーポンコードがパラメータ「:クーポンコード」と一致するレコード
) -- CTE 発行済枚数取得 の定義 終了
SELECT -- 挿入するデータを選択するSELECT句
:クーポンコード, -- 1列目: パラメータ「:クーポンコード」の値をそのまま使用 (クーポンコード)
( -- 2列目: クーポン発行連番 を決定するサブクエリ (括弧で囲まれた部分)
SELECT -- サブクエリ: クーポン発行連番を計算するSELECT句
発行済枚数 + 1 -- 発行済枚数に1を加算 (次のクーポン発行連番)
FROM -- FROM句: データ取得元はCTE「発行済枚数取得」
発行済枚数取得
WHERE -- WHERE句: 発行上限枚数を超えないかチェックする条件
( -- さらにネストされたサブクエリ (発行済枚数を取得)
SELECT -- ネストされたサブクエリ: 発行済枚数を取得するSELECT句
発行済枚数
FROM -- FROM句: データ取得元はCTE「発行済枚数取得」
発行済枚数取得
) < 発行上限枚数 -- 取得した発行済枚数が「発行上限枚数」より小さい場合のみ発行可能
), -- サブクエリ 終了
:会員コード, -- 3列目: パラメータ「:会員コード」の値をそのまま使用 (獲得会員コード)
獲得制限_1枚限り -- 4列目: 「クーポン管理」テーブルから取得する「獲得制限_1枚限り」列の値 (獲得制限_1枚限り)
FROM -- データ取得元テーブル: クーポン管理テーブル
クーポン管理
WHERE -- WHERE句: 絞り込み条件
クーポンコード = :クーポンコード; -- クーポンコードがパラメータ「:クーポンコード」と一致するレコード
このE-R図は、クーポン管理とクーポン明細のテーブル構造を表しており、クーポン管理テーブルはクーポン券種の設定情報を、クーポン明細テーブルは発行済みのクーポン情報を管理します。
例6:SQL
以下のSQL文は、一括購入割当テーブルに新しいレコードを追加するINSERT文です。 指定された一括購入ID、社員ID、企業IDのパラメータ値を、それぞれ対応するカラムに挿入します。
-- 一括購入割当テーブルに新しいレコードを挿入するクエリ
INSERT INTO 一括購入割当 (
一括購入ID, -- 挿入するカラム: 一括購入ID
社員ID, -- 挿入するカラム: 社員ID
企業ID -- 挿入するカラム: 企業ID
) VALUES (
:一括購入ID, -- 挿入する値: パラメータ 一括購入ID
:社員ID, -- 挿入する値: パラメータ 社員ID
:企業ID -- 挿入する値: パラメータ 企業ID
);
例7:SQL
以下のSQL文は、従業員テーブルから従業員番号を取得し、指定されたレポート年月と組み合わせて月次レポートテーブルに新しいレコードを挿入します。 INSERT INTO SELECT 文を使用しており、従業員テーブルの全従業員に対して、指定された年月で月次レポートを作成する処理です。 これにより、従業員ごとに月次レポートのレコードが自動的に生成されます。
-- INSERT INTO SELECT文: データをテーブルに挿入するSQL文 (SELECT文の結果を挿入)
INSERT INTO 月次レポート (従業員番号, レポート年月)
-- 挿入先のテーブルとカラムを指定: 月次レポートテーブルの "従業員番号" と "レポート年月" カラム
SELECT
従業員番号, -- 従業員テーブルから従業員番号を取得し、月次レポートテーブルの "従業員番号" 列に挿入
:レポート年月 -- パラメータ ":レポート年月" の値を月次レポートテーブルの "レポート年月" 列に挿入
FROM
従業員; -- データ取得元のテーブルを指定: 従業員テーブルからデータを取得
レコードの更新 (UPDATE)
例1:SQL
以下のSQL文は、クーポン発行処理の一部で、まずクーポン管理テーブルの発行済枚数をインクリメントし、発行上限を超えない範囲で更新します。 次に、更新されたクーポン管理テーブルから情報を取得し、新しいクーポン明細レコードをクーポン明細テーブルに挿入します。 この一連の処理により、クーポン発行数管理とクーポン明細の発行記録を整合性を保ちながら行うことができます。
-- クーポン管理テーブルを更新します
UPDATE クーポン管理
-- 発行済枚数を1増やす
SET
発行済枚数 = 発行済枚数 + 1
-- 更新対象のレコードを絞り込む条件
WHERE
クーポンコード = :クーポンコード -- パラメータ:クーポンコード (発行対象のクーポンコード)
AND 発行済枚数 < 発行上限枚数; -- 発行済枚数が発行上限枚数未満の場合のみ更新
-- クーポン明細テーブルに新しいレコードを挿入します
INSERT INTO クーポン明細 (
クーポンコード, -- クーポンコード
クーポン発行連番, -- クーポン発行連番
獲得会員コード, -- 獲得会員コード
獲得制限_1枚限り -- 獲得制限_1枚限り
)
-- 挿入する値をSELECT文で取得します
SELECT
:クーポンコード, -- パラメータ:クーポンコード (発行対象のクーポンコード)
発行済枚数, -- クーポン管理テーブルの発行済枚数を取得
:会員コード, -- パラメータ:会員コード (クーポンを獲得する会員コード)
獲得制限_1枚限り -- クーポン管理テーブルの獲得制限_1枚限りを取得
FROM
クーポン管理
-- SELECT文の対象レコードを絞り込む条件 (クーポンコードで絞り込み)
WHERE
クーポンコード = :クーポンコード; -- パラメータ:クーポンコード (発行対象のクーポンコード)
例2:SQL
以下のSQL文は、月次レポートテーブルのレコードを更新し、月間総歩数を計算して設定します。 月間総歩数は、歩数テーブルから、各従業員の指定されたレポート年月の歩数を合計することで算出されます。 これにより、指定された年月におけるすべての月次レポートレコードに対して、対応する歩数データに基づいた月間総歩数が自動的に更新されます。
-- UPDATE文: 月次レポートテーブルのレコードを更新するSQL文
UPDATE 月次レポート
-- SET句: 更新するカラムとその値を指定
SET
月間総歩数 = ( -- 月間総歩数カラムを更新する値を定義 (サブクエリ)
-- サブクエリ開始: 歩数テーブルから月間総歩数を計算する
SELECT
COALESCE(SUM(歩数.歩数), 0) -- 歩数の合計値を計算。COALESCE関数でSUM結果がNULLの場合に0を返す
FROM
歩数 -- 歩数テーブル
WHERE
歩数.従業員番号 = 月次レポート.従業員番号 -- 歩数テーブルと月次レポートテーブルを従業員番号で関連付け
AND TOYM(歩数.測定日) = :レポート年月 -- 歩数データの測定日をレポート年月で絞り込む
-- TOYM関数: 測定日を 'YYYY-MM' 形式に変換
-- サブクエリ終了: 計算された月間総歩数がUPDATE文のSET句に渡される
)
-- WHERE句: 更新対象のレコードを絞り込む条件を指定
WHERE
レポート年月 = :レポート年月; -- 更新対象の月次レポートをレポート年月で絞り込む
-- :レポート年月: パラメータ。更新対象のレポート年月を指定
E-R図
このE-R図は、従業員、歩数、月次レポートの３つのテーブルで構成され、従業員テーブルが中心となり、歩数テーブルと月次レポートテーブルに従業員番号で関連付けられています。 歩数テーブルは日々の歩数データを、月次レポートテーブルは月ごとの健康指標を記録し、従業員テーブルはこれらの情報が誰のものかを特定するために使用されます。
SQL標準における UPDATE 文の論理的な処理順序
| ステップ | 句など | 処理内容 |
|---|---|---|
| 1 | (UPDATE) テーブル名 | 更新対象テーブルを特定する |
| 2 | WHERE句 | 更新対象となる行を絞り込む |
| 3 | SET句 | 特定された行の指定されたカラムを更新する |
レコードの削除 (DELETE)
DELETE文は、テーブルから指定した条件に合致するレコードを削除するSQL文です。WHERE句で削除対象を絞り込みます。省略するとテーブルの全レコードが削除されるため注意が必要です。実行前にSELECT文で削除対象を確認し、トランザクション管理やバックアップを検討することで、データ損失のリスクを軽減できます
DELETE FROM テーブル名
WHERE 条件;
その他のDML関連
カーソル
カーソルは、データベースの検索結果である複数行の結果セットを、プログラムが一行ずつ順番に処理するための仕組みです。これにより、プログラムは結果セット全体を一度にメモリに読み込む必要がなく、効率的にデータを扱えます。
例1:SQL
このコードは、会員の購入履歴に基づいて会員種別を更新する処理です。まず、購入ステータスが「完了」かつ判定対象期限内の未処理の購入データをカーソルで取得し、会員テーブルの会員種別を一旦「一般会員」に設定します。その後、カーソルで取得した購入データを会員ごとに集計し、購入金額の合計が50,000円以上になった会員を「特別会員」にアップグレードし、購入データの判定処理状態を更新します。
DECLARE cur CURSOR FOR -- カーソル cur を宣言 (カーソルは、データベースから一度に1行ずつデータを取り出す仕組み)
SELECT
t2.会員番号, -- 会員番号を取得
t2.購入番号, -- 購入番号を取得
t2.購入金額 -- 購入金額を取得
FROM
t2 -- テーブル t2 から
WHERE
t2.購入ステータス = '完了' -- 購入ステータスが '完了' のレコードを抽出
AND t2.購入日時 <= :判定対象期限 -- 購入日時が :判定対象期限 以前のレコードを抽出
AND t2.判定処理状態 <> '判定処理済み' -- 判定処理状態が '判定処理済み' ではないレコードを抽出
ORDER BY
t2.会員番号, -- 会員番号で昇順にソート
t2.購入日時 -- 次に、第一ソートキー (t2.会員番号) の値が同じレコード同士の中で、t2.購入日時 カラムの値に基づいてソート
; -- カーソル宣言の終了
UPDATE 会員 t1 -- 会員テーブル t1 を更新
SET t1.会員種別 = '一般会員'; -- 全会員の会員種別を '一般会員' に設定
SET current_kaiin_no = 0; -- 変数 current_kaiin_no を 0 で初期化
SET goukei = 0; -- 変数 goukei を 0 で初期化
OPEN cur; -- カーソル cur をオープン (カーソルを使用可能にする)
fetch_loop: LOOP -- fetch_loop というラベルのループ開始 (カーソルからデータを繰り返し取得)
FETCH cur INTO kaiin_no, kounyu_no, kounyu_kingaku; -- カーソルから1行データを取得し、変数に格納 (会員番号, 購入番号, 購入金額)
IF kaiin_no <> current_kaiin_no THEN -- もし取得した会員番号が、現在処理中の会員番号と異なる場合
SET current_kaiin_no = kaiin_no; -- current_kaiin_no を新しい会員番号で更新
SET update_flag = 0; -- 変数 update_flag を 0 で初期化 (会員種別を更新したかどうかのフラグ、0:未更新)
SET goukei = 0; -- goukei を 0 で初期化 (新しい会員の購入金額を合計するため)
END IF; -- IF文 終了
IF update_flag = 0 THEN -- もし update_flag が 0 (会員種別がまだ更新されていない) なら
SET goukei = goukei + kounyu_kingaku; -- goukei に今回の購入金額を加算
UPDATE 購入 t2 -- 購入テーブル t2 を更新
SET t2.判定処理状態 = '判定処理済み' -- 該当レコードの判定処理状態を '判定処理済み' に更新
WHERE t2.購入番号 = kounyu_no; -- 購入番号が kounyu_no のレコード
IF goukei >= 50000 THEN -- もし goukei が 50000 以上になったら
UPDATE 会員 t1 -- 会員テーブル t1 を更新
SET t1.会員種別 = '特別会員' -- 該当会員の会員種別を '特別会員' に更新
WHERE t1.会員番号 = kaiin_no; -- WHERE句: 会員番号が kaiin_no のレコード
SET update_flag = 1; -- update_flag を 1 に設定 (会員種別を更新済みにする)
END IF; -- IF文 終了
ELSE -- update_flag が 0 でない場合 (すでに会員種別が更新済の場合)
UPDATE 購入 t2 -- 購入テーブル t2 を更新
SET t2.判定処理状態 = '繰越し' -- 該当レコードの判定処理状態を '繰越し' に更新
WHERE t2.購入番号 = kounyu_no; -- 購入番号が kounyu_no のレコード
END IF; -- IF文 終了
END LOOP fetch_loop; -- ループ fetch_loop 終了 (カーソルからデータがなくなるまで繰り返す)
CLOSE cur; -- カーソル cur をクローズ (カーソルを閉じる)
