2015年8月5日--联合查询 ，层次查询

//2015年8月6日13:01:19

--2015年8月5日12:34:23--------------------------chapter8--联合      union  条件里不能order by 必须在最后--完全联合 union all--相交intersect--相减minus/*SELECT sal ,job FROM emp WHERE empno =7839UNIONSELECT 1 ,'a' FROM emp */--下午讲课--2015年8月5日14:01:26--集合运算--1.????????????λ?? emp_jobhistory??/*CREATE TABLE emp_jobhistory(    id NUMBER,--?????    empno NUMBER,--??????    job VARCHAR2(9),--??λ    begindate DATE,--???????    sal Number(7,2)--????λ?????)*/--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);*/--exce1--2/*SELECT d.deptno ,COUNT (empno)            --meirenshu ==0FROM emp e, dept dWHERE d.deptno =e.deptno(+)GROUP BY d.deptno*/--3/*SELECT dname ,COUNT (empno)FROM emp e,dept dWHERE e.deptno= d.deptno GROUP BY d.dname UNIONSELECT dname ,0FROM dept dWHERE deptno NOT IN(SELECT deptno FROM emp WHERE deptno IS NOT NULL)*/--4/*SELECT deptno,enameFROM empWHERE deptno =10UNION ALLSELECT deptno,enameFROM empWHERE deptno=20*/--5/*SELECT deptno ,dname ,NULL ,NULL FROM deptUNIONSELECT deptno ,NULL,ename ,hiredate FROM emp*/--相交intersect--相减minus--homework__chapter8--1/*SELECT deptnoFROM deptMINUSSELECT deptnoFROM empWHERE job='SALESMAN'*/--2/*SELECT NULL,deptno ,enameFROM empUNIONSELECT  dname,deptno,NULLFROM deptORDER BY deptno,ename DESC*/--3/*SELECT empno ,ename ,jobFROM empWHERE job='SALESMAN'UNION ALLSELECT empno ,ename ,jobFROM empWHERE deptno =10*/--4/*SELECT empno, ename,dnameFROM emp e,dept dWHERE d.deptno =10AND d.deptno =e.deptnoUNION ALLSELECT empno, ename,dnameFROM emp e,dept dWHERE d.deptno =20AND d.deptno =e.deptno*/-------------------------------------------------chapter9--EXISTS NOT EXISTS/*SELECT e.*, (SELECT AVG(sal) FROM emp WHERE deptno =e.deptno)--引用了父查询的一个列FROM emp eWHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno =e.deptno)--相关子查询 子查询无法独立运行*/--exce1--1/*SELECT e.*, (SELECT AVG(sal) FROM emp WHERE deptno =e.deptno)FROM emp eWHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno =e.deptno)--相关子查询 子查询无法独立运行*//*SELECT *FROM emp eWHERE sal>(SELECT AVG(sal )FROM emp WHERE job=e.job)*/--2/*SELECT empno, ename ,sal   --缺陷：无法显示最低 平均工资 效率低FROM emp eWHERE sal=(SELECT MIN(sal)FROM empWHERE deptno =e.deptno)*/--exce2--1/*SELECT empno ,ename,(SELECT dname FROM dept WHERE deptno =e.deptno)FROM emp e*/--SELECT * FROM emp e WHERE sal=(SELECT MIN(sal) FROM emp WHERE deptno =e.deptno)--2/*SELECT ename FROM empWHERE empno IN (SELECT nvl(mgr,0) FROM emp)*/--3/*SELECT ename FROM empWHERE empno  NOT IN (SELECT nvl(mgr,0) FROM emp )*/--4/*(SELECT sal ,deptno,ROWNUM rnFROM (SELECT sal , deptno FROM emp GROUP BY deptno,sal)ORDER BY sal)WHERE rn<2*//*SELECT empno ,ename ,sal ,deptnoFROM emp eWHERE (SELECT COUNT (empno) FROM emp WHERE deptno =e.deptno AND sal<e.sal)<=1*//*SELECT ename ,job,sal ,deptno FROM emp eWHERE EXISTS (SELECT '1' FROM emp WHERE mgr=e.empno)*/--exce3--1/*SELECT dname FROM dept dWHERE EXISTS(SELECT '1' FROM emp WHERE d.deptno =deptno)*/--2/*SELECT dname FROM dept dWHERE NOT EXISTS(SELECT '1' FROM emp WHERE d.deptno =deptno)*/--SELECT empno, ename, job, mgr--FROM emp--START WITH empno= 7876--CONNECT BY PRIOR mgr = empno;--homework && chapter9--1/*SELECT ename ,deptnoFROM emp eWHERE sal>(SELECT avg(sal) FROM emp WHERE e.deptno =deptno)*/--2/*SELECT ename ,(SELECT ename FROM emp WHERE empno =e.mgr)FROM emp e*/--3/*SELECT ename ,deptno ,salFROM emp eWHERE (SELECT COUNT (empno) FROM emp WHERE deptno =e.deptno       AND sal>e.sal) <=0*/--4/*SELECT ename ,deptno ,salFROM emp eWHERE (SELECT COUNT (empno) FROM emp WHERE deptno =e.deptno       AND sal>e.sal) <=1*/--chapter 10 -----------------------------------/*SELECT [LEVEL] ,colnum ,exprFROM TABLE[WHERE condition][START WITH condition][CONNECT BY PRIOR condition]*//*SELECT LEVEL,empno,ename,mgrFROM empSTART WITH empno=7839CONNECT BY PRIOR empno =mgr*//*SELECT LEVEL,empno,ename,mgrFROM empSTART WITH empno=7934--7839CONNECT BY PRIOR  mgr=empno*/SELECT ename ||'reports to '|| PRIOR ename "Walk Top Down"FROM emp START WITH ename ='king'CONNECT BY PRIOR empno =mgr