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
     
      5