一些子查询

1. select empno, ename from emp where mgr in
2. (select empno from emp where job='MANAGER');
4. select * from dept where deptno not in (select distinct deptno from emp);
5. select * from dept where deptno not in (select deptno from emp);
7. select empno, ename, sal from emp where mgr=
8. (select empno from emp where ename='SCOTT');
10. select * from emp where sal > 1.4*
11. (select avg(sal) from emp);

1. insert into dept(deptno, dname, loc) select 50, 'TRAINING', 'PEKING' from dual;
3. update emp set sal=sal*1.2 where exists (select 1 from dept where deptno=emp.deptno and loc='DALLAS');

在写一条孔乙己式样的sql：

update emp

set

sal

=

sal

*

1.2

where

exists

(

select

avg(sal)

from

dept

);

简单的层次查询

–-查询7788号雇员的下属和下属的下属……

select level, t.* from emp t start with empno=7788 connect by prior empno=mgr;

–-查询7788号雇员的的上司和上司的上司……

select level, t.* from emp t start with empno=7788 connect by empno=prior mgr;