//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