oracle 學習分組查詢、分組函數、練習題答案

oracle 學習筆記

–分組查詢

MIN MAX AVG COUNT SUM NVL

–NVL(參數一，參數二) 函數可以使分組函數強制包含含有空值的記錄

–NVL2(參數一，參數二，參數三)

SELECT nvl2(null,’男’,’女’) FROM DUAL;–女

SELECT nvl2(‘M’,’男’,’女’) FROM DUAL; –男

select * from emp;

SELECT MIN(sal) FROM emp;

SELECT MAX(sal) FROM emp;

SELECT AVG(NVL(comm,0)) FROM emp;

SELECT COUNT(DISTINCT deptno) FROM dept;

SELECT SUM(comm) FROM emp;

練習1

• 1.查詢部門20的員工，每個月的工資總和及平均

工資。

SELECT SUM(NVL(sal,0)) “工資總和”,AVG(NVL(sal,0)) “平均工資”

FROM emp

WHERE deptno=20;

• 2.查詢工作在CHICAGO的員勞工數，最高工資及

最低工資。

SELECT COUNT(*) “員勞工數”,MAX(sal) “最高工資”,MIN(sal) “最低工資”

FROM emp e,dept d

WHERE d.loc=’CHICAGO’;

• 3.查詢員工表中一共有幾種崗位類型。

SELECT DISTINCT job FROM emp;

GROUP BY 分組函數

SELECT deptno,ROUND(AVG(NVL(sal,0)),2)

FROM emp

GROUP BY deptno;

查詢每個部門每個崗位的工資總和。

SELECT job,SUM(sal)

FROM emp

GROUP BY job;

SELECT deptno,job,SUM(sal)

FROM emp

GROUP BY deptno,job;

–如果在查詢中使用了組函數，任何不在組函數中

–的列或表達式都必須包含在GROUP BY子句中 。

select deptno,count(ename) from emp –錯誤

select deptno,count(ename) from emp group by deptno;

練習2

1.查詢每個部門的部門編号，部門名稱，部門人

數，最高工資，最低工資，工資總和，平均工資。

select d.deptno,d.dname,count(*),

max(sal),min(sal),sum(sal),avg(nvl(sal,0))

from emp e left outer join dept d on e.deptno=d.deptno

group by d.deptno, d.dname;

2.查詢每個部門，每個崗位的部門編号，部門名

稱，崗位名稱，部門人數，最高工資，最低工資

，工資總和，平均工資。

select d.deptno,d.dname,e.job,count(e.empno),

max(sal),min(sal),sum(sal),avg(nvl(sal,0))

from emp e right outer join dept d on e.deptno=d.deptno

group by d.deptno, d.dname,e.job

order by d.deptno;

3.查詢每個經理所管理的人數，經理編号，經理姓名，要求包括不是經理的人員資訊。

select mgr.empno,mgr.ename,count(e.empno)

from emp mgr,emp e

where mgr.empno=e.mgr(+)

–order by mgr.empno;

group by mgr.empno,mgr.ename;

使用 HAVING 子句

• 不能在 WHERE子句中限制組

• 可以通過 HAVING 子句限制組

select deptno,max(sal)

from emp

group by deptno

having max(sal)>2900;

SELECT語句執行過程：

– 1.通過FROM子句中找到需要查詢的表；

– 2.通過WHERE子句進行非分組函數篩選判斷；

– 3.通過GROUP BY子句完成分組操作；

– 4.通過HAVING子句完成組函數篩選判斷；

– 5.通過SELECT子句選擇顯示的列或表達式及組函數；

– 6.通過ORDER BY子句進行排序操作。

練習3

• 1.查詢部門人數大于2的部門編号，部門名稱，

部門人數。

select d.deptno,d.dname,count(e.empno)

from emp e right outer join dept d on e.deptno=d.deptno

group by d.deptno, d.dname

having count(e.empno)>2

order by d.deptno;

• 2.查詢部門平均工資大于2000，且人數大于2的

部門編号，部門名稱，部門人數，部門平均工資

，并按照部門人數升序排序。

select d.deptno,d.dname,count(e.empno),avg(nvl(sal,0))

from emp e right outer join dept d on e.deptno=d.deptno

group by d.deptno, d.dname

having avg(nvl(sal,0))>2000

order by count(e.empno);

課後作業

• 1.查詢部門平均工資在2500元以上的部門名稱及平均工資。

select d.deptno,d.dname,avg(nvl(sal,0))

from emp e,dept d

where e.deptno=d.deptno

group by d.deptno,d.dname

having avg(nvl(sal,0)) >2500;

2.查詢員工崗位中不是以“SA”開頭并且平均工資在2500

元以上的崗位及平均工資，并按平均工資降序排序。

select job,avg(nvl(sal,0))

from emp

group by job

having job not like ‘SA%’;

• 3.查詢部門人數在2人以上的部門名稱、最低工資、最高

資 工資,并對求得的工資進行四舍五入到整數位。

select d.dname,round(min(sal),0),round(max(sal),0),count(e.empno)

from emp e, dept d

where e.deptno=d.deptno

group by d.dname

having count(e.empno)>2;

• 4.查詢崗位不為SALESMAN，工資和大于等于2500的崗位及

每種崗位的工資和。

select job,sum(sal)

from emp

group by job

having job not like ‘SALESMAN’ and sum(sal)>=2500;

• 5.顯示經理号碼和經理姓名，這個經理所管理者工的最低

工資，沒有經理的KING也要顯示，不包括最低工資小于

3000的，按最低工資由高到低排序。

select m.ename,min(e.sal)

from emp e left outer join emp m

on e.mgr=m.empno

group by m.ename

having min(e.sal)>=3000;

• 6.寫一個查詢，顯示每個部門最高工資和最低工資的差額。

select deptno,max(sal),min(sal),max(sal)-min(sal)

from emp

group by deptno;