はじめに
今回も、応用情報技術者試験の過去問を解いていきます。
今回の問題は SQL（データベース） に関する問題です。
特にポイントとなるのは次の3つです。
- LEFT OUTER JOIN
- GROUP BY
- SUM関数とNULL処理（COALESCE）
実務でもよく使われるSQLの考え方なので、試験対策としてだけでなく理解しておきたい内容です。
問題
「部品」表と「在庫」表に対してSQL文を実行した結果が次のよう(実行結果)になりました。
SQL文の a に入る式として適切なものを選ぶ問題です。
部品テーブル
| 部品ID | 発注点 |
|---|---|
| P01 | 100 |
| P02 | 150 |
| P03 | 100 |
在庫テーブル
| 部品ID | 倉庫ID | 在庫数 |
|---|---|---|
| P01 | W01 | 90 |
| P01 | W02 | 90 |
| P02 | W01 | 150 |
SQL
SELECT 部品.部品ID AS 部品ID,
CASE WHEN 部品.発注点 > a
THEN '必要'
ELSE '不要'
END AS 発注要否FROM 部品 LEFT OUTER JOIN 在庫
ON 部品.部品ID = 在庫.部品IDGROUP BY 部品.部品ID, 部品.発注点
実行結果
| 部品ID | 発注要否 |
|---|---|
| P01 | 不要 |
| P02 | 不要 |
| P03 | 必要 |
選択肢
| 選択肢 | 内容 |
|---|---|
| ア | COALESCE(MIN(在庫.在庫数),0) |
| イ | COALESCE(MIN(在庫.在庫数),NULL) |
| ウ | COALESCE(SUM(在庫.在庫数),0) |
| エ | COALESCE(SUM(在庫.在庫数),NULL) |
解答
ウ
COALESCE(SUM(在庫.在庫数),0)
解説
まず、このSQLの処理の流れを整理します。
SQL処理の流れ
部品テーブル
↓
LEFT OUTER JOIN
↓
在庫テーブル
↓
GROUP BY
↓
SUM(在庫数)
↓
発注点と比較
つまり
部品ごとの総在庫数を求めて、発注点と比較しているSQLです。
各部品の在庫合計
| 部品ID | 在庫合計 |
|---|---|
| P01 | 90 + 90 = 180 |
| P02 | 150 |
| P03 | NULL |
P03は在庫テーブルに存在しないため
SUM = NULL
になります。
NULL問題
ここが重要ポイントです。
在庫が存在しない場合
SUM(在庫数) = NULL
になります。
しかしこのままだと
発注点 > NULL
の比較ができません。
そこで登場するのが
COALESCE
です。
COALESCEの役割
COALESCEは
NULLを別の値に置き換える関数
です。
COALESCE(SUM(在庫数),0)
なら
NULL → 0
に変換されます。
最終的な比較
| 部品ID | 発注点 | 在庫 | 判定 |
|---|---|---|---|
| P01 | 100 | 180 | 不要 |
| P02 | 150 | 150 | 不要 |
| P03 | 100 | 0 | 必要 |
P03は
100 > 0
なので
必要
になります。
これで問題の結果と一致します。
問題の用語解説
LEFT OUTER JOIN
左側のテーブルを基準に結合するSQLです。
一致するデータがなくても
左テーブルの行は必ず残ります。
例
| 部品 | 在庫 |
|---|---|
| P03 | NULL |
のような結果になります。
GROUP BY
同じ値のレコードをまとめるSQLです。
今回のSQLでは
部品IDごと
にまとめています。
SUM関数
数値を合計するSQL関数です。
SUM(在庫数)
で
倉庫ごとの在庫
↓
部品ごとの在庫合計
を求めています。
COALESCE
NULLを別の値に変換する関数です。
COALESCE(値,置換値)
例
COALESCE(NULL,0) → 0
体系的位置づけ
この問題は 応用情報シラバスの次の分野に属します。
テクノロジ系
└ データベース
├ SQL
├ 集約関数
├ NULL処理
└ JOIN
特に重要なのは
- JOIN
- GROUP BY
- SUM
- COALESCE
です。
この4つの組み合わせは
試験でも実務でも非常によく出ます。
今回の問題の重要ポイント
今回の問題の核心はこの3つです。
① LEFT JOIN
在庫がなくても部品は表示される。
② SUMはNULLになる
レコードが無い場合
SUM = NULL
になります。
③ COALESCEでNULL対策
COALESCE(SUM(在庫),0)
で
NULL → 0
に変換します。
SQLの完成形
SELECT 部品.部品ID,
CASE WHEN 部品.発注点 > COALESCE(SUM(在庫.在庫数),0)
THEN '必要'
ELSE '不要'
END
FROM 部品
LEFT OUTER JOIN 在庫
ON 部品.部品ID = 在庫.部品ID
GROUP BY 部品.部品ID, 部品.発注点
まとめ
今回の問題のポイントを整理します。
- LEFT JOINは一致しないデータも残る
- SUMはデータがないとNULLになる
- COALESCEでNULLを0に変換する
- 在庫合計と発注点を比較するSQL
コメント