史上最全oracle練習題____oracle

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

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

      select max(sal) from emp group by deptno;

第二步：根據第一步的結果和員工表進行關聯，擷取人員名稱

select a.empno,a.ename,a.sal,a.deptno from emp a join (select deptno, max(sal) max_sal from emp group by deptno ) b on a.deptno= b.deptno and a.sal = b.max_sal;

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

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

      Select deptno, avg(sal) avgSal from emp group bydeptno

      第二步：根據第一步的結果和員工表進行關聯。擷取人員名稱

3.取得部門中（所有人的）平均的薪水等級

第一步：獲得所有人的薪水等級

select e.empno,e.ename,e.deptno,g.grade from emp e join salgrade g on e.sal  between g.losal and g.hisal 

第二步：將第一步的結果用部門進行分組。然後擷取等級的平均值

select s.deptno ,avg(grade) from (select e.empno,e.ename,e.deptno,g.grade from emp e join salgrade g on e.sal  between g.losal and g.hisal ) s group by s.deptno

4.不準用組函數（Max），取得最高薪水

第一種方法：

第一步：將員工薪水降序排列

select empno,ename,sal from emp order by sal desc

第二步：取得第一條資料

select a.empno,a.ename,a.sal from (select empno,ename,sal from emp order by sal desc) a where rownum < = 1;

第二種方法：

第一步：將emp表當做2張表來處理。使用的笛卡爾乘積的方法進行比較。得到最大值以外的值。
select distinct a.sal from emp a join emp b on a.sal < b.sal 
第二步：獲得最大值
select a.sal,a.empno,a.ename from emp a where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal );
第三種方法：
第一步：將emp表當做2張表來處理。使用的笛卡爾乘積的方法進行比較。從大到小排序
select distinct a.sal from  emp a join emp b on a.sal > b.sal 
第二步：取得第一條資料
select  sal from (select distinct a.sal from  emp a join emp b on a.sal > b.sal ) where rownum <= 1

5,取得平均薪水最高的部門的部門編號。
第一種方法：
第一步：取得各部門的平均薪水。按desc排序。取得最大值
select e.deptno, avg(e.sal) avg_sal from emp e group by e.deptno  order by avg_sal desc ；
第二步：取得第一條資料
select a.deptno, a.avg_sal from (select e.deptno, avg(e.sal) avg_sal from emp e group by e.deptno  order by avg_sal desc ) a where rownum < =1;
第二種方法：
第一步：取得各部門的平均薪水
select avg(sal) avg_sal,e.deptno from emp e group by deptno;
第二步：使用彙總函式獲得平均薪水最大值
select max(avg_sal) from (select  avg(sal) avg_sal,e.deptno from emp e group by deptno)
第三步：將第一步和第二步擷取的結果進行關聯
select m.deptno from 
(select  avg(sal) avg_sal,e.deptno from emp e group by deptno) m 
right join 
(select max(avg_sal) max_sal from (select  avg(sal) avg_sal,e.deptno from emp e group by deptno)) n
on m.avg_sal = n.max_sal
第三種方法：
第一步：獲得各部門的最大值
select max(avg(sal)) from emp m group by deptno;
第二步：使用having過濾。獲得最大值
select a.deptno,avg(sal) avg_sal from emp a group by deptno   having avg(sal)  = (select max(avg(sal)) from emp m group by deptno);
6.取得平均薪水最高的部門的部門名稱

第一種方法：

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

select e.deptno ,avg(sal) avg_sal from emp e group by deptno order by avg_sal desc;

第二步：取得各部門的平均薪水最大值

select m.deptno, avg_sal from (select e.deptno ,avg(sal) avg_sal from emp e group by deptno order by avg_sal desc) m where rownum <=1 ;

第三步：跟部門表關聯。獲得部門名稱

select t.deptno,t.dname from dept t 

right join
  (select m.deptno, avg_sal from (select e.deptno ,avg(sal) avg_sal from emp e group by deptno order by avg_sal desc) m where rownum <=1) n
on t.deptno = n.deptno;

第二種方法：

第一步：獲得最高部門編號。和平均值最大值

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

第二步：關聯部門表

select  t.dname,a.sal from dept t right join emp a on t.deptno = a.deptno where a.sal = (select max(avg(sal)) max_sal from emp e  group by deptno);

第三種方法：

第一步：擷取平均薪水最高的部門編號

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

第二步：跟部門表關聯

select t.dname,t.deptno from dept t where t.deptno = (select e.deptno from emp e group by deptno having avg(sal) = (select max(avg(sal)) from emp group by deptno));

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

第一種方法：

第一步：獲得平均薪水最低的部門編號,升序排列

select m.deptno from (   select e.deptno, avg(sal) avg_sal from emp e group by deptno order by avg_sal asc) m where rownum <= 1;

第二步：獲得平均薪水層級最低的 部門編號 及平均薪水 及等級 

select s.grade,m.deptno,avg_sal from salgrade s

right join 

( select e.deptno, avg(sal) avg_sal from emp e group by deptno order by avg_sal asc) m on avg_sal 

between s.losal and s.hisal

where rownum < = 1

第三步：跟部門表關聯。獲得部門名稱

select t.dname ,t.deptno ,avg_sal from dept t
 right join 
  ( select s.grade,m.deptno,avg_sal from salgrade s right join ( select e.deptno, avg(sal) avg_sal from emp e group by deptno order by avg_sal asc) m on avg_sal

between s.losal and s.hisal where rownum < = 1)  n on t.deptno = n.deptno;

8.取得比普通員工（員工代碼沒有在mgr欄位上出現的）的最高薪水還要要的經理人姓名

第一步：獲得經理人編號

select distinct mgr from emp  where mgr is not null;

第二步：普通員工薪水不在mgr欄位裡邊的

select max(sal) max_sal from emp e where e.empno not in (select distinct mgr from emp  where mgr is not null);

第三步：獲得還要高的薪水經理人的姓名

select sal ,ename from emp s where  s.sal >(select max(sal) max_sal from emp e where e.empno not in (select distinct mgr from emp  where mgr is not null))

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

第一種方法：使用rownum排序

select * from (

select rownum r, t.*

from

  (

   select ename, sal from emp order by sal desc

  )    t

  where rownum <=5 )wherer>   0

第二種方法：

第一步：取得員工的最高薪水

select sal from emp order by sal desc ;

第二步：獲得前5名薪水

select sal from (select sal from emp order by sal desc ) where rownum <= 5;

第三步：跟員工表自關聯 獲得名稱

select e.ename,e.sal from emp e where e.sal in (select sal from (select sal from emp order by sal desc ) where rownum <= 5) order by sal desc;

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

第一步：取得全部薪水

select sal,ename from emp order by sal desc

第二步：獲得薪水第1名到第10名的員工薪水和姓名

select rownum r , ename ,sal from 
(select sal,ename from emp order by sal desc)
where rownum <= 10

第三步：排除前5個

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

11.取得最後入職的5名員工

第一步：降序排序最後入職

select ename,hiredate from emp order by hiredate desc；

第二步：獲得最後5名員工

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

12.取得每個薪水等級有多少員工

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

select s.grade,t.ename,t.sal from salgrade s join (select ename,sal from emp) t on t.sal between s.losal and s.hisal

第二步：獲得每個薪水等級的員工個數

select count(*),m.grade from (select s.grade,t.ename,t.sal from salgrade s join (select ename,sal from emp) t on t.sal between s.losal and s.hisal
) m group by m.grade

13.列出所有員工及直接上級的姓名
Select e.ename, nvl (m.ename, '沒有上級') mname from emp e left join emp m on e.mgr = m.empno


14.列出受雇日期早於其直接上級的所有員工的編號,姓名,部門名稱
select e.empno,e.ename,t.dname from emp e join emp a on e.hiredate < a.hiredate and e.mgr = a.empno join dept  t on e.deptno = t.deptno;


15.列出部門名稱和這些部門的員工資訊,同時列出那些沒有員工的部門.
select a.*,t.dname  from emp a right join dept t on t.deptno = a.deptno;


16.列出至少有一個員工的所有部門
Select dname, count(*) from emp e join dept d on e.deptno = d.deptno group by dname


17.列出薪金比"SMITH"多的所有員工資訊.
select * from emp where sal > (select sal from emp where ename = 'SMITH');


18.列出所有"CLERK"(辦事員)的姓名及其部門名稱,部門的人數.
1.首先獲得"CLERK"(辦事員)的姓名、部門編號
select e.deptno,e.ename,e.job from emp e where e.job = 'CLERK';
2.其次獲得相對應部門名稱
select t.deptno,t.dname,n.job,n.ename from dept t right join (select e.deptno,e.ename,e.job from emp e where e.job = 'CLERK') n on  n.deptno = t.deptno
3.其次各部門的人數
select count(*) tt ,j.dname from emp v join dept j on v.deptno = j.deptno group by j.dname
4.最後查詢出結果
select t1.ename,t1.deptno,t1.dname, t2.tt from
(select t.deptno,t.dname,n.job,n.ename from dept t join (select e.deptno,e.ename,e.job from emp e where e.job = 'CLERK') n on  n.deptno = t.deptno) t1
 join 
(select count(*) tt ,j.dname from emp v join dept j on v.deptno = j.deptno group by j.dname ) t2
on
t1.dname = t2.dname;


19.列出最低薪金大於1500的各種工作及從事此工作的全部僱員人數.
1.獲得薪水大於1500 的工作名稱 
select e.job from emp e group by e.job having min(sal) > 1500
2.獲得每個員工的工作人數
select count(*) cc from emp group by job 
3.獲得結果
select t1.job,t2.cc from (select e.job from emp e group by e.job having min(sal) > 1500) t1 join (select count(*) cc ,job from emp group by job 
) t2 on t1.job = t2.job


20.列出在部門"SALES"<銷售部>工作的員工的姓名,假定不知道銷售部的部門編號.
1.首先要獲得銷售部的部門編號
select deptno from dept where dname = 'SALES'
2.根員工表關聯。獲得員工的姓名
select * from emp where deptno = (select deptno from dept where dname = 'SALES')


21.列出與"SCOTT"從事相同工作的所有員工及部門名稱.
第一種方法：逐步分析
1.列出所有"SCOTT"的部門編號及從事的工作
select job from emp where ename = 'SCOTT'
2.列出從事‘SCOTT’的工作一樣的員工
select * from emp  where job = (select job from emp where ename = 'SCOTT') and ename != 'SCOTT'
3.關聯部門表。獲得部門名稱.並且排除SCOTT
select dname,t1.* from dept t  right join (select * from emp  where job = (select job from emp where ename = 'SCOTT') and ename != 'SCOTT') t1 on t.deptno = t1.deptno 


第二種方法：比較簡潔（推薦使用）
Select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno and e.job = (Select job from emp where ename = 'SC