• ROWNUM
–ROWNUM是一个伪列，伪列是类似于表中的列，而
实际并没有存储在表中的特殊列；
–ROWNUM的功能是在每次查询时，返回结果集的顺序号，
这个顺序号是在记录输出时才一步一步产生的，第一行
显示为1，第二行为2，以此类推。
• ROWNUM
ROWNUM使用的注意点：
• 1.如下SQL语句，SELECT * FROM EMP WHERE ROWNUM>2;查询
不到任何记录，因为ROWNUM是在记录输出时才生成，且总是
从1开始，所以输出的第一条记录不满足>2的条件，被过滤掉，
第二条的ROWNUM又成了1，又不满足>2的条件，又被过滤掉，
依此类推，所以永远没有满足条件的记录，返回为空。所以
对于ROWNUM只能执行<、<=运算，不能执行>、>=或一个区间
运算Between..And等
•2.ROWNUM和ORDER BY一起使用时，因为ROWNUM在记录输出时
生成，而ORDER BY子句在最后执行，所以当两者一起使用时，
需要注意ROWNUM实际是已经被排了序的ROWNUM
• TOP-N查询
–Top-N查询主要是实现表中按照某个列排序，输出最大或
最小的N条记录功能。
•Top-N分析语法：
SELECT [列名], ROWNUM
FROM (SELECT [列名]
FROM 表名
ORDER BY Top-N操作的列 ASC|DESC)
WHERE ROWNUM <= N;
•ASC:查询最小的N条记录
•DESC：查询最大的N条记录
分页
• 分页查询
–在Oracle中，利用ROWNUM的特性，可以实现数据库端的分
页查询，查询语法为：
–1.当未指定需要按照某列排序，语法为：
SELECT b.*
FROM (SELECT ROWNUM rn,[ 1, 2,....n]
列名 列名 列名
FROM 1,[ 2,... n]
表名 表名 表名
WHERE [AND] ROWNUM <=目标页数*每页记录数) b
WHERE rn > (目标页数-1)*每页记录数
--
或 （一般用下面这种模式）
SELECT b.*
FROM (SELECT ROWNUM rn,[ 1, 2,....n]
列名 列名 列名
FROM 1,[ 2,... n]
表名 表名 表名
[WHERE 条件表达式]) b
WHERE rn <= i*j and rn > (i-1)*j
i为第i页，j为每页的行数
第七章
练习一
1.查询入职日期最早的员工姓名，入职日期
select e.ename,e.hiredate
from emp e
WHERE e.hiredate in (select min(HIREDATE) from emp group by e.ename);
2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称
-- 下面是查询测试
-- select e.empno,e.ename, e.job,sal,e.deptno,dname,loc
-- from emp e ,dept d
-- where e.deptno = d.deptno
-- and d.loc='CHICAGO';
下面是结果：
select e.ename,e.sal,d.dname,d.loc
from emp e,DEPT d
where e.deptno = d.deptno
and e.sal> any (select sal from emp where ename='SMITH')
and d.loc ='CHICAGO'
order by e.sal asc;
3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期
select e.ename,e.hiredate,e.deptno
from emp e
where e.hiredate < all (select HIREDATE from emp where deptno=20);
4.查询部门人数大于所有部门平均人数的部门编号，部门名称，部门人数
select d.deptno,d.dname,count(e.ename)
from emp e , dept d
where d.DEPTNO = e.DEPTNO
group by d.DEPTNO,d.DNAME
HAVING (count(e.ename)>
(select count(ename)/count(DISTINCT deptno) from emp));
第七章
练习二
1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工
SELECT ENAME ,HIREDATE
FROM EMP
WHERE HIREDATE < ANY (SELECT HIREDATE FROM EMP where DEPTNO=10)
AND DEPTNO <> 10 ;
2.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工
SELECT ENAME ,HIREDATE
FROM EMP
WHERE HIREDATE < ALL (SELECT HIREDATE FROM EMP where DEPTNO=10)
AND DEPTNO <> 10 ;
-- 查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工
SELECT ENAME , JOB , DEPTNO
FROM EMP
WHERE JOB = ANY (SELECT JOB FROM EMP WHERE DEPTNO =10)
AND DEPTNO <> 10;
练习三
1.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名，职位，
不包括10部门员工
select m.ename, m.job,m.mgr,m.DEPTNO
from emp m
where (m.job,m.mgr) in (select e.job,e.mgr from emp e where e.deptno=10)
and m.DEPTNO<>10;
-- in 可以换成 = any
2.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名，职位，
不包括10部门员工
select m.ename, m.job,m.mgr,m.DEPTNO
from emp m
where (m.job in (select e.job from emp e where e.deptno=10) and m.DEPTNO<>10)
or (m.mgr in (select e.mgr from emp e where e.deptno=10) and m.DEPTNO<>10);
练习四：
1.查询比自己职位平均工资高的员工姓名、职位，部门名称，职位平均工资
SELECT
E.ename,E.JOB,D.dname,b.salavg 职位平均工资,e.sal 个人工资
FROM emp E ,(SELECT JOB,AVG(sal) salavg FROM emp GROUP BY emp.JOB)b,dept d
WHERE
e.DEPTNO = d.DEPTNO
and E.sal > b.salavg
and e.job = b.job
order by 2;
1.1 查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资
SELECT a.ename, a.sal, a.deptno, b.salavg
FROM emp a, (SELECT deptno, avg(sal) salavg
FROM emp
GROUP BY deptno) b
WHERE a.deptno = b.deptno
AND a.sal > b.salavg;
2.查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名、职位，不包括SCOTT和BLANK本人
条件：select job,mgr from emp where ename='SCOTT' or ename = 'BLAKE';
select e.ename,e.job
from emp e, (select job,mgr from emp where ename='SCOTT' or ename = 'BLAKE') b
where e.job=b.job and e.mgr = b.mgr
and e.ename not in('SCOTT','BLAKE');
3.查询不是经理的员工姓名
SELECT DISTINCT mgr FROM emp;
select ename
from emp
where empno not in (select distinct mgr from emp where mgr is not null);
练习五
1.查询入职日期最早的前5名员工姓名、入职日期。
select rownum, ename,hiredate from emp where rownum <=5;
2.查询工作在CHIACAGO并且入职日期最早的前两名员工姓名，入职日期
select rownum,e.ename,hiredate
from emp e join dept d on e.DEPTNO = d.DEPTNO
where d.loc ='CHICAGO' and rownum<3;
练习六
1.按照每页显示5条记录，分别查询第1页，第2 页，第3页信息，
要求显示员工姓名、入职日期 、部门名称。
select b.*
from (SELECT rownum rn,e.ENAME ,e.hiredate,d.DNAME
FROM EMP e ,DEPT d
WHERE e.DEPTNO = d.DEPTNO
)b
WHERE rn <=1*5 AND rn >(1-1)*5;
select b.*
from (SELECT rownum rn,e.ENAME ,e.hiredate,d.DNAME
FROM EMP e ,DEPT d
WHERE e.DEPTNO = d.DEPTNO
)b
WHERE rn <=2*5 AND rn >(2-1)*5;
select b.*
from (SELECT rownum rn,e.ENAME ,e.hiredate,d.DNAME
FROM EMP e ,DEPT d
WHERE e.DEPTNO = d.DEPTNO
)b
WHERE rn <=3*5 AND rn >(3-1)*5;
课后作业
1.查询工资高于编号为7782的员工工资，并且和7369号员工
从事相同工作的员工的编号、姓名及工资
SELECT EMPNO ,ENAME ,SAL
FROM EMP
WHERE (sal > (SELECT SAL from EMP WHERE EMPNO = 7782))
AND (job = (SELECT job FROM EMP WHERE EMPNO = 7369));
--2.查询工资最高的员工姓名和工资
SELECT ENAME , sal from EMP WHERE SAL =(SELECT max(sal) FROM EMP );
3. 查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资
select e.DEPTNO,d.dname,min(sal)
from emp e join dept d on e.DEPTNO = d.DEPTNO
group by e.DEPTNO,d.DNAME
HAVING (min(sal)>(select min(sal) from emp where deptno = 10));
4.查询员工工资为其部门最低工资的员工的编号和姓名及工资。
SELECT EMPNO,ENAME,SAL
FROM EMP E1
WHERE SAL=(SELECT MIN(SAL) FROM EMP E2 WHERE DEPTNO=E1.DEPTNO);
SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE (DEPTNO,SAL) IN (SELECT DEPTNO,MIN(SAL) FROM EMP GROUP BY DEPTNO);
5.显示经理是KING的员工姓名，工资。
SELECT ENAME,SAL FROM EMP
WHERE MGR=(SELECT EMPNO FROM EMP WHERE ENAME='KING');
6.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。
SELECT ENAME,SAL,HIREDATE FROM EMP
WHERE HIREDATE>(SELECT HIREDATE FROM EMP WHERE ENAME='SMITH');
7.使用子查询的方式查询哪些职员在NEW YORK工作。
SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO WHERE LOC='NEW YORK';
SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');
8.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。
SELECT ENAME,HIREDATE FROM EMP
WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='SMITH') AND ENAME<>'SMITH';
9.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。
SELECT EMPNO,ENAME
FROM EMP
WHERE SAL>(SELECT AVG(SAL) FROM EMP)
10.写一个查询显示其上级领导是King的员工姓名、工资。
SELECT ENAME,SAL FROM EMP
WHERE MGR=(SELECT EMPNO FROM EMP WHERE ENAME='KING');
11.显示所有工作在RESEARCH部门的员工姓名，职位。
SELECT ENAME,JOB
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='RESEARCH');
12.查询每个部门的部门编号、平均工资，要求部门的平均工资高于部门20的平均工资。
SELECT DEPTNO,AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING(AVG(SAL)>(
SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO=20)
);
13.查询大于自己部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度。
SELECT ENAME,SAL,ASAL,SAL-ASAL FROM EMP INNER JOIN
(SELECT DEPTNO,AVG(SAL) ASAL FROM EMP GROUP BY DEPTNO) AA ON EMP.DEPTNO=AA.DEPTNO WHERE SAL>ASAL;
SELECT ENAME SAL,(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=E2.DEPTNO),SAL-(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=E2.DEPTNO)
FROM EMP E2 WHERE sal>(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=E2.DEPTNO);
14.
列出至少有一个雇员的所有部门
SELECT * FROM DEPT WHERE DEPTNO IN(SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING(COUNT(*)>0));
SELECT * FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO from EMP GROUP BY DEPTNO HAVING count(DEPTNO)>0);
15.
列出薪金比"SMITH"多的所有雇员
SELECT * FROM EMP e1 WHERE E1.SAL >(SELECT e2.sal from EMP e2 WHERE e2.ename='SMITH');
SELECT * from emp where sal>(select sal from emp where Ename='SMITH');
16.
列出入职日期早于其直接上级的所有雇员
SELECT * FROM EMP e1 WHERE HIREDATE <(SELECT e2.hiredate FROM emp e2 WHERE E1.MGR = E2.Empno);
SELECT * FROM EMP E1 WHERE HIREDATE <(SELECT HIREDATE FROM EMP WHERE EMPNO=E1.MGR);
17.
找员工姓名和直接上级的名字
SELECT e1.ename ,e2.ename FROM emp e1 ,emp e2 WHERE E1.MGR = E2.EMPNO(+);
--SELECT E1.ENAME,E2.ENAME FROM EMP E1 LEFT JOIN EMP E2 ON E1.MGR=E2.EMPNO;
SELECT ENAME,(SELECT ENAME FROM EMP WHERE EMPNO=E1.MGR) FROM EMP E1;
18.
显示部门名称和人数
SELECT DNAME,(SELECT COUNT(*) FROM EMP WHERE DEPTNO=D1.DEPTNO) FROM DEPT D1;
SELECT DNAME,COUNT(EMPNO) FROM EMP RIGHT JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO GROUP BY DNAME;
19.
显示每个部门的最高工资的员工
SELECT * FROM EMP WHERE (DEPTNO,SAL) IN (SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO);
SELECT * from emp e1 where sal=(select max(sal) from emp e2 where e2.deptno=e1.deptno);
20.
显示出和员工号7369部门相同的员工姓名，工资
21.
显示出和姓名中包含"W"的员工相同部门的员工姓名
22.
显示出工资大于平均工资的员工姓名，工资
23.
显示出工资大于本部门平均工资的员工姓名，工资
24.
显示每位经理管理员工的最低工资，及最低工资者的姓名
select ename,sal from emp where (mgr,sal) in(select mgr,min(sal) from emp group by mgr);
25.
显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间
SELECT ENAME,HIREDATE FROM EMP
WHERE HIREDATE>(SELECT hiredate FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP))
26.
显示出平均工资最高的的部门平均工资及部门名称
--SELECT * FROM (SELECT DNAME,AVG(SAL) FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO GROUP BY DNAME ORDER BY AVG(SAL) DESC) WHERE ROWNUM <=1
