Database Systems Engineer Examination

Ｈ１５午後I 問２（ITEC 2005予想問題集 午後I 問２－１）

設問１（１）：外結合

SELECT 部門.部門コード, 部門.部門名, COUNT(社員.社員番号),
　　COUNT(受講集計.社員番号),
　　SUM(年間受講ポイント数 + 繰越ポイント数),
　　SUM(使用ポイント数)
FROM 部門, 社員 LEFT JOIN
　　(SELECT 社員番号, SUM(受講ポイント数) AS 使用ポイント数
　　FROM 受講, コース
　　WHERE 受講.コースコード = コース.コースコード
　　AND 受講開始年月日 BETWEEN '2002-04-01' AND '2002-09-30'
　　GROUP BY 社員番号 ) 受講集計
　　ON 社員.社員番号 = 受講集計.社員番号
WHERE 部門.部門コード = 社員.部門コード
GROUP BY 部門.部門コード, 部門.部門名
ORDER BY 部門.部門コード

SELECT コース.コースコード, コース.コース名
FROM コース, 必修コース, 社員
WHERE 社員番号 = 123456
AND 必修コース.コースコード = コース.コースコード
AND (必修コード.担当職務コード = 社員.担当職務コード１ OR
　　必修コード.担当職務コード = 社員.担当職務コード２)
AND コース.コースコード NOT IN
　　(SELECT コースコード
　　FROM 受講
　　WHERE 社員番号 = 123456)

SELECT 部門.部門コード, 部門.部門名, COUNT(社員.社員番号),
　　COUNT(受講集計.社員番号),
　　SUM((年間受講ポイント数 + 繰越ポイント数) * 兼務比率) AS 利用可能ポイント数合計,
　　SUM(使用ポイント数 * 兼務比率) AS 利用ポイント数合計
FROM 部門, 所属, 社員 LEFT JOIN
　　(SELECT 社員番号, SUM(受講ポイント数) AS 使用ポイント数
　　FROM 受講, コース
　　WHERE 受講.コースコード = コース.コースコード
　　AND 受講開始年月日 BETWEEN '2002-04-01' AND '2002-09-30'
　　GROUP BY 社員番号 ) 受講集計
　　ON 社員.社員番号 = 受講集計.社員番号
WHERE 部門.部門コード = 所属.部門コード AND 所属.社員番号 = 社員.社員番号
GROUP BY 部門.部門コード, 部門.部門名
ORDER BY 部門.部門コード