(오라클SQL기초강좌)상관 서브쿼리(Correlated Sub Query)

(오라클SQL기초강좌)상관 서브쿼리(Correlated Sub Query)

• 1.
  SQL 기초과정 #7 상관서브쿼리 (Correlated Sub Query) 탑크리에듀(http://www.topcredu.co.kr), 이종철 This practice makes the expert
• 2.
  강의자료, 실습코드는 다음URL에서 확인하실 수 있습니다. http://ojc.asia
• 3.
  상관 서브쿼리(Correlated SubQuery) • 서브 쿼리와 메인쿼리간에 서로 상관 참조하는 쿼리로 Inner Query에 서 Outer Query의 어떤 컬럼을 이용하는 경우다. • 일반적인 Query는 서브 쿼리의 결과를 메인에서는 단순히 이용만 하 지만(이때 서브쿼리는 제공자로서의 역할을 한다) 상관 서브 쿼리에 서는 서브 쿼리가 메인 쿼리의 값을 이용하여 값을 구하면 그 값을 다 시 메인 쿼리에서 이용하는 구조이므로 서브쿼리는 값을 확인하는 확인자 역할을 하게된다. This practice makes the expert
• 4.
  상관 서브쿼리 실행순서 • 1. Outer query를 실행하여 행을 하나 읽는다. • 2. (1)에서 읽은행의 값을 이용하여 서브쿼리에서 필요한 값을 넣고 Subquery를 수행한다. • 3. (2)의 결과값으로 Outer query의 WHERE절을 평가하여 읽은 행의 선택여부를 결정한다. 참이면 데이터 추출, 아니면 버리고 Outer Query의 다음 레코드를 읽음 • 4. Outer query의 테이블에 행이 없을 때까지 (1)-(3)을 반복 수행한다. This practice makes the expert
• 5.
  예문 - 1 •각 부서의 최대 급여를 받는 사원의 부서코드, 이름, 급여를 출력하는 데 부서코드 순으로 오름차순 정렬하여 출력하는 예문 첫번째 상관서 브쿼리로 구현 select deptno, ename, sal from emp e1 where sal = (select max(sal) from emp e2 where e1.deptno = e2.deptno) order by deptno; DEPTNO ENAME SAL ---------- ---------- ---------- 10 KING 5000 20 SCOTT 3000 20 FORD 3000 30 BLAKE 2850 This practice makes the expert
• 6.
  예문 - 2 •각 부서의 최대 급여를 받는 사원의 부서코드, 이름, 급여를 출력하는데 부서코드 순으로 오름차순 정렬하여 출력하는 예문 • Pairwise 서브쿼리로 구현 select ename, sal, deptno from emp where (deptno, sal ) in (select deptno, max(sal) from emp group by deptno) order by deptno; DEPTNO ENAME SAL ---------- ---------- ---------- 10 KING 5000 20 SCOTT 3000 20 FORD 3000 30 BLAKE 2850 This practice makes the expert
• 7.
  예문 - 3 •각 부서의 최대 급여를 받는 사원의 부서코드, 이름, 급여를 출력하는데 부서코드 순 으로 오름차순 정렬하여 출력하는 예문 • 조인, 인라인뷰로 구현 select e1.ename, e1.sal, e1.deptno from emp e1, ( select deptno, max(sal) as msal from emp group by deptno ) e2 where e1.deptno = e2.deptno and e1.sal = e2.msal order by e1.deptno; DEPTNO ENAME SAL ---------- ---------- ---------- 10 KING 5000 20 SCOTT 3000 20 FORD 3000 30 BLAKE 2850 This practice makes the expert
• 8.
  예문 - 4 •각 직무(JOB)별로 최대 급여를 받는 사원의 직무, 이름, 급여를 출력하 는데 직무명순으로 오름차순 정렬하여 출력하는 예문 select job, ename, sal from emp e1 where sal = (select max(sal) from emp e2 where e1.job = e2.job) order by deptno; JOB ENAME SAL --------- ---------- ---------- PRESIDENT KING 5000 CLERK MILLER 1300 MANAGER JONES 2975 ANALYST SCOTT 3000 …… …… This practice makes the expert
• 9.
  예문 - 5 •자신이 속한 부서의 평균 급여보다 급여를 적게 받는 사원의 부서, 사 원명, 급여를 출력하되 부서 순으로 오름차순 정렬하여 출력 select deptno, ename, sal from emp e1 where sal < (select avg(sal) from emp e2 where e1.deptno = e2.deptno) order by deptno; DEPTNO ENAME SAL ---------- ------------ ---------- 10 CLARK 2450 10 MILLER 1300 20 SMITH 800 20 ADAMS 1100 …… …… This practice makes the expert
• 10.
  예문 - 6 •EMP 테이블에서 급여가 높은 사원 5명을 출력 하는 예문 select ename, sal from emp e1 where 5 > (select count(*) from emp e2 where e2.sal > e1.sal) order by sal desc; ENAME SAL ---------- ---------- KING 5000 SCOTT 3000 FORD 3000 JONES 2975 BLAKE 2850 This practice makes the expert
• 11.
  예문 - 7 •각 부서별로 급여가 높은 사람 2명씩 출력 하는 예문 select deptno, ename, sal from emp e1 where 2 > (select count(*) from emp e2 where e2.sal > e1.sal and e2.deptno = e1.deptno) order by deptno, sal desc; DEPTNO ENAME SAL ---------- ------------- ---------- 10 KING 5000 10 CLARK 2450 20 SCOTT 3000 20 FORD 3000 30 BLAKE 2850 30 ALLEN 1600 This practice makes the expert
• 12.
  예문 - 8 •사원이 한명이라도 있는 부서명 출력 SELECT dname FROM dept d WHERE EXISTS ( SELECT 1 FROM emp WHERE deptno = d.deptno); DNAME ----------------------- ACCOUNTING RESEARCH SALES This practice makes the expert
• 13.
  This practice makesExpert! PL/SQL단기속성(1일완성)SQL힌트/튜닝(단기2일교육)SQL기초과정