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의 조건으로 전달한다.




♠실습 : 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);