oracle的一些sql查询例子，（子查询、分组查询、求和、求平均数等等）都囊括到了。（2）

因为不知道为什么博客篇幅的限制，本文为上篇博文作的补充。
各位朋友可接着小弟上篇的博文来看。
–11：列出所有“clerk”的姓名及其部门名称，部门人数，工资等级

 select e.ename,d.dname,count(e.deptno),s.grade
   from scott.emp e,scott.dept d,scott.salgrade s
   where e.deptno=d.deptno
   and e.sal>=s.losal
   and e.sal<=s.hisal
   and job='CLERK'
   group by e.ename,d.dname,s.grade;

–12：列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数及所在部门名称，位置，平均工资

 select t1.job,count(t1.job),d.dname,d.loc,avg(t1.sal)
   from scott.dept d,(select e.job,e.sal,e.deptno 
                         from scott.emp e
                        where e.sal>1500) t1
   where t1.deptno=d.deptno
   group by t1.job,d.dname,d.loc;

–13：列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级

select avg(e.sal) as xx from emp e;

select * from emp;

select e.ename,d.dname,e.mgr,s.grade
 from emp e,dept d,salgrade s,
 (select avg(e.sal) as xx
  from emp e) a
  where e.deptno = d.deptno 
  and e.sal>a.xx 
   and e.sal>=s.losal
   and e.sal<=s.hisal;

–14：列出与SCOTT从事相同工作的所有员工及部门名称，部门人数

select e.job from emp e where e.ename = 'SCOTT';

select * from dept;

select e.ename,d.dname,count(d.deptno)
 from emp e,dept d,
 (select e.job from emp e where e.ename = 'SCOTT') t 
where e.deptno = d.deptno(+) 
and e.job = t.job
group by e.ename,d.dname;  

–16：列出各种工作的最低工资及此雇员姓名

select e.ename ,e.job,e.sal
   from scott.emp e,scott.dept d ,(select e1.job,min(e1.sal) minsal 
                                       from scott.emp e1 
                                      group by e1.job ) t1
   where e.deptno=d.deptno
   and e.sal=t1.minsal;

–17：列出各个部门的MANAGER的最低薪金，姓名，部门名称，部门人数

select e.ename,d.dname,count(e.deptno),e.sal
   from scott.emp e,scott.dept d
   where e.deptno=d.deptno
   and e.job='MANAGER'
   group by e.ename,d.dname,e.sal;

–18. Oracle分页（根据ROWNUM分页）

  select * from
   (select t.*, rownum rn 
   from (select * from scott.emp) t
   where rownum<=20)
   where rn>=10;