平成23年度特別試験応用情報技術者試験午前問30

Question

“社員”表と“人事異動”表から社員ごとの勤務成績の平均を求める適切なSQL文はどれか。ここで，求める項目は，社員コード，社員名，勤務成績（平均）の3項目とする。

社員

社員コード	社員名	性別	生年月日	入社年月日
O1553	太田　由美	女	1970-03-10	1990-04-01
S3781	佐藤　義男	男	1943-11-20	1975-06-11
O8665	太田　由美	女	1978-10-13	1999-04-01

人事異動

社員コード	配属部門	配属年月日	担当勤務内容	勤務成績
O1553	総務部	1990-04-01	広報（社内報）	69.0
O1553	営業部	1998-07-01	顧客管理	72.0
S3781	資材部	1975-06-11	仕入在庫管理	70.0
S3781	経理部	1984-07-01	資金計画	81.0
S3781	企画部	1993-07-01	会社組織，分掌	95.0
O8665	秘書室	1999-04-01	受付	70.0

ア
SELECT 社員.社員コード, 社員名, AVG(勤務成績) AS "勤務成績（平均）"
FROM 社員, 人事異動
WHERE 社員.社員コード = 人事異動.社員コード
GROUP BY 勤務成績
イ
SELECT 社員.社員コード, 社員名, AVG(勤務成績) AS "勤務成績（平均）"
FROM 社員, 人事異動
WHERE 社員.社員コード = 人事異動.社員コード
GROUP BY 社員.社員コード, 社員.社員名
ウ
SELECT 社員.社員コード, 社員名, AVG(勤務成績)/COUNT(勤務成績) AS "勤務成績（平均）"
FROM 社員, 人事異動
WHERE 社員.社員コード = 人事異動.社員コード
GROUP BY 社員.社員コード, 社員.社員名
エ
SELECT 社員.社員コード, 社員名, MAX(勤務成績)/COUNT(*) AS "勤務成績（平均）"
FROM 社員, 人事異動
WHERE 社員.社員コード = 人事異動.社員コード
GROUP BY 社員.社員コード, 社員.社員名

キーボード：1〜4 で解答　→ 次の問題　← 前の問題

出典：平成23年度特別試験応用情報技術者試験午前問30

Answer 1

正解と解説を見る

正解：イ

社員ごとの平均を求めるので，2つの表を社員コードで結合した後，社員ごと（社員コード，社員名）にグループ化して AVG(勤務成績) を求めます。SELECT句に社員コードと社員名を書くため，GROUP BY句にもこの2つを指定する必要があります。

なお，“太田　由美”は2人いる（同姓同名）ので，社員名だけでグループ化すると別人の成績がまとめられてしまいます。社員コードでグループ化することが重要です。

ア: GROUP BY 勤務成績
誤り。勤務成績の値ごとにグループ化してしまうため，社員ごとの平均になりません。また，SELECT句の社員コードや社員名がGROUP BY句にないため，SQLとしても誤りです。
イ: GROUP BY 社員.社員コード, 社員.社員名で AVG(勤務成績)
正しい。社員ごとにグループ化し，AVGで勤務成績の平均を求めているので適切です。例えばO1553は (69.0＋72.0)÷2＝70.5 になります。
ウ: AVG(勤務成績)/COUNT(勤務成績)
誤り。AVGは既に平均値なので，さらに件数で割ると平均にはなりません。
エ: MAX(勤務成績)/COUNT(*)
誤り。最大値を件数で割っても平均にはなりません。平均は合計を件数で割った値（SUM/COUNT 又は AVG）です。

イ: GROUP BY 社員.社員コード, 社員.社員名で AVG(勤務成績)

平成23年度 特別試験 応用情報技術者試験 午前 問30