Oracle11g SQL基礎 ５章確認問題 解答＆解説

設問１ †

departments表の全ての行を数え、その数を表示しなさい。

SELECT COUNT(*)
FROM departments;

※department_id列には必ず値が入っているため、「COUNT(department_id)」でもよい。

設問２ †

employees表の「commission_pct」がNULLでない行を数え、その数を表示しなさい。

SELECT COUNT(*)
FROM employees
WHERE commission_pct IS NOT NULL;

※集合関数の特徴である「NULLを無視して計算する」をしっかり理解していれば、以下の解答を思いつくはず。

SELECT COUNT(commission_pct)
FROM employees;

設問３ †

employees表から「job_id」が'_MAN'で終わる社員の人数および「salary」の平均額と最高額と合計額を表示しなさい。人数の別名は"人数"、平均額の列名は"給与平均額"、最高額の列名は"給与最高額"、合計額の列名は"給与合計額"とする。

SELECT COUNT(*) AS "人数"
, AVG(salary) AS "給与平均額"
, MAX(salary) AS "給与最高額"
, SUM(salary) AS "給与合計額"
FROM employees
WHERE job_id LIKE '%\_MAN' ESCAPE '\';

設問４ †

employees表から「manager_id」ごとの社員数を求めなさい。列は「manager_id」とその社員数を表示すること。

SELECT manager_id
, COUNT(*)
FROM employees
GROUP BY manager_id;

設問５ †

employees表から部門ごとの平均給与を、平均給与の降順で表示しなさい。列は「department_id」「salaryの平均」とし、列名をそれぞれ"部門番号"、"部門平均額"とすること。

SELECT department_id AS "部門番号"
, AVG(salary) AS "部門平均額"
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;

※ORDER BY句は以下でもOK。

ORDER BY "部門平均額" DESC
ORDER BY 2 DESC

設問６ †

employees表から同じ「department_id」の社員数が５人以下の「department_id」と「salary」の合計を表示しなさい。「salary」の合計の列名は"部門別給与合計"とする。

SELECT department_id
, SUM(salary) AS "部門別給与合計"
GROUP BY department_id
HAVING COUNT(*) <= 5;

※GROUP BY句とHAVING句はどちらが先でもOK。

設問７ †

employees表から「job_id」が'AD_PRES'の社員を除く中から、各「department_id」の「salary」の平均が5000以上の「department_id」と「salary」の平均を表示しなさい。「salary」の平均の列名は"給与平均"とする。

SELECT department_id
, AVG(salary) AS "給与平均"
FROM employees
WHERE job_id != 'AD_PRES'
GROUP BY department_id
HAVING AVG(salary) >= 5000;

※「WHERE句には集合演算子が使えず、HAVING句は集合演算子が使える」という性質を理解していれば、以下の解答が思いつく・・・かも？

SELECT department_id
, AVG(salary) AS "給与平均"
FROM employees
GROUP BY department_id
HAVING job_id != 'AD_PRES' AND AVG(salary) >= 5000;

※しかしこれは実行してわかるとおり、エラーとなる。グループ化したデータにjob_idが存在しないためエラーになるのだが、この理論（理屈）が解るだろうか？
※何よりも授業でも説明したとおり効率が悪いので、こんなSELECT文は作るべきではない！

設問８ †

employees表から、部門（department_id）毎に職種（job_id）がいくつあるか求めて表示しなさい。

SELECT department_id
, COUNT(DISTINCT job_id)
FROM employees
GROUP BY department_id;

※同じ職種を数えてしまっては困るため、DISTINCTキーワードが必要！

設問９ †

employees表から「job_id」が'AD_PRES'の社員を除く中から、入社日から今日までの勤務年数別の人数を数えて表示しなさい。勤務年数の多い順に並べること。

SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date) / 12)
, COUNT(*)
FROM employees
GROUP BY TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date) / 12)
ORDER BY 1 DESC;

設問１０ †

Employees表から「job_id」列のデータを重複無しですべて表示しなさい。た だし、DISTINCTキーワードを使わずに表示すること。

SELECT job_id
FROM enployees
GROUP BY job_id;

※重複なしということは、同じ値を表示しないということ。であれば、グループ化してしまえば同じ意味である。
（効率がいいか悪いかは別の話）

戻る