본문 바로가기

.주제별/SQL,DB

SQL)강의- 제 7 장 subqueries

출처 : http://naver.kaist.ac.kr/oracle_sql/s07.htm

     

1. Nested subquery 처리방법

    ◈SELECT 문장은 Query Block으로 간주될 수 있다.
       아래 예는 두개의 query block으로 구성되어 있는데 main query와 inner query가 그것이다.
       ① Nestd SELECT 문장이 먼저 실행된다.
       ② 결과가 main query의 조건으로 전달한다.

    image


    실습 : Smith와 같은 부서에 근무하는 종업원들의 성과 직급을 검색한다

    SQL> SELECT last_name,title    FROM   s_emp
              WHERE  title = (SELECT title    FROM   s_emp
                                    WHERE  last_name = 'Smith');

     

2. 단일행 Subquery

    ◈단일행 Subquery는 nested SELECT 문장으로부터 오직 한개의 행(값)을 돌려준다.
    ◈단일행을 돌려주기 위한 subquery에서 그룹함수를 사용함으로써 main query의 자료 검색을
       가능하게 한다.(AVG 그룹함수는 단일값을 돌려준다.)

    실습 : Smith와 같은 부서에 근무하는 종업원들의 성과 직급을 검색한다

    SQL> SELECT last_name,title   
              FROM   s_emp
              WHERE  title = (SELECT title      FROM   s_emp
                                     WHERE  last_name = 'Smith');

    ◈Note : Stock Clerk이란 값을 돌려준 subquery는 singe row subquery이다.
                 Subquery가 오직 한개의 행만 돌려줄시 다음과 같은 단일행 비교연산자 또는
                 논리연산자를 사용해야 한다.( =, , <, >=,<=)
     

    실습 : 평균급여액보다 급여를 적게 받는 모든 사원 성, 직급 및 급여액을 Display하라.

    SQL> SELECT last_name,title,salary
              FROM   s_emp
              WHERE  salary <  (SELECT AVG(salary) FROM   s_emp);

     

3. FROM절상의 Subquery

    ◈ SELECT 문장의 FROM에 subquery를 추가할 수 있다.

    실습 : 지역이름, 고객번호 및 고객이름을 display한다. 

    SQL> SELECT r.name REGION, c.id, c.name CUSTOMER
              FROM   (SELECT *  FROM   s_region) r, s_customer c
              WHERE   r.id = c.region_id;

     

    Subquery 사용시 Error

    ◈만약 한 개 이상의 값을 돌려주는 subquery를 단일행 비교 연산자 사용 시 error가 발생한다.
    ◈Error를 정정하기 위해 비교연산자를 복수행 연산자인 IN 으로 변경한다.

    실습 : 다음 subquery는 한 개 이상의 값을 돌려 주며 단일행 비교 연산자를 사용하고 있다.
               이런 Error를 바로잡기 위해 =을 IN으로 변경한다.

    SQL> SELECT last_name, salary, dept_id  
              FROM   s_emp
              WHERE  last_name in (SELECT MIN(last_name)  
                                               FROM   s_emp
                                               GROUP BY dept_id);

     

4. 복수 행 Subqueries

    ◈한 개 이상의 값을 돌려주는 subquery를 Multiple Row Subquery라고 한다.
    ◈단일행 연산자 대신에 IN같은 복수행 연산자를 사용하도록 한다.
    ◈IN연산자는 한 개 이상의 값을 가지도록 한다.

    실습 : 각 부서의 최저급여와 같은 급여를 받는 모든 사원의 정보를 display한다.

    SQL> SELECT last_name,salary,dept_id
              FROM   s_emp
              WHERE  last_name IN (SELECT MIN(last_name)
                                               FROM   s_emp
                                               GROUP BY dept_id);

     

    ◈위의 subquery는 한 개 이상의 값을 돌려주는 GROUP BY절을 갖고 있다.
    ◈일련의 값들이 subquery로부터 돌려지기 때문에 IN 같은 복수행 비교연산자를 사용해야 한다.

5. FROM절상의 Subquery

    ◈ SELECT 문장의 FROM에 subquery를 추가할 수 있다.
    ◈ 이런 구조는 view와 유사하게 작동한다.

    실습 : 지역이름, 고객번호 및 고객이름을 display한다.

    SQL> SELECT r.name REGION, c.id, c.name CUSTOMER
              FROM   (SELECT *  FROM   s_region) r, s_customer c
              WHERE   r.id = c.region_id;

     

6. Subquery시 HAVING절

    Multiple Row Subquery
    ◈subquery는 WHERE절 뿐만 아니라 HAVING절에서도 사용가능하다.
    ◈오라클7 서버는 Subquery를 실행하여 그 결과가 main query의 HAVING절에서 사용하도록 한다.

    실습 : 32번 부서의 평균급여액보다 부서 평균급여액이 더 큰 모든 부서들을 display한다 .

    SQL> SELECT title,AVG(salary)
              FROM   s_emp
              GROUP BY title
              HAVING AVG(salary) = (SELECT MIN(AVG(salary))
                                                 FROM   s_emp
                                                 GROUP BY title);

     

    실습 : 평균급여액이 가장 적은 직급을 Display한다.

    SQL> SELECT title,AVG(salary)
              FROM   s_emp
              GROUP BY title
              HAVING AVG(salary) = (SELECT MIN(AVG(salary))
                                                 FROM   s_emp
                                                 GROUP BY title);