[SQL Plus] Sub Query

Sub Query : SQL 구문 내부에 또 다른 SQL 구문이 존재하는 구문

형식) select 필드명 from 테이블명 where 필드명 연산자 (select 필드명 from 테이블명 where 조건식);

                                                             -> 단일행 연산자(=, >, >=, <, <=, <>)

                                                             -> 다중행 연산자(in, any, all)

 

1. 단일 행 서브쿼리

Q. emp 테이블에서 SCOTT사원이 받는 SAL보다 많이 받는 사원을 구해서

그 사원의 EMPNO,ENAME,JOB,SAL 순으로 출력하시오.

SQL>
select empno,ename,job,sal from emp
where sal>(SELECT SAL FROM EMP WHERE ENAME='SCOTT');
결과>
     EMPNO ENAME      JOB              SAL
   ---------- ----------   ---------     ----------
      7839    KING       PRESIDENT       5000

 

2. 단일 행 서브쿼리 - 그룹 함수 사용

Q. emp 테이블에서 가장 HIREDATE가 오래된 사원을 찾아

그 사원의 ENAME,HIREDATE순으로 출력하시오.

SQL>select ename,hiredate from emp
where hiredate = (SELECT MIN(HIREDATE) FROM EMP);
결과>
ENAME      HIREDATE
---------- --------
SMITH      80/12/17

 

3. 다중 행 서브쿼리 - IN

Q. 부서별로 최소급여를 받는 사원의 ename,job,sal,deptno 순으로 출력하시오

SQL> select ename,job,sal,deptno from emp
where sal IN (select min(sal) from emp group by deptno);
결과>
ENAME      JOB         SAL     DEPTNO
---------- --------- ---------- ----------
SMITH      CLERK        800         20
JAMES      CLERK        950         30
MILLER     CLERK       1300         10

 

4. 다중 행 서브쿼리 - ANY

> ANY (조건식) : 최소값을 구하고 내림차순 정렬

< ANY (조건식) : 최대값을 구하고 오름차순 정렬

 

Q. emp 테이블에서 30번 부서의 최대급여를 받는 사원보다 적은 급여를 받는

사원의 ename,job,sal,deptno 를 출력하시오(단 30번 부서는 제외)

SQL> select ename,job,sal,deptno from emp
where sal < ANY(select sal from emp where deptno=30)
and deptno!=30

결과>

ENAME      JOB            SAL     DEPTNO
---------- ---------     ---------- ----------
SMITH      CLERK           800         20
ADAMS     CLERK         1100         20
MILLER     CLERK          1300         10
CLARK      MANAGER    2450         10

 

5. 서브쿼리 - INSERT

SQL> INSERT INTO ( select empno,ename,sal,hiredate,job,deptno
                         from b_emp3 where deptno=40) values
                      (7777,'JANG',4000, to_date('19-07-01','rr-mm-dd'),'MANAGER',40);

결과>

SQL> select * from b_emp3 where deptno=40;

     EMPNO  ENAME      JOB            MGR     HIREDATE        SAL    COMM   DEPTNO 
    ---------- ----------   ---------      ---------- --------      ---------- ---------- ---------- 
      7777     JANG       MANAGER                19/07/01       4000                40

6. 서브쿼리 - UPDATE

SQL> UPDATE b_emp3
                     set (job,sal)=(select job,sal from b_emp3 where ename='SCOTT')
              where ename='JONES';

결과>

SQL> select job,sal from b_emp3 where ename='JONES';
JOB             SAL
---------   ---------
ANALYST      3000

 

7. 서브쿼리 - DELETE

SQL> delete from b_emp3 where deptno=(select deptno from dept
                                                         where dname='SALES');

결과> 6 행이 삭제되었습니다.