平成24年度 春期 午前Ⅱ 問9
データ操作
ビューに関する問題
ある月の“月末商品在庫”表と“当月商品出荷実績”表を使って，ビュー“商品別出荷実績”を定義した。このビューに SQL 文を実行した結果の値はどれか。
月末商品在庫
| 商品コード | 商品名 | 在庫数 |
|---|---|---|
| S001 | A | 100 |
| S002 | B | 250 |
| S003 | C | 300 |
| S004 | D | 450 |
| S005 | E | 200 |
当月商品出荷実績
| 商品コード | 商品出荷日 | 出荷数 |
|---|---|---|
| S001 | 2012-03-01 | 50 |
| S003 | 2012-03-05 | 150 |
| S001 | 2012-03-10 | 100 |
| S005 | 2012-03-15 | 100 |
| S005 | 2012-03-20 | 250 |
| S003 | 2012-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
解説
条件を満たす S001 と S003 の在庫 100＋300＝400 です。
ビューは、月末商品在庫を左に置いた LEFT OUTER JOIN で、商品ごとに出荷数を合計しています。商品ごとに求めると、S001 は 50＋100＝150 で在庫100、S002 は出荷が無いので出荷実績数は NULL で在庫250、S003 は 150＋150＝300 で在庫300、S004 は NULL で在庫450、S005 は 100＋250＝350 で在庫200 です。SQL 文の条件 出荷実績数 <= 300 を満たすのは S001 の150と S003 の300で、S005 の350は超えています。NULL との比較は真にならないので、S002 と S004 は選ばれません。月末在庫数を合計すると 100＋300＝400 になり、正解と一致します。結合で生じた NULL は比較の条件で落ちる、という点を見落とさないことが計算の要です。
ほかの選択肢はなぜ違うのか
- イ500：500 は、条件の向きを逆にして出荷実績数が300以上の商品を選んだ場合の在庫 300＋200 です。S005 の出荷実績数350は300を超えるので、条件を満たしません。
- ウ600：600 は、出荷のあった S001、S003、S005 の在庫 100＋300＋200 をすべて足した値です。S005 の出荷実績数は350で、300以下という条件から外れます。
- エ700：700 は、出荷の無かった S002 と S004 の在庫 250＋450 の合計です。この2商品の出荷実績数は NULL で、比較の条件が真にならないので、どちらも選ばれません。
出典：平成24年度 春期 データベーススペシャリスト試験 午前Ⅱ 問9
この解説に誤りを見つけたら教えてください。直して、直した記録を残します。誤りを報告する（メールが開きます）