oracle学习记录之六

select * from emp where deptno=(select deptno from emp where ename='SMITH');

 select * from emp where job in (select job from emp where deptno=10);

select ename, sal, deptno from emp where sal>(select max(sal) from emp where deptno=30);

 select ename, sal, deptno from emp where sal> any (select sal from emp where deptno=30);

select * from emp where deptno=(select deptno from emp where ename='SMITH') and job=(select job
 from emp where ename='SMITH');

select * from emp where (deptno, job) = (select deptno, job from emp where ename='SMITH');

select a1.ename, a1.sal, a1.deptno from emp a1, (select deptno, avg(sal)  ss from emp group by
 deptno) a2 where a1.deptno=a2.deptno and a1.sal>a2.ss;

select a1.*, rownum rn from (select * from emp) a1;

select a1.*, rownum rn from (select * from emp) a1 where rownum<10;   --where子句中必须用rownum不能用rn

select * from (select a1.*, rownum rn from (select * from emp) a1 where rownum<10) where rn>5;

select * from (select a1.*, rownum rn from (select ename, sal, deptno from emp) a1 where rownum<10) where rn>6;

create table test(ename, sal, deptno) as select ename, sal, deptno from emp;

SQL> select * from emp where sal>2500
  2  union
  3  select * from emp where job='MANAGER';

SQL> select * from emp where sal>2500
  2  intersect
  3  select * from emp where job='MANAGER';