応用情報技術者試験 令和5年秋 午前問29 解説付き過去問
問題
"製品"表と"在庫"表に対し、次のSQL文を実行した結果として得られる表の行数は幾つか。
SELECT DISTINCT 製品番号 FROM 製品
WHERE NOT EXISTS (SELECT 製品番号 FROM 在庫
WHERE 在庫数 > 30 AND 製品製品番号 = 在庫製品番号)
正解
解説
この問題は、相関副問合せとNOT EXISTSの動作を正しく理解し、条件を満たす製品番号の行数を求めるものです。以下の手順で解説します。
- SQL文の目的の理解
- 相関副問合せの仕組み
- 製品表と在庫表の確認
- AB1805
- CC5001
- MZ1000
- XZ3000
- ZZ9900
- 各製品に対する副問合せの結果の確認
- AB1805：在庫数50の行が存在 → 条件に一致する行がある → NOT EXISTSは偽 → 対象外
- CC5001：在庫数40と35の行が存在 → 条件に一致する行がある → NOT EXISTSは偽 → 対象外
- MZ1000：在庫表に該当行が存在しない → 条件を満たす行が存在しない → NOT EXISTSは真 → 対象
- XZ3000：在庫数10と20の行のみ → いずれも30以下 → 条件を満たす行が存在しない → NOT EXISTSは真 → 対象
- ZZ9900：在庫数100の行が存在 → 条件に一致する行がある → NOT EXISTSは偽 → 対象外
- 結果の集計
このSQL文は、製品表から「在庫表において在庫数が30を超える行が存在しない製品番号」を抽出しようとしています。
そのため、WHERE句内のNOT EXISTSにより、「在庫数 > 30 かつ 製品製品番号 = 在庫製品番号」という条件に一致する行が在庫表に存在しない製品番号だけが対象となります。
このSQL文は、相関副問合せを用いています。
相関副問合せとは、主問合せの行ごとにサブクエリが実行され、主問合せの値がサブクエリ内で参照される形式です。
製品表の行数分だけ、副問合せが繰り返し評価されます。
製品表には、次の5つの製品番号が存在します：
在庫表には、それぞれの製品の在庫情報が複数の倉庫にまたがって記録されています。
製品ごとに「在庫表に在庫数 > 30 の行が存在するかどうか」を確認します。
NOT EXISTSが真となるのは、製品番号「MZ1000」と「XZ3000」の2つです。
SELECT DISTINCT により重複が除外されますが、もともと異なる製品番号なので、そのまま2行が抽出されます。
したがって、得られる表の行数は2行です。