• <nav id="wkkge"><strong id="wkkge"></strong></nav>
  • <menu id="wkkge"></menu>
  • Oracle教程
    Oracle練習題

    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 

     

     

    全部教程
  • <nav id="wkkge"><strong id="wkkge"></strong></nav>
  • <menu id="wkkge"></menu>
  • 面对面棋牌游戏