[국비지원] KH 정보교육원 41일차 (1/2)

 오늘은 ANSI 조인을 마무리하고 서브쿼리를 배웠다. 조인보다 오히려 쉽고 간편한 것 같다. 자바는 람다를 나갔다. 람다 초반은 쉬었는데 좀 헷갈린다. 책은 오히려 얕게 배우니까 괜찮은데 수업은 깊이있게 파고드다보니 헷갈린다. 그냥 책을 계속 보는 것도 나쁘지 않을 것 같다

 

------------------------------------------------------
Sub-query (부속질의)

 가. Join은 한 개 이상의 테이블에서, 원하는 데이터를 조회
 나. 서브쿼리(=부속질의)는, 하나의 SELECT 문장 만으로 원하는
     데이터를 조회할 수 없을 때 사용하는 방법 (조인을 대체할 수 있다)
 다. 한 개 이상의 SELECT문장을 하나로 합쳐서, 하나의 실행가능
     한 문장으로 만들어, 원하는 데이터를 조회하는 방법
 라. 종류:
     (1) 상관 부속질의
         - 서브쿼리(=부속질의)의 단독 수행이 불가능한 경우
         - 메인쿼리의 데이터가 있어야 실행가능한 경우로,
         - 메인쿼리가 먼저 수행되고, 차례대로 서브쿼리가 수행됨
         - 메인쿼리의 결과가 서브쿼리에 전달되어, 서브쿼리 수행
     (2) 비상관 부속질의
         - 서브쿼리(=부속질의)의 단독 수행이 가능한 경우로,
         - 서브쿼리가 먼저 수행된 다음에, 메인쿼리가 수행됨
         - 서브쿼리의 결과가 메인쿼리에 전달되어 실행됨
 마. 사실상, 서브쿼리(=부속질의)는, SELECT 문의 모든 절(clause)
     에서 뿐만 아니라, 모든 DML문장에서도 사용가능하다!!! (*주의*)
 사. 반드시 소괄호()를 사용해야 함.
 아. 구분: 서브쿼리가 실행되어 반환된 행의 개수에 따라,
     (1) 단일 행 서브쿼리
         - 서브쿼리 실행 결과가 한 개의 행 반환.
         - 메인쿼리에서 사용가능 연산자: 
             *** 비교 연산자 (=, !=, <, >, <=, >=) ***
     (2) 복수 행 서브쿼리
         - 서브쿼리 실행 결과가 복수 개의 행 반환.
         - 메인쿼리에서 사용가능 연산자: 
             *** IN, ANY, ALL, EXISTS 등 ***
 자. 서브쿼리 사용시, **가장 주의해야** 할 점: 사용할 연산자
     사용된 연산자에 따라, 위 '아'와 같이 행 반환
------------------------------------------------------
SELECT select_list       -- 메인쿼리(바깥쪽 SELECT 문장)
FROM 테이블
WHERE 컬럼명 연산자 (
     SELECT select_list FROM 테이블    -- 서브쿼리(안쪽 SELECT 문장)
 );
------------------------------------------------------

사원정보에서, 'Whalen'보다 많은 월급을 받는 사원조회 Step1

* 하나의 SELECT문장만으로는 조회불가:
  가. 먼저, 'Whalen'의 월급이 얼마나인지 알아야 함. (SELECT 1)
  나. 조회한 'Whalen'의 월급보다 많은 월급을 받는 사원조회가능 (SELECT 2)

SELECT #1: Whalen 의 월급조회(4400)

SELECT
    salary
FROM
    employees
WHERE
    last_name = 'Whalen';

SELECT #2: Whalen 보다 월급을 많이 받는 사원 조회

SELECT
    last_name,
    salary
FROM
    employees
WHERE
    salary >= 4400;   -- Whalen의 월급(4400) 지정

------------------------------------------------------

사원정보에서, 'Whalen'보다 많은 월급을 받는 사원조회 Step2

위 #1과 같이, 하나 이상의 SELECT 문장이 있어야만, 
원하는 결과를 조회가능한 경우에, 서브쿼리(=부속질의) 사용가능.

즉, 서브쿼리는, 여러 번의 SELECT 문장을 수행해야 얻을 수 있는 결과를, 
하나의 중첩된(nested) SELECT 문장으로 만들어, 원하는 결과를 쉽게 조회 가능하게 함.

SELECT  -- 메인쿼리
    last_name,
    salary
FROM
    employees
WHERE
    -- (나중에수행) Whalen 보다 많은 월급여를 받는 사원조회
    salary >= (
        SELECT -- 서브쿼리
            salary
        FROM
            employees
        WHERE
            last_name = 'Whalen'  -- (먼저수행) Whalen 의 월급여 획득
    );

서브쿼리(= 비상관 부속질의):
a. 단독 수행에 지장없음
b. Whalen의 월급여가 메인쿼리에 전달됨

------------------------------------------------------
단일 행 Sub-query

가. 하나의 행을 반환
나. 반드시 단일 행 서브쿼리를 사용해야만 하는 경우:
     - 기본키(Primary Key)를 이용하는 경우
     - 그룹함수(MAX, MIN, SUM 등)를 이용하는 경우
------------------------------------------------------

(1) 평균 월급여보다 많은 월급을 받는 사원조회

평균 급여를 먼저 구하기 위해, 단일 행 서브쿼리 및 그룹함수 AVG 사용
따라서, 메인쿼리에서 사용가능한 연산자는 하나의 값과 비교하는 비교연산자임!

SELECT  -- 메인쿼리 (단일행 서브쿼리 -> 비교연산자)
    last_name,
    salary,
    ( SELECT avg(salary) FROM employees ) AS 평균급여
FROM
    employees
WHERE
       salary >= (   -- 단일 행 서브쿼리: 모든 사원의 평균 월급여 반환
        SELECT
            avg(salary) 
        FROM
            employees
    );

(2) 100번 부서의 최대 월급여와 동일한 월급을 받는 사원조회

SELECT -- 메인쿼리 (단일행 서브쿼리 -> 비교연산자)
    last_name,
    salary
FROM
    employees
WHERE
    salary = ( -- 단일 행 서브쿼리: 100번 부서의 최대 월급여 반환        
        SELECT
            max(salary)
        FROM
            employees
        WHERE
            department_id = 100
    );

 

(3) 부서의 모든 직원이, 100번 부서의 최대 월급여보다, 많은 월급을 받는 부서조회

SELECT -- 메인쿼리 (단일행 서브쿼리 -> 비교연산자)
    -- 최종적으로 선택된, 각 부서의 번호와 최소 월급여 출력
    department_id,
    min(salary)
FROM
    employees
GROUP BY
    department_id    -- 부서별 그룹 생성
HAVING                -- 2차 필터링: 조건에 부합하는 그룹만 필터링
    min(salary) > ( -- 단일행 서브쿼리 : 100번 부서의 최대 월급여 반환
        SELECT
            max(salary)
        FROM
            employees
        WHERE
            department_id = 100
    );

(4) 사원 Whalen 보다, 채용일자가 느린(Date 값이 큰) 사원조회

SELECT  -- 메인쿼리
    last_name,
    hire_date
FROM
    employees
WHERE
    hire_date > (   -- 단일 행 서브쿼리: Whalen의 채용일자 반환
        SELECT
            hire_date
        FROM
            employees
        WHERE
            last_name =  'Whalen'
    );

------------------------------------------------------
복수 행 Sub-query
------------------------------------------------------
가. 하나 이상의 행을 반환
나. 메인쿼리에서 사용가능한 연산자는 아래와 같음:
   (1) IN : 메인쿼리와 서브쿼리가 IN 연산자로 비교수행. 
              서브쿼리 결과값이 복수 개인 경우에 사용.
   (2) ANY : 서브쿼리에서, > 또는 < 같은 비교 연산자를 사용하고자 할 때 사용. 
                검색조건이 하나라도 일치하면 참.
   (3) ALL : 서브쿼리에서 > 또는 < 같은, 비교 연산자를 사용하고자 할 때 사용.
               검색조건의 모든 값이 일치하면 참.
   (4) EXISTS : 서브쿼리의 반환값이 존재하면, 메인쿼리를 실행하고,
                  반환값이 없으면 메인쿼리를 실행하지 않음.
------------------------------------------------------

(1) IN 연산자 사용한 복수행 서브쿼리
 가. 서브쿼리 반환값이 복수 개.
 나. 메인쿼리와 동등비교연산자(=) 방식으로 비교할 때 사용.