応用情報技術者試験 科目A 無料問題 3 / 10 ・ 分野3: 技術要素
表「店舗売上」（店舗番号, 地域, 売上）に次の6行がある。（1, A, 300）（2, A, 500）（3, A, 400）（4, B, 200）（5, B, 260）（6, C, 350）。次の SQL 文で得られる店舗番号の集合はどれか。 SELECT 店舗番号 FROM 店舗売上 S1 WHERE S1.売上 > (SELECT AVG(S2.売上) FROM 店舗売上 S2 WHERE S2.地域 = S1.地域)
- {2, 5}
- {2, 3, 5, 6}
- {2, 3, 6}
- {2, 5, 6}
正解と解説を見る
正解: A
正解の理由
この副問合せは外側の行 S1 の地域を条件に使う相関副問合せで、S1 の行ごとに、その行と同じ地域の売上の平均を求め直す。地域 A の平均は (300+500+400)÷3＝400 で、400 より大きいのは店舗2（500）だけである。店舗3 は 400 で平均と等しいので入らない。地域 B の平均は (200+260)÷2＝230 で、店舗5（260）が入る。地域 C は店舗6 だけで平均は 350、自分自身と等しいので入らない。結果は {2, 5} になる。
違う選択肢
「{2, 5, 6}」… {2, 5, 6} は、各地域で売上が最も高い店舗である。地域 C は1店舗だけなので、その店舗は最高でもあり平均でもある。問合せは平均「より大きい」行を選ぶので、平均と等しい店舗6 は入らない。
「{2, 3, 6}」… 全6店舗の平均は 2,010÷6＝335 で、これより大きいのは店舗2・3・6 になる。副問合せの WHERE S2.地域 = S1.地域 が外側の行の地域と結び付いているので、比べる平均は地域ごとに変わる。
「{2, 3, 5, 6}」… 「以上」で比べると、平均とちょうど等しい店舗3（地域 A の平均 400）と店舗6（地域 C の平均 350）も入り、{2, 3, 5, 6} になる。SQL 文の比較は「>」なので、等しい行は入らない。
相関副問合せの読み方
副問合せの中に外側の表の相関名（ここでは S1）が出てきたら相関副問合せで、外側の行ごとに副問合せの結果が変わると読む。外側の表の相関名が出てこない副問合せは、一度だけ評価されて同じ値がすべての行に使われる。1行しかないグループでは、平均・最大・最小がその行の値と一致するので、「より大きい」と「以上」の違いが結果に出やすい。