データベーススペシャリスト 2024年 午前2 問07
問題文
“商品”表と“商品別売上実績”表に対して、SQL文を実行して得られる売上平均金額はどれか。
選択肢
ア：100
イ：150（正解）
ウ：225
エ：275
商品表と商品別売上実績表のSQL平均値計算【午前2 解説】
要点まとめ
- 結論：商品ランクが'A'の商品の売上合計金額の平均は150である。
- 根拠：LEFT OUTER JOINで商品ランク'A'の6商品から売上実績がある3商品の売上合計金額を抽出し、NULLは除外して平均を計算する。
- 差がつくポイント：JOINの種類とWHERE句の条件が平均計算にどう影響するかを正確に理解することが重要である。
正解の理由
SQL文は「商品」表と「商品別売上実績」表をLEFT OUTER JOINし、商品ランクが'A'の行に絞っています。
商品ランク'A'の商品コードはS001、S002、S003の3つです。
売上実績はS001が50、S003が250、S002は売上実績がないためNULLです。
平均はNULLを除外して計算されるため、(50 + 250) ÷ 2 = 150となり、選択肢イが正解です。
商品ランク'A'の商品コードはS001、S002、S003の3つです。
売上実績はS001が50、S003が250、S002は売上実績がないためNULLです。
平均はNULLを除外して計算されるため、(50 + 250) ÷ 2 = 150となり、選択肢イが正解です。
よくある誤解
LEFT OUTER JOINでNULLを含む行も対象になるため、NULLを0として計算する誤りが多いです。
また、WHERE句の条件がJOIN後のテーブルに適用される点を見落としがちです。
また、WHERE句の条件がJOIN後のテーブルに適用される点を見落としがちです。
解法ステップ
- 商品ランクが'A'の商品の商品コードを抽出する（S001、S002、S003）。
- 商品別売上実績表とLEFT OUTER JOINし、売上合計金額を結合する。
- 売上実績がない商品（S002）はNULLとなる。
- WHERE句で商品ランク='A'の行に絞る。
- AVG関数はNULLを除外して平均を計算するため、売上合計金額がある2件の平均を求める。
選択肢別の誤答解説
- ア: 100
売上合計金額の合計300を3件で割った誤り。NULLを0と誤認して計算している。 - イ: 150
正解。NULLを除外し、売上実績がある2件の平均を正しく計算している。 - ウ: 225
売上合計金額の合計450を2件で割った誤り。売上合計金額の合計は300であるため計算ミス。 - エ: 275
売上合計金額の最大値と平均を混同した誤り。最大値は450であり平均とは異なる。
補足コラム
LEFT OUTER JOINは左側のテーブルの全行を保持し、右側のテーブルに一致する行がなければNULLを返します。
AVG関数はNULL値を自動的に除外して計算するため、NULLを0として扱うことはありません。
WHERE句はJOIN後の結果に適用されるため、JOINの種類とWHERE句の順序を理解することが重要です。
AVG関数はNULL値を自動的に除外して計算するため、NULLを0として扱うことはありません。
WHERE句はJOIN後の結果に適用されるため、JOINの種類とWHERE句の順序を理解することが重要です。
FAQ
Q: LEFT OUTER JOINでNULLが含まれる場合、AVG関数はどう扱いますか？
A: AVG関数はNULL値を除外して計算するため、NULLは平均値の計算に影響しません。
A: AVG関数はNULL値を除外して計算するため、NULLは平均値の計算に影響しません。
Q: WHERE句の条件はJOINの前後どちらに適用されますか？
A: WHERE句はJOIN後の結果に適用されるため、JOINの結果を絞り込みます。
A: WHERE句はJOIN後の結果に適用されるため、JOINの結果を絞り込みます。
Q: INNER JOINとLEFT OUTER JOINの違いは何ですか？
A: INNER JOINは両テーブルに一致する行のみを返し、LEFT OUTER JOINは左テーブルの全行を返します。
A: INNER JOINは両テーブルに一致する行のみを返し、LEFT OUTER JOINは左テーブルの全行を返します。
関連キーワード: SQL, LEFT OUTER JOIN, AVG関数、NULL処理、集約関数、データベース、平均値計算
\ せっかくなら /
データベーススペシャリストを
クイズ形式で学習しませんか？
クイズ画面へ遷移する→
すぐに利用可能！