2009 02 21 주말.ORACLE

pairwise comparisons = 
ex)  Select *  from emp where (job,deptno) in (Select distinct job, deptno from emp where sal>=3000)

nonpairwise comparisons =
select * from emp where job in (select job from emp where sal >=3000)
and deptno in ( Select deptno from emp where sal>=3000)





일반적인 서브쿼리는 서브쿼리 실행후에 본쿼리 를 실행하는대

correlated 서브쿼리는 한 row 당 실행된다.



with
g as ( select deptno,avg(sal) avgsal from emp group by deptno)
select ename ,sal from emp join g
using (deptno)
# with 절 사용



  column org_chart format A12;
        SELECT lpad(ename,length(ename)+(level*2)-2,'_') as org_chart
            from emp
            START WITH ename ='KING'
            CONNECT BY PRIOR empno = mgr ;
#트리구조 oracle /scott 계정으로 질의 해보시오 .


         SELECT lpad(name,length(name)*2+(level*2)-2,'_') as org_chart
            from mall
            START WITH name ='전체'
            CONNECT BY PRIOR id= upid
//

         SELECT lpad(name,length(name)*2+(level*2)-2,'_') as org_chart
            from mall
            START WITH name ='전체'
            CONNECT BY PRIOR id= upid 

and name<>'식료품'

정규 표현식 공부는 필요하다.!!!  (regular expression)