令和3年度 春期 午前Ⅱ 問24
データベース
ビューに関する問題
ある月の“月末商品在庫”表と“当月商品出荷実績”表を使って，ビュー“商品別出荷実績”を定義した。このビューにSQL文を実行した結果の値はどれか。
月末商品在庫
| 商品コード | 商品名 | 在庫数 |
|---|---|---|
| S001 | A | 100 |
| S002 | B | 250 |
| S003 | C | 300 |
| S004 | D | 450 |
| S005 | E | 200 |
当月商品出荷実績
| 商品コード | 商品出荷日 | 出荷数 |
|---|---|---|
| S001 | 2021-03-01 | 50 |
| S003 | 2021-03-05 | 150 |
| S001 | 2021-03-10 | 100 |
| S005 | 2021-03-15 | 100 |
| S005 | 2021-03-20 | 250 |
| S003 | 2021-03-25 | 150 |
〔ビュー“商品別出荷実績”の定義〕
CREATE VIEW 商品別出荷実績（商品コード，出荷実績数，月末在庫数）
AS SELECT 月末商品在庫.商品コード，SUM（出荷数），在庫数
FROM 月末商品在庫 LEFT OUTER JOIN 当月商品出荷実績
ON 月末商品在庫.商品コード = 当月商品出荷実績.商品コード
GROUP BY 月末商品在庫.商品コード，在庫数
〔SQL文〕
SELECT SUM（月末在庫数）AS 出荷商品在庫合計 FROM 商品別出荷実績 WHERE 出荷実績数 <= 300
- ア400
- イ500
- ウ600
- エ700
答えと解説を見る
✓ これが正解ア400
解説
出荷実績数が300以下の商品はS001とS003で、在庫数の和は400です。
ビューは、月末商品在庫を左側に置いた左外結合のあと、商品コードと在庫数でグループ化し、出荷数を合計しています。商品ごとに出荷実績数を求めると、S001は50＋100＝150、S003は150＋150＝300、S005は100＋250＝350です。出荷のないS002とS004は、左外結合で行は残りますが、合計する出荷数がないため出荷実績数はNULLになります。SQL文の条件は出荷実績数が300以下で、NULLとの比較は真にならないので、S002とS004は除かれます。条件を満たすのはS001とS003で、在庫数の合計は100＋300＝400となり、正解の値と一致します。外結合でできたNULLの行は比較の条件で落ちる、という点を押さえておくと、この種の集計で迷いません。
ほかの選択肢はなぜ違うのか
- イ500：500は、出荷実績数が300以上の商品、つまり300のS003と350のS005を選んだときの在庫数の和、300＋200で得られる値です。条件の不等号の向きを逆に読むとこの値になります。
- ウ600：600は、出荷が1件でもある商品、つまりS001、S003、S005の在庫数100＋300＋200の和です。合計した出荷実績数ではなく、1件ごとの出荷数がどれも300以下であることで判定するとこの値になります。
- エ700：700は、出荷のないS002とS004の在庫数250＋450の和です。出荷実績数がNULLの行だけを選んだ値ですが、NULLとの比較は真にならないので、この2行はむしろ条件から外れる側です。
出典：令和3年度 春期 システムアーキテクト試験 午前Ⅱ 問24
この解説に誤りを見つけたら教えてください。直して、直した記録を残します。誤りを報告する（メールが開きます）