AND e.deptno = d.deptno
AND e.deptno = temp.dno;
11：列出所有“clerk”的姓名及其部门名称，部门人数，工资等级
SELECT e.ename , d.dname ,temp.count,s.grade
FROM emp e, dept d,(
SELECT deptno dno,COUNT(empno) count
FROM emp www.2cto.com
GROUP BY deptno) temp,salgrade s
WHERE job=&＃39;CLERK&＃39;
AND e.deptno = d.deptno
AND d.deptno = temp.dno
AND e.sal BETWEEN s.losal AND s.hisal;
12：列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数及所在部门名称，位置，平均工资
SELECT t.job,t.count,d.dname,e.ename,reg.avg
FROM dept d,(
SELECT e.job,COUNT(e.empno) count
FROM emp e
GROUP BY e.job
HAVING MIN(e.sal)>1500
)t,emp e,(
SELECT deptno dno,AVG(sal) avg
FROM emp
GROUP BY deptno
)reg
WHERE e.deptno = d.deptno AND e.job = t.job
AND e.deptno = reg.dno;
13：列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
select a1.ename,a2.dname,a1.mgr,a3.grade from emp a1,dept a2,salgrade a3,(select avg(sal) mysal from emp) a4 where a1.deptno = a2.deptno and sal between a3.losal and a3.hisal and a1.sal > a4.mysal; www.2cto.com
14：列出与SCOTT从事相同工作的所有员工及部门名称，部门人数
SELECT e.empno,e.ename,e.job,d.dname,temp.count FROM emp e,dept d,(SELECT deptno dno,COUNT(empno) count FROM emp GROUP BY deptno) temp
WHERE e.job=(SELECT job FROM emp WHERE ename=&＃39;SCOTT&＃39;) AND e.ename<>&＃39;SCOTT&＃39; AND e.deptno=d.deptno AND temp.dno=e.deptno;
15：列出在每个部门工作的员工数量，平均工资，和平均服务年限
SELECT d.dname,count(e.empno),avg(e.sal),round(avg(sysdate-e.hiredate)/365) from emp e,dept d where e.deptno(+)=d.deptno GROUP BY d.dname;
16：列出各种工作的最低工资及此雇员姓名
select a1.ename,a1.job,a1.sal from emp a1,(select job,min(sal) min_sal from emp group by job) a2 where a1.job=a2.job and a1.sal=a2.min_sal; www.2cto.com
17：列出各个部门的MANAGER的最低薪金，姓名，部门名称，部门人数
select e.sal,e.ename,d.dname, count from(select job,min(sal) sal,ename,deptno from emp where job=&＃39;MANAGER&＃39;GROUP BY job,ename,deptno) e,
(select d.deptno,d.dname,count(e.empno) count fromemp e,dept d wheree.deptno=d.deptno GROUP BY d.deptno,d.dname) d
where e.deptno=d.deptno;
Oracle分页（根据ROWNUM分页）
select * from (select a1.*,rownum rn from (select ename,sal from emp order by sal) a1 where rownum<=10) where rn >=6;
作者 夜半风雨
京公网安备 11010802041100号