Oracle11g SQL基礎 5章確認問題 解答&解説
設問1 †
departments表の全ての行を数え、その数を表示しなさい。
SELECT COUNT(*) FROM departments;
※department_id列には必ず値が入っているため、「COUNT(department_id)」でもよい。
設問2 †
employees表の「commission_pct」がNULLでない行を数え、その数を表示しなさい。
SELECT COUNT(*) FROM employees WHERE commission_pct IS NOT NULL;
※集合関数の特徴である「NULLを無視して計算する」をしっかり理解していれば、以下の解答を思いつくはず。
SELECT COUNT(commission_pct) FROM employees;
設問3 †
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 '\';
設問4 †
employees表から「manager_id」ごとの社員数を求めなさい。列は「manager_id」とその社員数を表示すること。
SELECT manager_id , COUNT(*) FROM employees GROUP BY manager_id;
設問5 †
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
設問6 †
employees表から同じ「department_id」の社員数が5人以下の「department_id」と「salary」の合計を表示しなさい。「salary」の合計の列名は"部門別給与合計"とする。
SELECT department_id , SUM(salary) AS "部門別給与合計" GROUP BY department_id HAVING COUNT(*) <= 5;
※GROUP BY句とHAVING句はどちらが先でもOK。
設問7 †
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文は作るべきではない!
設問8 †
employees表から、部門(department_id)毎に職種(job_id)がいくつあるか求めて表示しなさい。
SELECT department_id , COUNT(DISTINCT job_id) FROM employees GROUP BY department_id;
※同じ職種を数えてしまっては困るため、DISTINCTキーワードが必要!
設問9 †
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;
設問10 †
Employees表から「job_id」列のデータを重複無しですべて表示しなさい。た だし、DISTINCTキーワードを使わずに表示すること。
SELECT job_id FROM enployees GROUP BY job_id;
※重複なしということは、同じ値を表示しないということ。であれば、グループ化してしまえば同じ意味である。
(効率がいいか悪いかは別の話)