APB-DB-SQL

皆さん、AP試験対策お疲れ様です。科目Bのデータベース分野でよく出るSQL文パターンを、暗記用にまとめますね。

## 1. 基本的なSELECT文

```sql

• 単純な条件抽出

  SELECT 商品名, 単価 FROM 商品 WHERE 単価 >= 1000;

• 複数条件（AND/OR）

  SELECT * FROM 社員 WHERE 部署コード = '01' AND 年齢 >= 30;

• パターンマッチ（LIKE）

  SELECT * FROM 顧客 WHERE 氏名 LIKE '田中%';

• NULL判定

  SELECT * FROM 注文 WHERE 発送日 IS NULL;

  ```

## 2. 集合関数・GROUP BY・HAVING

-- 集計
SELECT 部署コード, COUNT(*), AVG(給与) FROM 社員 GROUP BY 部署コード;

• 集計結果の絞り込み(HAVING)

  SELECT 部署コード, SUM(売上) FROM 売上実績 GROUP BY 部署コード HAVING SUM(売上) > 1000000;

※ WHEREは集計前の行を絞り、HAVINGは集計後のグループを絞る、という違いが頻出ポイントです。

## 3. 結合(JOIN)

```sql

• 内部結合

  SELECT A.社員名, B.部署名
  FROM 社員 A INNER JOIN 部署 B
  ON A.部署コード = B.部署コード;

• 外部結合(左外部結合)

  SELECT A.社員名, B.部署名
  FROM 社員 A LEFT OUTER JOIN 部署 B
  ON A.部署コード = B.部署コード;

  ```
  ※ 左外部結合は「右側にマッチしなくても左側は全件出す」がポイント(未所属社員も出したい場合など)。

## 3. 結合(JOIN)

• INNER JOIN
  ＝両方にあるデータだけ

• LEFT OUTER JOIN
  ＝左側の表は全部残す

• RIGHT OUTER JOIN
  ＝右側の表は全部残す

INNER JOIN
例：注文した社員だけ表示する

   ○
  ／＼
 ／  ＼
共通部分

LEFT OUTER JOIN
例：注文してない社員も含めて表示する

○○○○ + 共通部分

RIGHT OUTER JOIN
例：注文した社員だけ表示する

共通部分 + ○○○○

## 4. 副問い合わせ(サブクエリ)

• IN を使ったサブクエリ
  意味「東海地域に属する部署に所属している社員の氏名を取得する」

  SELECT 氏名 FROM 社員
  WHERE 部署コード IN (SELECT 部署コード FROM 部署 WHERE 地域 = '東海');

• 相関副問い合わせ(NOT EXISTS)
  意味「1件も注文を担当したことがない社員を取得する」

  SELECT 氏名
  FROM 社員 A
  WHERE NOT EXISTS (
     SELECT *
     FROM 注文 B
     WHERE A.社員コード = B.担当者コード);

• 相関副問い合わせ(EXISTS)
  意味「1件以上の注文を担当したことがある社員の氏名を取得する」

  SELECT 氏名 FROM 社員 A
  WHERE EXISTS (
   SELECT * FROM 注文 B WHERE A.社員コード = B.担当者コード);

## 5. 更新系(UPDATE/DELETE/INSERT)

```sql

• 条件付き更新

  UPDATE 商品 SET 単価 = 単価 * 1.1 WHERE 分類コード = '02';

• サブクエリを使った削除

  DELETE FROM 注文明細
  WHERE 注文番号 IN (SELECT 注文番号 FROM 注文 WHERE キャンセル区分 = '1');

• 挿入

  INSERT INTO 顧客 (顧客コード, 氏名, 登録日) VALUES ('C001', '鈴木一郎', CURRENT_DATE);

  ```

## 6. ビュー(VIEW)

```sql

CREATE VIEW 部署別売上 AS
SELECT 部署コード, SUM(売上高) AS 売上合計
FROM 売上実績
GROUP BY 部署コード;

```
※ ビューへのUPDATEが可能かどうか(集計関数を含む場合は不可、など)も過去問で問われます。

## 7. トランザクション制御

```sql

BEGIN TRANSACTION;
UPDATE 口座 SET 残高 = 残高 - 10000 WHERE 口座番号 = 'A001';
UPDATE 口座 SET 残高 = 残高 + 10000 WHERE 口座番号 = 'B001';
COMMIT;

• 異常時は ROLLBACK;
  ```
  ※ ACID特性(原子性・一貫性・独立性・耐久性)とセットで問われやすいです。

## 8. 権限制御(GRANT/REVOKE)

GRANT SELECT, UPDATE ON 社員 TO ユーザーA;
REVOKE UPDATE ON 社員 FROM ユーザーA;

```

## 9. インデックス・制約

```sql

CREATE INDEX idx_社員_部署 ON 社員(部署コード);

ALTER TABLE 社員 ADD CONSTRAINT fk_部署
FOREIGN KEY (部署コード) REFERENCES 部署(部署コード);

```

## 10. インデックス・制約ALTER TABLE 頻出パターン集

• 一意制約(UNIQUE)の追加

  ALTER TABLE 社員 ADD CONSTRAINT uq_社員番号 UNIQUE (社員番号);

• 列の追加

  ALTER TABLE 社員 ADD 電話番号 VARCHAR(15);

• 列の削除

  ALTER TABLE 社員 DROP COLUMN 電話番号;

• 列のデータ型・定義変更

  ALTER TABLE 社員 MODIFY 給与 DECIMAL(10,2);

• ※ MODIFYはMySQL系。標準SQL/一部DBでは下記の書き方も出る

  ALTER TABLE 社員 ALTER COLUMN 給与 TYPE DECIMAL(10,2);

• 列名の変更

  ALTER TABLE 社員 RENAME COLUMN 氏名 TO 従業員氏名;

• テーブル名の変更

  ALTER TABLE 社員 RENAME TO 従業員;

• NOT NULL制約の追加

  ALTER TABLE 社員 MODIFY 部署コード VARCHAR(2) NOT NULL;

• 主キー制約の追加

  ALTER TABLE 社員 ADD CONSTRAINT pk_社員 PRIMARY KEY (社員コード);

• 外部キー制約の追加

  ALTER TABLE 社員 ADD CONSTRAINT fk_部署
  FOREIGN KEY (部署コード) REFERENCES 部署(部署コード);

• 制約の削除

  ALTER TABLE 社員 DROP CONSTRAINT fk_部署;

• デフォルト値の設定

  ALTER TABLE 社員 ALTER COLUMN 入社区分 SET DEFAULT '正社員';

## 11.WITH句(共通表式/CreateViewのような機能)の例

```sql

WITH 部署別平均給与 AS (
   SELECT 部署コード, AVG(給与) AS 平均給与
   FROM 社員
   GROUP BY 部署コード
)
SELECT A.社員名, A.給与, B.平均給与
FROM 社員 A
JOIN 部署別平均給与 B
ON A.部署コード = B.部署コード
WHERE A.給与 > B.平均給与;

```

やっていること**:

1. `WITH` で「部署別平均給与」という一時的な集計結果(名前付きのテーブルのようなもの)を先に作る
2. そのあとの `SELECT` 文で、社員テーブルと結合して「自分の部署の平均給与より高い給与をもらっている社員」を抽出する

ポイント(試験で狙われやすいところ)**:

• WITH句を使わずに同じことをやろうとすると、サブクエリを何度も書く必要があって読みにくくなる → WITH句は「複雑な副問い合わせを分かりやすく名前付けして再利用できる」のがメリット
• WITH句で定義した名前(この例では「部署別平均給与」)は、そのSQL文の中でだけ使える一時的なものであり、実テーブルとして保存されるわけではない
• 複数のWITH句をカンマ区切りで並べて定義することもできる(例:`WITH A AS (...), B AS (...) SELECT ...`)

科目Bでは「このWITH句を使わずに書き直すとどうなるか」「WITH句の中身を読み解いて結果を答える」といった形式で出やすいので、上の例のように「先に集計→あとで結合・比較」という流れをイメージで掴んでおくと読みやすくなりますよ。

特に頻出の狙われ方**:

• WHERE と HAVING の使い分け
• 内部結合 vs 外部結合の結果件数の違い
• 相関副問い合わせ(EXISTS/NOT EXISTS)の動作の読み取り
• トランザクションの排他制御(デッドロック含む)
• 正規化(第1～第3正規形)とE-R図の対応関係

もし希望があれば、この中の特定パターン(例:相関副問い合わせの読み取り問題)だけを深掘りした練習問題を作ることもできますよ。

過去問SQL

過去問SQL-R2A

図3 部屋の空き状況の確認を行うためのSQL文

SELECT 施設ID, 部屋種別ID, COUNT(*) FROM 部屋
WHERE NOT EXISTS (
SELECT * FROM 予約明細 WHERE 予約明細.部屋ID = 部屋.部屋ID
AND 予約明細.宿泊日 >= :チェックイン日付 AND 予約明細.宿泊日 < :チェックアウト日付
)
AND 施設ID = :施設ID AND 部屋種別ID = :部屋種別ID
GROUP BY 施設ID, 部屋種別ID
HAVING COUNT(*) >= :部屋数

• 考え方:指定した施設・部屋種別で、「チェックイン日～チェックアウト日の間、ずっと空いている部屋」が、必要な部屋数以上あるかどうかを判定するSQLです

図6　削除するレコードを抽出するSQL文

SELECT t1.予約ID, t1.予約明細ID, t1.部屋ID, t1.宿泊日 FROM 予約明細 t1
WHERE t1.予約ID > ( SELECT MIN(t2.予約ID) FROM 予約明細 t2
WHERE t2.部屋ID = t1.部屋ID AND t2.宿泊日 = t1.宿泊日 )

• 考え方:同じ部屋ID・同じ宿泊日を持つレコード群(t2)の中で、最も予約IDが小さいもの = 最初に挿入された予約です(予約IDは挿入順に大きくなる仕様のため)。
  t1.予約ID がその最小値より大きい、つまり「後から重複して入った予約」だけを抽出対象にしています。
  これにより最初の1件だけが残り、残りの重複レコードが削除対象として抽出されます

過去問SQL-R3S

• 表２　割当依頼した社員に一括購入IDを割り当ててる　SQL文 [#f9d11e9d]

INSERT INTO 一括購入割当 (一括購入ID, 社員ID, 企業ID)　VALUES(:一括購入ID，:社員ID，:企業ID)

※　埋込み変数が回答になるパターンもある　:(コロン)は「埋込み変数」である。

• 図2　閲覧可能な重複を含まない書籍の一覧を取得するSQL文

SELECT sk.書籍ID
FROM 社員書籍購入 sk
WHERE sk.企業ID = :企業ID AND sk.社員ID = :社員ID
UNION
SELECT ik.書籍ID
FROM 一括購入 ik
INNER JOIN 一括購入割当 iw
ON ik.一括購入ID = iw.一括購入ID
WHERE ik.企業ID = :企業ID AND iw.社員ID = :社員ID

過去問SQL-R5A

ウィンドウ関数は、指定したウィンドウ内を対象として集計等を行うものです。ここでいうウィンドウとは、テーブルの一部を取り出したものです。
GROUP BY句と異なり、行と列の範囲をしているだけで集約は行わないこと、現在の行から●行前までや■行後までなどの範囲指定ができるため、
移動平均やある時点までの累計値の算出などに活用することができます。また、サブクエリを使うのと比較して、構文をスッキリさせることができるのもメリットのひとつです

SQL意味：倉庫コード、商品コードごとに各年月日の6日前から当日までの平均在庫数及び売上個数を集計する

SELECT 年, 月, 日, 倉庫コード, 商品コード,
AVG(在庫数) OVER 期間定義 AS 平均在庫数,
SUM(売上個数) OVER 期間定義 AS 期間内売上個数
FROM 在庫推移状況
WINDOW 期間定義 AS (
PARTITION BY 倉庫コード, 商品コード
ORDER BY 年, 月, 日 ASC
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

考え方１：ウィンドウ枠を●行前までや■行後までというように指定する場合、行が整列されていなければ範囲指定は意味をなしません。このため、各パーテーション内をORDER BY句で整列する処理が必要
考え方２：ウィンドウ関数内ではウィンドウ名の前に"OVER"が必要

過去問SQL-R4A

図2　表3のアクエス制御を設定するSQL文

GRANT SELCT (契約ID,暗証番号) ON 契約 TO ADMIN

図3　表4の料金プラン表を作成するSQL文

CREATE TABLE 料金プラン
(料金プランコード CHAR(8) NOT NULL,
通信事業者コード CHAR(4) DEFAULT '1234' NOT NULL ,　--データ型:CHAR(4)で、初期値'1234'と非NULL制約を設定
料金プラン名 VARCHAR(30) NOT NULL,
基本料金 DECIMAL(5,0) NOT NULL,
通話単価 DECIMAL(5,2) NOT NULL,
通信単価 DECIMAL(5,4) NOT NULL,
PRIMARY KEY (料金プランコード), --主キー(プライマリーキー)の設定
FOREIGN KEY (通信事業者コード) REFERENCES 通信事業者(通信事業者コード)) --外部キーの設定

過去問SQL-R5S

問題
INSERT INTO 従業員ごと_目標集計_一時(従業員コード, KPIコード, 目標個人集計)
SELECT　従業員コード,KPIコード,SUM(月別目標値)　FROM 月別個人目標
WHERE 年月 （e） :年度開始年月 AND :集計年月
（g）

解答
e BETWEEN　　// 日付で挟まされていることから判断
g GROUP BY A.従業員コード, A.所属組織コード // Select文のSUM関数から判断　

午後対策

【過去33回分】応用情報技術者試験（科目B：データベース ）過去問と模範解答を見て傾向と対策を考えてみた。

サイト内リンク

• AP
• APB-DB
• APB-DB-SQL

• AI
• FE
• 書籍案内