6 子查询&&连接查询

select * from emp where deptno=(select deptno from emp where ename='SMITH') and ename<>'SMITH';
select * from emp where job in(select distinct job from emp where deptno=10);
select * from emp where sal>all(select sal from emp where deptno=30);
select * from emp where sal=some(select sal from emp where deptno=30);
select * from emp where sal>any(select sal from emp where deptno=30);
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');

select t2.* from (select t1.*,rownum rn from (select * from emp) t1 where rownum<=6) t2 where rn>=4;/***分页查询***/
select e.ename,b.ename from emp e,emp b where e.mgr=b.empno;/***自连接***/