oracle---plsql---樣本laobai

標籤：pre   部門   種類   ora   多級   highlight   class   管理者   自己的   

select * from scott.emp;--1 列出emp表中各部門的部門號，最高工資，最低工資    select deptno,max(sal),min(sal) from scott.emp group by deptno;2 列出emp表中各部門job為‘CLERK‘的員工的最低工資，最高工資 select min(sal),max(sal) from scott.emp where job=‘CLERK‘3 --對於emp中最低工資小於13000的部門，--列出job為‘CLERK‘的員工的 (部門號)，最低工資，最高工資      select min(sal),max(sal) from scott.emp where job=‘CLERK‘ and deptno in  (    select deptno from scott.emp group by deptno having min(sal)<‘13000‘    )  4 根據部門號由高而低，工資有低而高列出每個員工的姓名，部門號，工資select ename,deptno,sal from scott.emp order by deptno desc,sal  5列出‘張三‘所在部門中每個員工的姓名與部門號  select ename,deptno from   scott.emp where deptno in    (     select deptno from scott.emp where ename=‘WARD‘     )6列出每個員工的姓名，工作，部門號，部門名 select ename,job,scott.emp.deptno ,scott.dept.dname   from scott.emp  inner join scott.dept on scott.emp.deptno = scott.dept.deptno;     select ename,job,scott.emp.deptno ,scott.dept.dname   from scott.emp , scott.dept where   scott.emp.deptno = scott.dept.deptno;7列出emp中工作為‘CLERK‘的員工的姓名，工作，部門號，部門名 select ename,job,scott.emp.deptno ,scott.dept.dname   from scott.emp , scott.dept   where   scott.emp.deptno = scott.dept.deptno and job=‘CLERK‘;8 對於emp中有管理者的員工，列出姓名，管理者姓名（管理者外鍵為mgr） 同表自關聯。適合於設計多級菜單，多級種類。select s1.ename,s2.ename as 管理者 from (select * from scott.emp where mgr is not null) s1,scott.emp s2 where s1.mgr = s2.empno;9對於dept表中，列出所有部門名，部門號，select * from 同時列出各部門工作為‘CLERK‘的員工名與工作select d.*,e.ename,e.job from scott.dept d  join  (select deptno,ename,job from scott.emp where job=‘CLERK‘) e on d.deptno = e.deptno;10 對於工資高於本部門平均水平的員工，列出部門號，姓名，工資，按部門號排序  select deptno,ename,sal from scott.emp e  where sal>(select avg(sal) from scott.emp where deptno = e.deptno)  order by deptno;    select deptno,avg(sal) from scott.emp group by deptno  11 對於emp，列出各個部門中工資高於本部門平均水平的員工數和部門號，按部門號排序  select  count(1) 員工數,deptno from   ( select deptno,ename,sal from scott.emp e  where sal>(select avg(sal) from scott.emp where deptno = e.deptno)  ) group by deptno order by deptno;12   對於emp中工資高於本部門平均水平，人數多與1人的，列出部門號，人數，按部門號排序 select  count(1) 員工數,deptno from   ( select deptno,ename,sal from scott.emp e  where sal>(select avg(sal) from scott.emp where deptno = e.deptno)  ) group by deptno having count(1)>1 order by deptno;13 對於emp中低於自己工資至少5人的員工，列出其部門號，姓名，工資，[以及工資少於自己的人數] --找出有比自己工資少的人select t.*  from(  select deptno,ename,sal,empno from scott.emp   where sal not in  (    select  sal from (   select distinct(sal) ,rownum r from scott.emp  order by  sal )    where r<6    ) ) t ,scott.emp where scott.emp.sal<t.sal;      select a.deptno,a.ename,a.sal,(select count(b.ename) from    emp as b where b.sal<a.sal) as 人數 from scott.emp as a    where (select count(b.ename) from emp as b where b.sal<a.sal)>5    

　　

oracle---plsql---樣本laobai