(06)oracle新人笔记------高级子查询（重点）

嵌套子查询

SELECT empno,ename,sal,e.deptno

FROM emp e,(SELECT deptno,AVG(sal) avgsal

              FROM emp

              GROUP BY deptno) d

WHERE e.deptno=d.deptno

AND e.sal>d.avgsal;

 

 

 

 

相关子查询

SELECT empno,ename,sal,deptno

FROM emp outer

WHERE sal>(SELECT AVG(sal)

           FROM emp

           WHERE deptno=outer.deptno);

 

SELECT dname,(SELECT count(empno)

              FROM emp

              WHERE deptno=d.deptno)

FROM dept d;

 

SELECT *

FROM emp e

WHERE 0<(SELECT count(empno)

         FROM emp

         WHERE mgr=e.empno);

 

EXISTS(约等于IN)

SELECT ename,job,sal,deptno

FROM emp e

WHERE EXISTS(SELECT 'QWERQWE'    //随便搜什么，只是占个位子

                FROM emp

                WHERE mgr=e.empno);

 

SELECT ename,job,sal,deptno

FROM emp e

WHERE NOT EXISTS(SELECT 'QWERQWE' 

             FROM emp

             WHERE mgr=e.empno);

 

 

 

 

练习

 

1. CREATETABLE emp_jobhistory(

id NUMBER,

empno NUMBER,

job VARCHAR(9),

begindate DATE,

sal NUMBER(7,2))

 

 

 

 

2. INSERTINTO emp_jobhistory

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

3. INSERTINTO emp_jobhistory

VALUES(2,7839,'SALSMAN','17-2月-82',1800);

4. INSERTINTO emp_jobhistory

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

5. INSERTINTO emp_jobhistory

VALUES(4,7839,'SALSMAN','17-2月-85',1800);

6. INSERTINTO emp_jobhistory

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