Oracle經典練習題

1、取得每個部門最高薪水的人員名稱

第一步：取得每個部門的最高薪水

select deptno, max(sal) maxSal from emp group by deptno

第二步：獲取結果

select ename, sal, e.deptno from emp e join ( select deptno, max(sal) maxSal from emp group bydeptno) t on e.deptno = t.deptno and e.sal = t.maxSal

2、哪些人的薪水在部門的平均薪水之上

第一步：獲取每個部門的平均薪水

select deptno, avg(sal) avgSal from emp group by deptno

第二步：獲取結果

select ename, sal from emp e join (select deptno, avg(sal) avgSal from emp group by deptno) t on e.deptno = t.deptno and e.sal > t.avgSal

3、取得部門中（所有人的）平均的薪水等級，如下：

第一步：獲取每個員工的薪水等級

select deptno, grade from emp e join salgrade g on e.sal between g.LOSAL and g.HISAL

第二步：將第一步的結果用部門編號進行分組，得到結果

select deptno, avg(grade) from (select deptno, grade from emp e join salgrade g on e.sal between g.LOSAL and g.HISAL) group by deptno

4、不準用組函數（Max），取得最高薪水（給出兩種解決方案）

第一種(rownum)：

● 將員工薪水降序排列

select * from emp order by sal desc

● 取得查詢結果的第一條數據

select sal from ( select * from emp order by sal desc) where rownum = 1

第二種(自關聯)

● 將Emp表當成2張表來用，進行比較，得到最大值以外的值。

select distinct e.sal from emp e join emp t on e.sal < t.sal

● 獲取最大薪水

select sal from emp where sal not in (select distinct e.sal from emp e join emp t on e.sal < t.sal)

5、取得平均薪水最高的部門的部門編號（至少給出兩種解決方案）

第一種：

select * from (select deptno from emp group by deptno order by avg(sal) desc) where rownum = 1

第二種：

● 獲取每個部門的平均薪水

select deptno, avg(sal) from emp group by deptno

● 取得查詢結果的最高數據

select max(avgSal) from (select deptno, avg(sal) avgSal from emp group by deptno)

● 取得結果

select deptno from (select deptno, avg(sal) avgSal from emp group by deptno) s join (select max (avgSal) maxAvgSal from (select deptno, avg(sal) avgSal from emp group by deptno)) t on s.avgSal = t.maxAvgSal

第三種(聚合函數可以嵌套使用)

select deptno from emp group by deptno having avg(sal) = (select max(avg(sal)) from emp group by deptno);

6、取得平均薪水最高的部門的部門名稱

● 參考上一題的結果(取得部門編號)

select deptno from emp group by deptno having avg(sal) = (select max(avg(sal)) from emp group by deptno);

● 取得部門名稱

Select dname from dept where deptno = (select deptno from emp group by deptno having avg(sal) = (select max(avg(sal)) from emp group by deptno))

7、求平均薪水的等級最低的部門的部門名稱

第一步：取得每個部門的平均薪水

select deptno, avg(sal) avgSal from emp group by deptno

第二步：獲取每個部門的平均 薪水等級

select deptno, avgSal, grade from salgrade g join ( select deptno, avg(sal) avgSal from emp group by deptno ) t on t.avgSal between g.losal and g.hisal

第三步：取得最低的等級

select min(grade) from (select deptno, avgSal, grade from salgrade g join ( select deptno, avg(sal) avgSal from emp group by deptno ) t on t.avgSal between g.losal and g.hisal)

第四步：獲取部門編號

Select deptno from (select deptno, avgSal, grade from salgrade g join ( select deptno, avg(sal) avgSal from emp group by deptno ) t on t.avgSal between g.losal and g.hisal) where grade = (select min(grade) from (select deptno, avgSal, grade from salgrade g join ( select deptno, avg(sal) avgSal from emp group by deptno ) t on t.avgSal between g.losal and g.hisal))

第五步：取得部門名稱

Select dname from dept where deptno in (Select deptno from (select deptno, avgSal, grade from salgrade g join ( select deptno, avg(sal) avgSal from emp group by deptno ) t on t.avgSal between g.losal and g.hisal) where grade = (select min(grade) from (select deptno, avgSal, grade from salgrade g join ( select deptno, avg(sal) avgSal from emp group by deptno ) t on t.avgSal between g.losal and g.hisal)))

8、取得比普通員工(員工代碼沒有在mgr字段上出現的)的最高薪水還要高的經理人姓名

第一步：取得所有經理的員工編號

select distinct mgr from emp where mgr is not null

第二步：取得普通員工的最高薪水

Select max(sal) maxSal from emp where empno not in (select distinct mgr from emp where mgr is not null )

第三步：獲取結果

select ename , sal from emp where empno in (select distinct mgr from emp where mgr is not null) and sal > (Select max(sal) maxSal from emp where empno not in (select distinct mgr from emp where mgr is not null ))

9、取得薪水最高的前五名員工

select *
from 
(
 select rownum r, t.* 
 from 
  (
    Select ename, sal from emp order by sal desc
  )  t  
  where rownum <=5 
)where r>0

10、取得薪水最高的第六到第十名員工

select *
from 
(
 select rownum r, t.* 
 from 
  (
    Select ename, sal from emp order by sal desc
  )  t  
  where rownum <=10 
)where r>5

11、取得最后入職的5名員工

Select * from ( select ename, hiredate from emp order by hiredate desc ) where rownum <= 5