平成29年度春期 システム監査技術者 午前Ⅱ 問17

SQLの抽出条件を読み解く視点

SQLの抽出条件と結果の対応を考えるときは、条件がどの行に適用され、複数の条件がどのような関係で組み合わされるかに注目し、列の値と条件の対応を一つの観点から整理します。

この問題で問われていること

注目する観点は、各条件が対象行を選ぶ仕組みと、条件同士の組み合わせ方です。列ごとの値、比較の向き、条件を結ぶ語の意味を画面上で丁寧に対応づけ、結果に含まれる行の特徴を言葉で確認してください。

視点

抽出結果を読む前に、条件ごとに対象となる列と行の特徴を整理し、条件が単独で示す範囲と、複数条件を組み合わせた後に残る範囲を区別して考えます。

確認ポイント

• 各条件が参照している列と値の関係を確認する
• 条件同士を結ぶ語が示す組み合わせ方を確認する
• 結果に含まれる行の共通点を条件ごとに照合する

正解

イ

解説

商品ランクがAの商品だけを対象にLEFT OUTER JOINを行うと、実績のある商品は売上金額を取得し、実績のない商品はNULLになります。AVGはNULLを平均対象から除外するため、50と250の平均は (50円＋250円)÷2件＝150円となり、正解はイです。

理由

Aランクの商品はS001、S002、S003です。売上実績を結合すると、S001は50円、S003は250円、S002は対応する実績がないためNULLになります。AVGはNULLを計算対象から除外するので、売上平均金額は (50円＋250円)÷2件＝150円です。

比較

LEFT OUTER JOINは実績のないS002も結果上は残しますが、そのNULLを0円として平均するわけではありません。したがって、Aランク商品の件数3件で割るのではなく、AVGの対象となる実績2件で平均します。GROUP BYはAランクのグループを集計するために使われています。

選択肢ア

これは不正解だよ。100という値は、Aランク商品の売上実績を正しく平均した結果ではありません。AランクではS001が50円、S003が250円であり、S002は実績なしのNULLです。AVGはNULLを除外するため、これらの条件から100円にはなりません。

選択肢イ（正解）

これは正解だよ。WHERE句で商品ランクがAの商品に絞ると、売上実績はS001が50円、S003が250円となり、S002はNULLです。AVGはNULLを除外して実績のある2件だけを平均するため、(50円＋250円)÷2件＝150円です。

選択肢ウ

これは不正解だよ。225という値は、Aランク商品の売上実績50円と250円を平均した結果とは一致しません。LEFT OUTER JOINで残る実績なしのS002はNULLであり、AVGの対象件数や金額をこの値に変える条件はありません。

選択肢エ

これは不正解だよ。275という値は、Aランク商品の該当実績から得られる平均値ではありません。対象となる売上金額は50円と250円で、実績のないS002はNULLとしてAVGから除外されます。したがって、275円になる集計条件とは一致しません。

学習のポイント

LEFT JOINの未一致行と、AVGにおけるNULLの扱いを理解する。