/*

集合运算：查询结果可以作为一个集合。

查询结果集与查询结果集之间的运算；

1、联合(UNION)运算

返回由任一查询结果集包含的行，并且去除重复行,并且按照查询结果集的第一列升序排序。

使用原则：

多个被联合的查询语句所选择的列数和列的数据类型必须一致，列的名字不必相同。

如果多个查询结果都有NULL值，整个结果中只包含一个NULL值。

每个查询不能包含自己的Order by子句，只能在联合之后使用Order by子句。

union all的使用：

返回由任一查询结果集包含的行，并且包含重复行，默认情况下不对结果集进行排序 。

与union的区别：

union all 不排序、不剔掉重复；

2、交集运算- INTERSECT 返回多个查询结果集的公有行（多个结果集中都包含的行）。

使用原则

多个查询语句所选择的列数和列的数据类型必须一致，列的名字不必相同。

相交运算不忽略空值。

3、 相减运算 - MINUS

返回第一个查询结果集中存在 而第二个查询结果集中不存在的行记录。

（从第一个查询结果集中剔除也在第二个查询结果集中出现的记录，第一个结果集剩余的记录就是相减运算的结果）

使用原则

多个查询语句所选择的列数和列的数据类型必须一致，列的名字不必相同。

相减运算不忽略空值。

使用集合相减运算可以进行分页查询；

==============================================

4、相关子查询

就是在嵌套子查询基础上，子查询中的where条件使用到了父查询表中的字段值；

父查询和子查询之间通过子查询的where有条件过滤相关关系；

5、exists 和 not exists的相关子查询的用法

exists和not exists的相关子查询返回的是一个判定结果，判定结果 true或false；

如果相关子查询查询到了结果，则返回true；

如果相关子查询没有查询到结果，则返回false；

使用exists关键字，

如果相关子查询返回true，则主查询中对应的记录作为结果集的内容；

使用not exists关键字，

如果相关子查询返回false，则主查询中对应的记录作为结果集的内容；

exists和not exists可以认为类似于java 的if语句，相关子查询相当于是if语句的判定表达式；

6、层次查询（树状递归查询）

按照字段之间的树状层次关系进行查询；

语法：

SELECT [LEVEL], column, expr...

FROM table

[WHERE condition(s)]

[START WITH condition(s)]

[CONNECT BY PRIOR condition(s)] ;

解析：

LEVEL：节点的层次，伪列，由查询的起点开始算起为1，依次类推。

FROM table：指定表、视图或包含列的快照，你只能从单独的一个表中选择。

WHERE： 限制返回的行。

Condition：是一个比较式。

START WITH：指定层次的根行 (起点)。这个子句对于一个正确的分级查询是必须的。

CONNECT BY PRIOR：指定存在父行与子行的关系列。对于分级查询该子句是必须的。

例如：CONNECT BY PRIOR mgr = empno

表示 当前记录的mgr是下一个层次的记录的empno；

当前记录的mgr字段的值是哪个员工的empno值；

例如：CONNECT BY PRIOR empno = mgr

表示我的empno的值是谁的mgr值；

注意： = 左边的属性是父记录的属性；

= 右边的属性是子记录的属性；

*/

-----------------------------------PPT8集合运算-----------------------------------------------------------------

----测试数据准备脚本

CREATE TABLE emp_jobhistory(

id NUMBER,--流水号

empno NUMBER,--员工编号

job VARCHAR2(9),--岗位

begindate DATE,--开始日期

sal Number(7,2)--在该岗位时工资

)

---插入如下数据：

INSERT INTO emp_jobhistory VALUES(1,7839,'TRAINEE','17-11月-81',500);

INSERT INTO emp_jobhistory VALUES(2,7839,'SALESMAN','17-2月-82',1800);

INSERT INTO emp_jobhistory VALUES(3,7839,'CLERK','17-2月-83',2000);

INSERT INTO emp_jobhistory VALUES(4,7839,'SALESMAN','17-2月-85',1800);

INSERT INTO emp_jobhistory VALUES(5,7839, 'MANAGER','17-2月-87',3000);

--union运算

select * from emp_jobhistory;

--查询编号为7839的员工当前工资、岗位和历史工资、岗位

--当前岗位和工资

select e.job,e.sal from emp e where e.empno=7839;

--历史岗位和工资

select ej.job,ej.sal from emp_jobhistory ej where ej.empno=7839;

--union并集运算

select e.job,e.sal from emp e where e.empno=7839

union

select ej.job,ej.sal from emp_jobhistory ej where ej.empno=7839;

---查询哪些员工做过岗位调动

select e.empno from emp e

intersect

select ej.empno from emp_jobhistory ej;

---查询哪些员工没有做过岗位调动

select e.empno from emp e

minus

select ej.empno from emp_jobhistory ej;

---分页查询

SELECT ROWNUM RN,E.* FROM EMP E;

SELECT ROWNUM RN,E.* FROM EMP E WHERE E.DEPTNO=30;

---分页显示；每页显示5条记录：要求显示EMP表中的第1-5条记录

SELECT ROWNUM RN,E.* FROM EMP E WHERE ROWNUM<=5;

---分页显示；每页显示5条记录：要求显示EMP表中的第6-10条记录(产生是在有了1-5的基础上才有6-10)

SELECT ROWNUM RN,E.* FROM EMP E WHERE ROWNUM>5 AND ROWNUM<=10;---不正确

---使用子查询进行分页显示：每页显示5条记录：要求显示EMP表中的第6-10条记录

SELECT * FROM (SELECT ROWNUM RN,E.* FROM EMP E) T WHERE T.RN>5 AND T.RN<=10;

--使用集合相减运算进行分页查询

SELECT ROWNUM RN,E.* FROM EMP E WHERE ROWNUM<=10

minus

SELECT ROWNUM RN,E.* FROM EMP E WHERE ROWNUM<=5;

-------------------------------PPT9高级子查询---------------------------------------------------------------------

--相关子查询

--1.查询比自己职位平均工资高的员工姓名、职位，部门名称，职位平均工资

-----嵌套子查询

SELECT E.ENAME,E.JOB,D.DNAME,MM.AVGSAL FROM EMP E

JOIN DEPT D ON E.DEPTNO=D.DEPTNO

JOIN (SELECT E.JOB MMJOB,AVG(NVL(E.SAL,0)) AVGSAL FROM EMP E GROUP BY E.JOB) MM ON E.JOB=MM.MMJOB

WHERE E.SAL>MM.AVGSAL ;

-----相关子查询

SELECT E.ENAME,E.JOB,D.DNAME,E.SAL FROM EMP E

JOIN DEPT D ON E.DEPTNO=D.DEPTNO

WHERE E.SAL>(SELECT AVG(NVL(E1.SAL,0)) AVGSAL FROM EMP E1 WHERE E.JOB=E1.JOB);

----例如；查询至少调过2次岗位的员工编号，姓名，岗位

select e.empno,e.ename,e.job

from emp e

where 2<=(select count(*) from emp_jobhistory ej where ej.empno=e.empno);

-----使用exists 和not exists的相关子查询

--例：查询那些人是经理？

select * from emp e where exists (select * from emp m where m.mgr=e.empno);

--例：查询那些人不是经理？

select * from emp e where not exists (select * from emp m where m.mgr=e.empno);

---------------------------------PPT10层次查询-------------------------------------------------------------------

---树状层次递归查询

select * from emp e;

select e.empno,e.ename,e.job,e.mgr

from emp e

start with e.ename='KING'

connect by prior e.empno=e.mgr;

--从下往上查

select e.empno,e.ename,e.job,e.mgr

from emp e

start with e.empno=7876

connect by prior e.mgr=e.empno;

------------------------------PPT8与PPT9与PPT10练习题与课后作业---------------------------------------------------------------------

---************PPT8练习1******************--

--1.分别使用联合运算及完全联合运算完成，按照时间升序顺序，查询员工7839的工作岗位列表。

--联合运算

select e.hiredate,e.empno,e.job from emp e where e.empno=7839

union

select ej.begindate,ej.empno,ej.job from emp_jobhistory ej where ej.empno=7839;

---完全联合运算

select e.hiredate,e.empno,e.job from emp e where e.empno=7839

union all

select ej.begindate,ej.empno,ej.job from emp_jobhistory ej where ej.empno=7839;

--2.使用多表连接，查询每个部门的部门编号，部门人数，没有人数的部门显示0。

select d.deptno,count(e.empno)

from dept d

full join emp e on d.deptno=e.deptno

group by d.deptno

order by d.deptno;

--3.使用联合运算，查询每个部门的部门编号，部门人数，没有人数的部门显示0。

select e.deptno,count(*)

from emp e

group by e.deptno

union

select d.deptno,0

from dept d

where d.deptno not in(

select distinct nvl(e2.deptno,0)from emp e2)

group by d.deptno;

--4.使用联合运算，查询10号部门及20号部门的员工姓名，部门编号。

select e.ename,e.deptno from emp e where e.deptno=10 union

select e.ename,e.deptno from emp e where e.deptno=20;

--5.使用集合运算，输出PPT中的效果？

select d.deptno "部门",d.loc "工作地点",null "员工姓名",null "入职日期" from dept d

union

select e.deptno,null,e.ename,e.hiredate from emp e;

---************PPT8课后作业******************--

--1.用集合运算，列出不包含job为SALESMAN 的部门的部门号。

select e.deptno,e.job from emp e minus

select e.deptno,e.job from emp e where e.job='SALESMAN';

--2.写一个联合查询，列出下面的信息：???????--->!!!!!!

--EMP表中所有雇员的名字和部门编号,不管他们是否属于任何部门。

--DEPT表中的所有部门编号和部门名称，不管他们是否有员工。

select e.ename,e.deptno,null dname from emp e union

select null,d.deptno,d.dname from dept d;

--3.用集合运算查询出职位为SALESMAN和部门编号为10的人员编号、姓名、职位，不排除重复结果。

select e.empno,e.ename,e.job from emp e where e.job='SALESMAN'

union all

select e.empno,e.ename,e.job from emp e where e.deptno=10;

--4.用集合查询出部门为10和20的所有人员编号、姓名、所在部门名称。?????

select e.empno,e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and e.deptno=10

union all

select e.empno,e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and e.deptno=20;

---************ppt9练习1******************--

--如下练习，使用相关子查询完成

--1.查询比所在职位平均工资高的员工姓名，职位。

SELECT E.ENAME,E.JOB FROM EMP E

WHERE E.SAL>(SELECT AVG(NVL(E1.SAL,0)) AVGSAL FROM EMP E1 WHERE E.JOB=E1.JOB);

--2.查询工资为其部门最低工资的员工编号，姓名，工资。

select e.empno,e.ename,e.sal

from emp e

where e.sal=(select min(nvl(e1.sal,0)) from emp e1 where nvl(e1.deptno,0)=nvl(e.deptno,0));

---************ppt9练习2******************--

--如下练习，用相关子查询完成

--1.查询所有雇员编号，名字和部门名字。!!!!!!!!!!!

select e.empno,e.ename,(select d.dname from dept d where d.deptno=e.deptno) from emp e;

--2.查询哪些员工是经理？

select * from emp e where exists(select * from emp m where m.mgr=e.empno);

--3.查询哪些员工不是经理？

select * from emp e where not exists(select * from emp m where m.mgr=e.empno);

--4.查询每个部门工资最低的两个员工编号，姓名，工资。

--注意算法思想（需要完善）

select e.empno,e.ename,e.sal,e.deptno

from emp e

where (select count(empno)from emp e2 where

e2.deptno=e.deptno and e2.sal<e.sal)<=1;

-----(完善版)

select e2.ename,e2.sal,e2.deptno

from (select rownum rn,e22.* from(select e.sal,e.deptno,e.ename from emp e order by e.sal) e22) e2

where 1>=(select count(*)from (select rownum rn,e11.* from(select e.sal,e.deptno,e.ename from emp e order by e.sal) e11)e1

where e1.deptno=e2.deptno and e2.rn>e1.rn);

---************ppt9练习3******************--

--如下练习，用exists或not exists完成

--1.列出至少有一个雇员的所有部门名称。

select d.dname

from dept d

where exists(select * from emp e where e.deptno=d.deptno);

--2.列出一个雇员都没有的所有部门名称。

select d.dname

from dept d

where not exists(select * from emp e where e.deptno=d.deptno);

---************ppt9课后作业******************--

--如下练习，使用相关子查询完成。

--1.查询薪水多于他所在部门平均薪水的雇员名字，部门号。

select e.ename,e.deptno

from emp e

where e.sal>(select avg(nvl(ee.sal,0))from emp ee where ee.deptno=e.deptno);

--2.查询员工姓名和直接上级的名字。!!!!!!!!

select e.ename,(select m.ename from emp m where e.mgr=m.empno)

from emp e;

--3.查询每个部门工资最高的员工姓名，工资。

select e.ename,e.sal

from emp e

where e.sal=(select max(nvl(ee.sal,0))from emp ee where nvl(ee.deptno,0)=nvl(e.deptno,0));

--4.查询每个部门工资前两名高的员工姓名，工资(扩展作业)

select e.empno,e.ename,e.sal,e.deptno

from emp e

where (select count(empno)from emp e2 where

e2.deptno=e.deptno and e2.sal>e.sal)<=1;

---************PPT10课后作业******************--

--1.产生一个报告显示 BLAKE的所有下级（包括直接和间接下级）雇员的名字、薪水和部门号。

select e.ename,e.sal,e.deptno

from emp e

start with e.ename='BLAKE'

connect by prior e.empno=e.mgr;

--2.创建一个报告显示对于雇员 SMITH 经理的层次，包括级别和姓名，首先显示他的直接经理。?????

select level,e.ename

from emp e

where e.ename<>'SMITH'

start with e.ename='SMITH'

connect by prior e.mgr=e.empno;

--3.创建一个缩进报告显示经理层次，从名字为 KING的雇员开始，显示雇员的名字、经理ID和部门ID。

select e.ename,e.mgr,e.deptno,lpad (ename,length(ename)+(LEVEL-1)*2,'*')

from emp e

start with e.ename='KING'

connect by prior e.empno=e.mgr;

--4.产生一个公司组织图表显示经理层次。从最顶级的人开始，排除所有job为CLERK的人，还要排除FORD和那些对FORD报告的雇员。?????

select e.ename,e.mgr,e.deptno,lpad (ename,length(ename)+(LEVEL-1)*2,'*')

from emp e

where e.job<>'CLERK'

start with e.ename='KING'

connect by prior e.empno=e.mgr and e.ename<>'FORD';

------

select lpad (ename||'_'||e.mgr||'_'||e.deptno,length(ename||'_'||e.mgr||'_'||e.deptno)+(LEVEL*2)-2,'-')

from emp e

where e.job<>'CLERK'

start with e.ename=(select ename from emp where mgr is null)

connect by prior e.empno=e.mgr and e.ename<>'FORD';

---对于此题e.job<>'CLERK'的位置是错误的---

select lpad (ename||'_'||e.mgr||'_'||e.deptno,length(ename||'_'||e.mgr||'_'||e.deptno)+(LEVEL*2)-2,'-')

from emp e

start with e.ename='KING'

connect by prior e.empno=e.mgr and e.ename<>'FORD' and e.job<>'CLERK';