8장 서브 쿼리.

Presentation on theme: "8장 서브 쿼리."— Presentation transcript:

1 8장 서브 쿼리

2 목 차 서브 쿼리의 문법과 종류 단일 행 서브 쿼리 서브 쿼리에서 그룹 함수의 사용 다중 행 서브 쿼리 IN 연산자
ALL 연산자 ANY 연산자

3 이름이 ‘Jones’인 사원의 부서이름 조회
서브 쿼리의 문법과 종류 서브쿼리 : select 문 안에 또 다른 select문이 있는 것 - ( … ) 로 처리하여 가장 먼저 수행 이름이 ‘Jones’인 사원의 부서이름 조회

4 단일 행 서브쿼리 : 서브쿼리가 반환하는 결과 값이 단일 행인 경우
단일 행 서브 쿼리 단일 행 서브쿼리 : 서브쿼리가 반환하는 결과 값이 단일 행인 경우 Jones 가 있는 부서의 이름 조회 SQL> SELECT dname      FROM   dept      WHERE  deptno = ( SELECT deptno                      FROM  emp                       WHERE  ename='JONES'); SQL> SELECT deptno           FROM  emp         WHERE  ename='JONES'; SQL> SELECT dname      FROM   dept    WHERE  deptno = 20                  

5 단일 행 서브 쿼리 –cont’d 10번 부서에서 근무하는 사원의 이름과 부서명 출력
SQL> SELECT e.ename, d.dname   FROM   emp e, dept d     3 WHERE  e.deptno = d.deptno 4 AND d.deptno = 10;                      SQL> SELECT e.ename, d.dname   FROM   emp e,     3 ( SELECT deptno, dname 4 FROM dept 5 WHERE deptno=10 ) d 6 WHERE  e.deptno = d.deptno;             테이블명 대신 서브쿼리를 사용하는 경우

6 서브 쿼리에서 그룹 함수의 사용 평균급여보다 많이 받는 사원의 이름과 급여 출력 SQL> SELECT ename, sal
     FROM   emp      WHERE  sal > ( SELECT AVG(sal)                       FROM  emp);  평균급여보다 많이 받는 사원의 이름과 급여를 고액순으로 출력 SQL> SELECT ename, sal      FROM   emp      WHERE  sal > ( SELECT AVG(sal)                       FROM  emp) ORDER  BY sal DESC;

7 서브 쿼리에서 그룹 함수의 사용 –cont’d
10번 부서의 최대 급여와 같은 급여를 받는 사원번호와 이름 출력 SQL> SELECT  empno, ename      FROM    emp    WHERE   sal = ( SELECT  MAX(sal)                       FROM    emp                                WHERE  deptno = 10);

8 다중 행 서브쿼리 : 서브쿼리가 반환하는 결과 값이 여러 행인 경우
다중 행 서브 쿼리 다중 행 서브쿼리 : 서브쿼리가 반환하는 결과 값이 여러 행인 경우 서브쿼리 결과 값 하나 일치 하나이상 일치 모든값이 일치 하나라도 만족

9 IN 연산자 A 나 B로 시작하는 이름을 가진 사원과 같은 부서 사원의 이름, 입사일, 부서번호 출력
SQL> SELECT  ename, hiredate, deptno    FROM   emp      WHERE   deptno IN ( SELECT  deptno                              FROM  emp                             WHERE  ename < ‘C');  Blake와 같은 부서사원의 이름, 입사일, 부서번호 출력(BLAKE 제외) SQL> SELECT  ename, hiredate, deptno    FROM   emp      WHERE   deptno = ( SELECT  deptno                              FROM  emp                             WHERE  ename = 'BLAKE') and ename != ‘BLAKE’; 

10 IN 연산자 –cont’d 급여가 3000 이상인 사원이 소속된 부서 사원의 이름, 급여, 부서번호 출력
SQL> SELECT ename, sal, deptno    FROM   emp      WHERE  deptno IN ( SELECT deptno                            FROM   emp                            WHERE  sal >= 3000); 급여가 3000 이상인 사원의 부서번호 출력 SQL> SELECT distinct deptno    FROM   emp      WHERE  sal >= 3000;

11 ALL 연산자 30번 부서 사원의 최대 급여보다 더 많이 받는 사원의 이름, 급여 출력
SQL> SELECT ename, sal      FROM emp      WHERE sal > ALL (SELECT sal                          FROM emp                         WHERE deptno = 30); SQL> SELECT ename, sal      FROM emp      WHERE sal > ( SELECT max(sal)                          FROM emp                         WHERE deptno = 30);

12 ANY 연산자 30번 부서의 사원 급여 최소값보다 더 많이 받는 사원의 이름, 급여 출력
SQL> SELECT ename, sal      FROM emp      WHERE sal > ANY (SELECT sal                          FROM emp                         WHERE deptno = 30); SQL> SELECT ename, sal      FROM emp      WHERE sal > (SELECT min(sal)                          FROM emp                         WHERE deptno = 30);

13 예제 1,2 SCOTT의 급여 이상을 받는 사원의 이름, 급여 출력 SQL> SELECT ename, sal
     FROM emp      WHERE sal >= (SELECT sal                         FROM emp                         WHERE ename=‘SCOTT’); 직급이 사원(clerk)인 사람의 부서번호, 부서명, 지역 조회 SQL> SELECT deptno, dname, loc      FROM dept      WHERE deptno IN (SELECT deptno                        FROM emp                        WHERE job=‘CLERK’);

14 예제 3, 4 이름에 T를 포함하고 있는 사원과 같은 부서에 근무하는 사원의 번호와 이름 출력
SQL> SELECT empno, ename      FROM emp      WHERE deptno IN (SELECT deptno                          FROM emp                         WHERE ename LIKE ‘%T%’); 부서위치가 DALLAS인 모든 사원의 이름, 부서번호 조회 SQL> SELECT ename, deptno      FROM emp      WHERE deptno = (SELECT deptno                        FROM dept                        WHERE loc= ‘DALLAS’);

15 이름이 King 인 사원의 사번을 상사(mgr)로 가진 사원 조회
예제 5, 6 Sales 부서의 모든 사원의 이름과 급여 출력 SQL> SELECT ename, sal      FROM emp      WHERE deptno = (SELECT deptno                         FROM dept                         WHERE upper(dname) = ‘SALES’); King의 부하사원들의 이름과 급여, 상사 조회 SQL> SELECT ename, sal , mgr      FROM emp      WHERE mgr = (SELECT empno                        FROM emp                        WHERE ename=‘KING’); 이름이 King 인 사원의 사번을 상사(mgr)로 가진 사원 조회

16 예제 7 급여가 평균급여보다 많고 이름에 S가 들어가는 사원과 동일한 부서에 근무하는 모든 사원의 번호, 이름, 급여 출력
SQL> SELECT empno, ename, sal      FROM emp      WHERE deptno IN (SELECT deptno                         FROM emp WHERE sal > (SELECT avg(sal) FROM emp )                         AND ename like ‘%S%’); 급여가 평균급여보다 많고 이름에 S가 들어가는 사원과 동일한 부서에 근무하는

17 Tip 부서위치가 DALLAS 가 아닌 모든 사원의 정보 조회 SQL> SELECT * FROM emp
     WHERE deptno NOT IN (SELECT deptno                        FROM dept                        WHERE loc=‘DALLAS’); 부서위치가 DALLAS 인 부서번호 조회 사번이 7566인 사원과 같은 상사를 가진 사원 정보 조회(널값 포함) SQL> SELECT *      FROM emp      WHERE NVL(mgr, 0) = (SELECT NVL(mgr,0)                        FROM emp                        WHERE empno = 7566); 사번이 7566 인 사원의 상사 조회

18 Tip 부하직원이 없는 사원 정보 출력 SQL> SELECT * FROM emp
     WHERE empno NOT IN (SELECT NVL(mgr,0)                        FROM emp );      FROM emp m      WHERE m.empno NOT IN (SELECT e.mgr                        FROM emp e WHERE e.mgr=m.empno); 부서장 사원 정보 조회 SQL> SELECT *      FROM emp      WHERE empno IN (SELECT NVL(mgr,0)                        FROM emp );