平成24年度 春期 午前Ⅱ 問17
データベース
副問合せに関する問題
“商品”表に対して，次のSQL文を実行して得られる仕入先数は幾つか。
SELECT DISTINCT 仕入先コード FROM 商品
WHERE (販売単価 - 仕入単価) >
(SELECT AVG (販売単価 - 仕入単価) FROM 商品)
商品
| 商品コード | 商品名 | 販売単価 | 仕入先コード | 仕入単価 |
|---|---|---|---|---|
| A001 | A | 1,000 | S1 | 800 |
| B002 | B | 2,500 | S2 | 2,300 |
| C003 | C | 1,500 | S2 | 1,400 |
| D004 | D | 2,500 | S1 | 1,600 |
| E005 | E | 2,000 | S1 | 1,600 |
| F006 | F | 3,000 | S3 | 2,800 |
| G007 | G | 2,500 | S3 | 2,200 |
| H008 | H | 2,500 | S4 | 2,000 |
| I009 | I | 2,500 | S5 | 2,000 |
| J010 | J | 1,300 | S6 | 1,000 |
- ア1
- イ2
- ウ3
- エ4
答えと解説を見る
✓ これが正解ウ3
解説
差額の平均は360で、それを超えるのはS1、S4、S5の3社です。
このSQL文は、副問合せで全商品の販売単価から仕入単価を引いた差額の平均を求め、その平均より差額が大きい商品の仕入先コードを、DISTINCTで重複を除いて取り出します。各商品の差額は、Aが200、Bが200、Cが100、Dが900、Eが400、Fが200、Gが300、Hが500、Iが500、Jが300です。合計は3,600なので、平均は3,600÷10＝360です。360を超えるのはD（S1）、E（S1）、H（S4）、I（S5）の4行です。仕入先コードの重複を除くとS1、S4、S5が残り、仕入先数は3になります。これは、副問合せで求めた集計値を条件に使い、DISTINCTで同じ値の行を一つにまとめる考え方です。
ほかの選択肢はなぜ違うのか
- ア1：1社だけになるのは、たとえば差額が最も大きい商品Dの仕入先だけを数えた場合です。平均を超えるかどうかで行を絞ると、条件を満たす行は複数の仕入先にまたがり、重複を除いても3社が残ります。
- イ2：2は、仕入先ごとに全商品の差額が平均を超えるものだけを数えた値です。S1は差額200の商品Aを持つので外れ、S4とS5の2社が残りますが、SQL文は商品の行ごとに比べているので、この数え方は誤りです。
- エ4：4は、平均を超えた4行を、DISTINCTで重複を除かずにそのまま数えた値です。DとEはどちらも仕入先S1なので、仕入先の数としては一つにまとめる必要があります。
出典：平成24年度 春期 システム監査技術者試験 午前Ⅱ 問17
この解説に誤りを見つけたら教えてください。直して、直した記録を残します。誤りを報告する（メールが開きます）