L09 Sub Query

2016. 10. 21(Fri)

L09 Sub Query

• Sub Query

• 서브 쿼리는 하나의 SELECT 문장의 절 안에 폼함된 또 하나의 SELECT 문장입니다.

• 서브 쿼리를 포함하고 있는 쿼리를 메인 쿼리, 포함된 또 하나의 쿼리를 서브 쿼리라 합니다.

• 서브 쿼리는 비교 연산자의 오른쪽에 기술해야 하고 반드시 괄호로 둘러쌓아야 합니다.

• 서브 쿼리는 메인 쿼리가 실행되기 이전에 한 번만 실행이 됩니다.

• ‘SMITH’와 동일한 부서에서 일하는 직원의 이름을 출력하세요.

SELECT ename, deptno

FROM EMP

WHERE deptno = (

 SELECT deptno

 FROM EMP

 WHERE LOWER(ename) = 'smith');

• 연습문제) JONES가 일하는 부서의 이름은?

SELECT DNAME

FROM DEPT

WHERE DEPTNO = (

 SELECT DEPTNO

 FROM EMP

 WHERE LOWER(ename) = 'jones');

1. Single Row Sub Query

1. 단일 행(Single Row) 서브 쿼리는 수행 결과가 오직 하나의 로우(행, row)만을 반환하는 서브 쿼리를 갖는 것을 말한다.

2. 단일 행 서브 쿼리문에서는 이렇게 오직 하나의 로우(행, row)로 반환되는 서브 쿼리의 결과는 메인 쿼리에 보내게 되는데 메인 쿼리의  WHERE 절에서는 단일 행 비교 연산자인 =, >, >=, <,<=, <>fmf tkdydgodi gkqslek.

3. KING과 같은 부서에서 근무하는 사원의 이름과 부서 번호를 출력하는 SQL 문을 작성해 보시오.

SELECT ename, deptno FROM EMP

WHERE deptno=(SELECT deptno

    FROM EMP

    WHERE ename=’KING’);

4. 문제1) MARTIN과 동일한 직급을 가진 사원의 이름을 출력하세요

답)       SELECT ename, job FROM EMP

WHERE ename<>'MARTIN’ AND

JOB=(SELECT job

FROM EMP

WHERE ename='MARTIN');

5. 문제2) MARTIN의 급여와 동일하거나 더 많이 받는 사원명과 급여를 출력하세요.

답)       SELECT ename, SAL FROM EMP

WHERE ename<>'MARTIN'

AND SAL >= (SELECT sal

FROM EMP

     WHERE ename='MARTIN')

          ORDER BY SAL;

6. 문제3) DALLAS에서 근무하는 사원의 이름, 부서 번호를 출력하세요.

답)       SELECT ename, deptno FROM EMP

WHERE deptno=(SELECT deptno

    FROM dept

    WHERE loc=’DALLAS');

7. 문제4) SALES(영업부)부서에서 근무하는 모든 사원의 이름과 급여를 출력하세요.

답)       SELECT ename, sal FROM EMP

WHERE deptno=(SELECT deptno

             FROM dept

             WHERE dname='SALES');

    

8. 문제5) 직속상관이 KING인 사원의 이름과 급여를 출력하세요.

답)       SELECT ename, sal FROM EMP

WHERE mgr =(SELECT empno

           FROM EMP

           WHERE ename='KING');

2. Single Row Sub Query Used Aggregate Functions

1. Aggregate Functions는 집계 함수로 우리가 흔히 말하는 Group 함수이다.

2. 문제1) 평균 급여보다 더 많은 급여를 받는 사원을 검색하세요.

1. SELECT ename, sal FROM EMP

WHERE sal > (SELECT AVG(sal) FROM EMP);

3. Multiple Row Sub Query

1. 다중 행 서브 쿼리는 서브 쿼리에서 반횐되는 결과가 하나 이상의 행일 때 사용하는 서브 쿼리입니다. 다중 행 서브 쿼리는 반드시 다중 행 서브 쿼리는 반드시 다중 행 연산자(Multipl Row Operator)와 함께 사용해야 합니다.

2. IN

1. 메인 쿼리의 비교 조건(‘=’ 연산자로 비교할 경우)이 서브 쿼리의 결과 중에서 하나라도 일치하면 참입니다.

2. 예제1) 급여를 3000 이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원 이름, 급여, 부서명을 출력하세요.

SELECT e.ENAME, e.SAL, d.dname

FROM EMP e, DEPT d

where e.deptno = d.DEPTNO

AND e.DEPTNO IN(SELECT DEPTNO

        FROM EMP

        WHERE SAL >= 3000)

                                order by sal;

3. 문제1-1) 부서별로 가장 급여를 많이 받는 사원의 정보(사원 번호, 사원 이름, 급여, 부서 번호)를 출력하시오.(IN 연산자 이용)

SELECT EMPNO, ENAME, SAL, DEPTNO

FROM EMP

WHERE SAL IN (SELECT MAX(SAL)

                FROM EMP

                GROUP BY DEPTNO);

4. 문제 1-2) 직급(JOB)이 MANAGER인 사람의 속한 부서의 부서 번호와 부서명, 지역을 출력하세요.

3. ALL

1. 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 참입니다.

2. 찾아진 값에 대해서 AND 연산을 해서 모두 참이면 참이 되는 셈이 됩니다. >all은 “모든 비교값 보다 크냐”고 묻는 것이되므로 최대값보다 더 크면 참이 됩니다. (ANY(30,40,50)50이 선택됨)

3. 예제2) 30번 소속 사원들 중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사람의 이름, 급여를 출력하세요.

• MAX 사용

SELECT ENAME, SAL

FROM EMP

WHERE SAL > ( SELECT MAX(SAL)

               FROM EMP

               WHERE DEPTNO = 30);

• >ALL() 사용

SELECT ENAME, SAL

FROM EMP

WHERE SAL > ALL ( SELECT SAL

           FROM EMP

           WHERE DEPTNO = 30);

4. 문제 2-1) 영업 사원들 보다 급여를 많이 받는 사원들의 이름과 급여, 직급(담당 업무)를 출력하되, 영업사원은 출력하지 않는다.

4. ANY, SOME

1. 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 하나 이상이 일치하면 참입니다.

2. >ANY()는 찾아진 값에 대해서 하나라도 크면 참이 되는 셈이 됩니다. 그러므로 찾아진 값 중에서 가장 작은 값 즉, 최소 값보다 크면 참이 됩니다. (ANY(30,40,50) 30이 선택됨

3. 예제3-1) 부서번호가 30번인 사원들 중 급여가 가장 낮은 사람보다 높은  급여를 받는 사원의 이름과 급여를 출력하세요

• MIN()을 사용한 구문

SELECT ENAME, SAL FROM EMP

WHERE SAL> (SELECT MIN(SAL) FROM EMP

           WHERE DEPTNO=30)

           ORDER BY SAL;

• ANY()를 사용한 구문

4. 문제3-1) 영업 사원들의 최소 급여보다 많이 받는 사원들의 이름과 급여, 직급(담당 업무)를 출력하되 영원사원은 출력하지 않습니다.

5. 문제3-2) SMITH와 동일한 직급을 가진 사원의 이름과 직급을 출력합니다.(단일행 서브 쿼리 이용)

6. 문제3-3)  직급이 ‘SALESMAN’인 사원이 받는 급여의 최대 급여보다 많이 받는 사원들의 이름과 급여를 출력하되 부서번호가 20번인 사원은 제외합니다.(ALL 연산자 이용)

7. 3-4) 직급이 ‘SALESMAN’인 사원이 받는 급여의 최소 급여보다 많이 받는 사원들의 이름과 급여를 출력하되 부서번호가 30번인 사원은 제외한다.(ANY 연산자 이용)

4. EXIST

메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 만족하는 값이 하나라도

존재하면 참입니다.

1. SELECT 칼럼

FROM 테이블

WHERE EXISTS (서브 쿼리)

1. 서브 쿼리의 결과가 하나 이상 존재할 경우 그 조건으로 메인 쿼리를 실행한다.

1. SELECT 칼럼

FROM A

WHERE EXISTS(

SELECT*FROM B

WHERE A.KEY=B.KEY);

1. 마치 JOIN을 하듯 “A.KEY=B.KEY”라는 구문이 잇는데 이를 “SET 한다”라고 한다.

2. 서브 쿼리의 SET 한 검색 결과가 존재할 경우 SET의 조건으로 메인 쿼리를 실행한다.

3. WHERE의 조건으로 Inner Join이 필요하지만 결과는 한 개의 테이블에서 해결될 경우 사용하면 성능이 향상된다.

4. EXISTS()는 IN()과 거의 똑같이 사용 가능하지만 IN()은 결과를 실제로 출력하지만 EXISTS()는 결과가 실제로 존재 하는지만 검사해서 성능이 IN()보다 좋다.

1. 예1-1) EMP 테이블에서 사용 중인 DEPT 테이블의 DEPTNO와 DENAME을 출력하라. IN()을 이용

SELECT DNAME, DEPTNO

FROM DEPT

WHERE DEPTNO IN(

SELECT DISTINCT(DEPTNO)

FROM EMP);

4. 예1-2) 위의 문제를 EXITS() 이용

SELECT DNAME, DEPTNO

FROM DEPT

WHERE EXISTS(

SELECT*FROM EMP

WHERE EMP.DEPTNO=DEPT.DEPTNO);

5. 예2-1) EMP 테이블에서 사용하지 않는  DEPT 테이블의 DEPTNO와 DENAME을 출력하라. NOT IN()을 이용

SELECT DNAME, DEPTNO

FROM DEPT

WHERE DEPTNO NOT IN(

SELECT DISTINCT(DEPTNO)

FROM EMP);

6. 예2-2) 위의 문제를 NO EXISTS() 이용

SELECT DNAME, DEPTNO

FROM DEPT

WHERE NOT EXISTS(

SELECT*FROM EMP

WHERE EMP.DEPTNO = DEPTNO);

7. 문제1) 커미션을 받는 부서명을 출력하라 (EXITS 이용)

SELECT DNAME

FROM DEPT

WHERE EXISTS(

SELECT*FROM EMP

WHERE COMM>0

AND EMP.DEPTNO=DEPT.DEPTNO);

8. 문제2) 부서명이 ‘ACCOUNTING’인 사원의 사원 번호와 이름, 급여를 출력하세요.

SELECT ENAME, SAL, JOB

FROM EMP

WHERE EXISTS (

SELECT*FROM DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO

AND DNAME='ACCOUNTING');

9. 문제3) 50번인 부서 번호가 있으면 SMITH씨의 부서를 50번으로 바꾸세요. (50번 부서가 없기 때문에 실행 0)

UPDATE EMP SET DEPTNO=50

WHERE ENAME='SMITH'

AND EXISTS(

SELECT * FROM DEPT

WHERE deptno=50);