平成29年度 春期 午前Ⅱ 問10
データ操作
ビューに関する問題
ある月の“月末商品在庫”表と“当月商品出荷実績”表を使って，ビュー“商品別出荷実績”を定義した。このビューにSQL文を実行した結果の値はどれか。
月末商品在庫
| 商品コード | 商品名 | 在庫数 |
|---|---|---|
| S001 | A | 100 |
| S002 | B | 250 |
| S003 | C | 300 |
| S004 | D | 450 |
| S005 | E | 200 |
当月商品出荷実績
| 商品コード | 商品出荷日 | 出荷数 |
|---|---|---|
| S001 | 2017-03-01 | 50 |
| S003 | 2017-03-05 | 150 |
| S001 | 2017-03-10 | 100 |
| S005 | 2017-03-15 | 100 |
| S005 | 2017-03-20 | 250 |
| S003 | 2017-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の在庫100と300で400です。
見分ける軸は、外結合で出荷のない商品の出荷実績数がNULLになることと、NULLは比較条件を満たさないことです。ビューでは商品ごとに出荷数を合計します。S001は50＋100で150、S003は150＋150で300、S005は100＋250で350です。S002とS004は出荷がないので、LEFT OUTER JOINで行は残りますが、SUMの結果はNULLになります。次にWHERE 出荷実績数 <= 300で絞ると、150のS001と300のS003が残ります。S005は350なので外れ、NULLのS002とS004は比較がunknownになり外れます。残った2商品の月末在庫数を足すと100＋300＝400です。外結合で残る行の値がNULLになることを、条件を当てる前に確かめるのがこの型の解き方です。
ほかの選択肢はなぜ違うのか
- イ500：500は、S003の300とS005の200を足した値です。不等号を逆に読んで出荷実績数300以上の商品を選ぶとこの組になりますが、条件は300以下なので、350のS005は入りません。
- ウ600：600は、出荷のあったS001・S003・S005の在庫数100＋300＋200の和です。WHERE句の条件を当てずに合計した形で、出荷実績数が350のS005まで含めてしまっています。
- エ700：700は、出荷のなかったS002とS004の在庫数250＋450の和にあたります。しかし、この2行は出荷実績数がNULLで比較がunknownとなるため、WHERE句はこの2行を選びません。
出典：平成29年度 春期 データベーススペシャリスト試験 午前Ⅱ 問10
この解説に誤りを見つけたら教えてください。直して、直した記録を残します。誤りを報告する（メールが開きます）