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

来源：互联网发布：京东万象数据编辑：程序博客网时间：2024/05/29 13:28

因为不知道为什么博客篇幅的限制，本文为上篇博文作的补充。
各位朋友可接着小弟上篇的博文来看。
–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.jobgroup 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;

0 0