子查询（嵌套子查询）

where子句：子查询返回结果为单行单列，单行多列，多行单列(往往使用in，any，all)操作符
having子句：子查询返回结果单行单列数据，同时为了使用统计函数操作
from子句：子查询返回结果为多行多列，可以按照一张数据表（临时表）的形式操作
select子句：了解即可，可以通过别的子句形式完成。
rownum子句：只能执行<=运算，不能执行>或者between and 区间的运算，与order by连用时，要先使用order by排序（建立个临时表）。
范例（where）：

---

1.查询基本工资比ALLEN低的全部雇员信息
a.找到ALLEN的工资，返回一个单行单列的数据
b. 在where子句中里面使用子查询

select * from emp 
where sal>(select sal from emp where ename='ALLEN')

2.查询与雇员7566从事统一工作且领导相同的的全部雇员信息
a.找到7566的工作和领导，返回单行多列的数据
b.在where子句中里面使用子查询

select * from emp where (job,mgr)=(select job,mgr from emp where empno=7566)
and empno<>7566

3.查询工资与每个部门中最低工资相同的全部雇员信息
a.查出每个部门最低工资是多少，返回多行单列的数据。
b.用where子句（in）
c.如果在in中子查询返回值有NULL，那么不会影响，但如果是not in 中子查询有null，那么不会返回任何值，用nvl函数（nvl（mgr，100））

select * from emp where sal in 
(select  min(sal) from emp GROUP BY deptno)

4.空数据判断
在sql中提供了一个exists结构用于判断子查询是否有数据返回，如果子查询中有数据返回，则exists结构返回ture，反之返回false

---

范例（having）

having一定是结合group by子句一起使用的，其主要目的是分组后的信息再次过滤，而与where子句不同的是，having是在分组后，可以使用统计函数。

1.查询每个部门平均工资最高的的部门名称及平均工资（最高的平均工资，肯定是统计函数的嵌套）
a.先求出部门平均工资最高的那个平均工资，返回的是单行单列的数据

select  dname,avg(sal) from emp join dept on DEPT.DEPTNO=EMP.DEPTNO
GROUP BY dname having avg(sal) =(select max(avg(sal)) from emp GROUP BY deptno )

---

范例（from）

from子句的主要功能是确定数据来源，那么来源都属于数据表，只要是在from子句中出现的内容一般都是多行多列的子查询返回。

1.查询薪金比ALLEN或clark多的所有员工的编号，姓名，基本工资，部门名称，领导姓名，部门人数。
a.找出所需要的表
emp：员工的编号，姓名，基本工资
dept：部门名称
emp：领导姓名
emp：统计部门人数

select emp.empno,EMP.ename,EMP.SAL,DEPT.dname,s.ENAME,temp.tt
from emp,dept,emp s,(select deptno ,count(*) as tt from emp GROUP BY deptno) temp
where emp.sal>any
(select sal from emp where ename in('ALLEN','CLARK'))
and DEPT.DEPTNO=EMP.DEPTNO
and EMP.mgr=s.EMPNO
and EMP.deptno=temp.deptno

---

范例（select）
1.查询每个部门的编号，名称，位置，部门人数，平均工资

 select dept.deptno,loc,DNAME,
(select count(*)
from emp where EMP.DEPTNO=DEPT.DEPTNO group by deptno
  ) count,
(   select avg(sal)from emp where EMP.DEPTNO=DEPT.DEPTNO group by deptno
) avg
from DEPT

---

范例（rownum）
1.查询工作在CHICAGO并且入职日期最早的前2,名员工姓名，入职日期。

select b.hiredate,rownum from (select * from emp ORDER BY hiredate ) b
join dept on DEPT.DEPTNO=b.deptno where ROWNUM<3 and loc= 'CHICAGO'

---

分页查询

• 目标页数*每页记录数
• （目标页数-1）*每页记录数
  1.每页6条记录，查询第45页高工最高的员工姓名，工资，入职日期，所在部门.

select r,ename,hiredate,sal,dname
from (select rownum r,deptno,ename,hiredate,sal   from (select * from emp ORDER BY sal DESC) 
where rownum<=45*6 ) s,dept where r>(45-1)*6 and DEPT.DEPTNO=s.deptno

---

练习
1.查询工资高于编号7782的员工工资，并且和7396号员工工作相同的员工编号，姓名，工资。

select sal,ename,empno
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 emp.deptno,dname,min(sal)
from emp,DEPT
where EMP.DEPTNO=dept.DEPTNO 
GROUP BY emp.deptno,dname having min(sal)<( 
select min(sal) from emp where deptno=10)

4.查询员工工资为其部门最低工资的员工编号和姓名及工资。

select ename,sal,empno from emp where sal in(
select 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 ename from emp where deptno=(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 ename,empno from emp where sal>
(select avg(sal) from emp)

10.查询显示上级领导是KING的员工，姓名
同第五题
11.显示所有工作在RESEARCH部门的员工姓名，职位。

select ename,job from emp join dept on dept.DEPTNO=EMP.DEPTNO
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,avg from emp,
(select deptno ,avg(sal) as avg  from emp GROUP BY deptno) temp
where EMP.DEPTNO=TEMP.DEPTNO
and sal>avg

14.列出至少有一个雇员的所有部门。

select deptno from emp
GROUP BY DEPTNO
having count(*)>=1

15.列出薪金比'SMITH'多的所有雇员。

select ename from emp where sal>(select sal from emp 
where ename='SMITH')

16.列出入职日期早于其直接上级的所有雇员。

select EMP.ename 
from emp join emp temp 
on EMP.mgr=TEMP.empno
where  EMP.HIREDATE<TEMP.HIREDATE

17.找员工姓名和直接上级姓名。

select EMP.ename,TEMP.ename 
from emp join emp temp
on EMP.MGR=TEMP.EMPNO

18.显示每个部门的最高工资的员工。

select ename from emp where sal 
in(select max(sal) from emp GROUP BY DEPTNO )

19.显示工资最高的员工参加工作时间晚的员工姓名，参加工作时间。

select ename,hiredate from emp 
where hiredate >(select hiredate from emp where sal=(select max(sal)from emp ))

20.显示每位经理管理员工的最低工资，及最低工资者的姓名。

a.SELECT ename,sal from emp 
where (sal,mgr) in (SELECT min(SAL),mgr from emp  GROUP BY mgr)
b.Select e.ename,e.sal From emp e,(select min(sal) mins
 from emp group by mgr having mgr is not null) a where e.sal=a.mins;

21.显示平均工资最高部门的平均部门名称及平均工资。

a.
select  DEPT.dname ,avg(sal)
from dept join emp on DEPT.DEPTNO=EMP.DEPTNO GROUP BY DEPT.dname  having avg(sal)=(
select  max(avg(sal)) from emp GROUP BY DEPTNO)


b.
select dept.dname,TEMPTAB2.MAXSAL
from
(select max(tempTab.avgsal) maxsal
from 
(select DEPTNO,avg(sal) avgsal
from EMP
GROUP BY DEPTNO) tempTab) tempTab2
join 
(select DEPTNO,avg(sal) avgsal
from EMP
GROUP BY DEPTNO) tempTab3 on tempTab2.MAXSAL = tempTab3.AVGSAL
join dept on dept.deptno = tempTab3.deptno