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 =7839
UNION
SELECT 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 ==0
FROM emp e, dept d
WHERE d.deptno =e.deptno(+)
GROUP BY d.deptno
*/

--3
/*SELECT dname ,COUNT (empno)
FROM emp e,dept d
WHERE e.deptno= d.deptno 
GROUP BY d.dname 
UNION
SELECT dname ,0
FROM dept d
WHERE deptno NOT IN(SELECT deptno FROM emp WHERE deptno IS NOT NULL)
*/
--4
/*SELECT deptno,ename
FROM emp
WHERE deptno =10

UNION ALL
SELECT deptno,ename
FROM emp
WHERE deptno=20*/

--5
/*SELECT deptno ,dname ,NULL ,NULL FROM dept
UNION
SELECT deptno ,NULL,ename ,hiredate FROM emp*/


--相交intersect
--相减minus

--homework__chapter8
--1
/*SELECT deptno
FROM dept
MINUS
SELECT deptno
FROM emp
WHERE job='SALESMAN'*/

--2
/*SELECT NULL,deptno ,ename
FROM emp
UNION
SELECT  dname,deptno,NULL
FROM dept
ORDER BY deptno,ename DESC*/

--3
/*SELECT empno ,ename ,job
FROM emp
WHERE job='SALESMAN'
UNION ALL
SELECT empno ,ename ,job
FROM emp
WHERE deptno =10*/

--4
/*SELECT empno, ename,dname
FROM emp e,dept d
WHERE d.deptno =10
AND d.deptno =e.deptno

UNION ALL
SELECT empno, ename,dname
FROM emp e,dept d
WHERE d.deptno =20
AND d.deptno =e.deptno*/


-----------------------------------------------
--chapter9
--EXISTS NOT EXISTS
/*
SELECT e.*, (SELECT AVG(sal) FROM emp WHERE deptno =e.deptno)--引用了父查询的一个列
FROM emp e
WHERE 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 e
WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno =e.deptno)
--相关子查询 子查询无法独立运行
*/
/*SELECT *
FROM emp e
WHERE sal>(SELECT AVG(sal )FROM emp WHERE job=e.job)*/

--2
/*SELECT empno, ename ,sal   --缺陷：无法显示最低 平均工资 效率低
FROM emp e
WHERE sal=
(
SELECT MIN(sal)
FROM emp
WHERE 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 emp
WHERE empno IN (SELECT nvl(mgr,0) FROM emp)*/
--3
/*SELECT ename 
FROM emp
WHERE empno  NOT IN (SELECT nvl(mgr,0) FROM emp )*/

--4
/*(
SELECT sal ,deptno,ROWNUM rn
FROM (SELECT sal , deptno FROM emp GROUP BY deptno,sal)
ORDER BY sal
)
WHERE rn<2
*/


/*SELECT empno ,ename ,sal ,deptno
FROM emp e
WHERE (SELECT COUNT (empno) FROM emp WHERE deptno =e.deptno AND sal<e.sal)<=1*/

/*
SELECT ename ,job,sal ,deptno 
FROM emp e
WHERE EXISTS (SELECT '1' FROM emp WHERE mgr=e.empno)*/
--exce3
--1
/*SELECT dname 
FROM dept d
WHERE EXISTS(SELECT '1' FROM emp WHERE d.deptno =deptno)*/


--2
/*SELECT dname 
FROM dept d
WHERE 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 ,deptno
FROM emp e
WHERE 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 ,sal
FROM emp e
WHERE (SELECT COUNT (empno) FROM emp WHERE deptno =e.deptno 
      AND sal>e.sal) <=0*/
--4
/*SELECT ename ,deptno ,sal
FROM emp e
WHERE (SELECT COUNT (empno) FROM emp WHERE deptno =e.deptno 
      AND sal>e.sal) <=1*/


--chapter 10 
-----------------------------------
/*
SELECT [LEVEL] ,colnum ,expr
FROM TABLE
[WHERE condition]
[START WITH condition]
[CONNECT BY PRIOR condition]*/


/*SELECT LEVEL,empno,ename,mgr
FROM emp
START WITH empno=7839
CONNECT BY PRIOR empno =mgr*/

/*SELECT LEVEL,empno,ename,mgr
FROM emp
START WITH empno=7934--7839
CONNECT BY PRIOR  mgr=empno*/

SELECT ename ||'reports to '|| PRIOR ename "Walk Top Down"
FROM emp 
START WITH ename ='king'
CONNECT BY PRIOR empno =mgr