8. select 심화 - subQuery

• subquery
  • 순서 : sub query(1) -> main query(2)
      
  • 종류
    1. single row : 1개 
       = 스칼라 서브쿼리
       
       
    2. multi row : 한 종류 column type 해당 값 여러개 || 복합
       
       • multi 연산자 : in, not in, any, exists, not exists - 고급 sql 
         
         
    3. multi column : 여러종류 colum type 해당 값 한개 || 복합
  • 위치
    1. where절 -> 비교 대상 in (서브쿼리문)
    2. from절
    3. having절
    4. select 절 상관쿼리
    5. create 절
    6. insert 절
    7. update 절

--1. single row
-- 김정미와 같은 직책을 가지는 사람의 이름, 직책, 급여, 부서번호
SELECT se.NAME , se.TITLE , se.DEPT_ID 
FROM S_EMP se 
WHERE DEPT_ID = ( SELECT DEPT_ID 
                  FROM S_EMP
                  WHERE NAME = '김정미') 
;

--2. multi row subQUERY
-- 여러 행이 전달 된 경우
SELECT se.NAME , se.DEPT_ID 
FROM S_EMP se 
WHERE DEPT_ID IN
                (SELECT sd.ID 
                FROM S_DEPT sd
                WHERE sd.REGION_ID = 3
                )
;

--3. multi column subQUERY <중요> 
-- 여러 종류의 column을 비교하는 경우
-- group 만 하면 누구인지 이름을 알 수 없다.
SELECT se.NAME , se.DEPT_ID, se.SALARY  
FROM S_EMP se 
WHERE (SALARY, DEPT_ID)  -- 2개의 column의 값과 비교하는 것이므로 2개 column 값을 넣어줘야함
IN (SELECT MIN(SALARY), DEPT_ID  
    FROM S_EMP
    group by DEPT_ID) 
;

-- 4. from 절에서의 subQUERY
SELECT e.name, e.title, d.name
FROM (SELECT name, title, dept_id 
      from STUDENT.S_EMP
	  WHERE TITLE = '사원') e, S_DEPT d
WHERE e.dept_id = d.id
;

-- 5. having 절에서의 subQUERY 
SELECT DEPT_ID , AVG(SALARY) 
FROM S_EMP
group by DEPT_ID 
HAVING AVG(SALARY) > (SELECT AVG(SALARY)
                      from S_EMP
                      WHERE DEPT_ID =113) 
;

-- 6. 상관 쿼리 
-- group by로 인해서 다른 관련 정보를 못가져오는데 아래 방식을 이용하면 부서 정보를 가져올 수 있다.
SELECT 
se.DEPT_ID , 
(select name from S_DEPT sd WHERE sd.id=se.DEPT_ID) "dept name", 
AVG(salary) 
FROM S_EMP se 
WHERE se.TITLE ='사원'
group by se.DEPT_ID 
;

-- 심화
-- gruop by로 이미 새로 만든 table은 dept_id , avg(salary)를 column명 으로 갖고 있다.
-- min을 하게 되면 해당 min 값이 dept_id row는 여러개고 min은 column값들을 합한 값이기 때문에 출력할 수 없다.
SELECT min(AVG(SALARY))
      FROM STUDENT.S_EMP
      group by dept_id
;

-- 이해를 위한 기본 SELECT 그룹함수 내용
SELECT sum(salary)
from STUDENT.S_EMP
;

-- 가장 적은 평균급여를 받는 직책에 대해 그 직책과 평균 급여를 나타내시오
SELECT TITLE, avg(se.SALARY)
from S_EMP se
group by se.TITLE 
HAVING AVG(se.SALARY) = (SELECT Min(AVG(SALARY))
                         FROM S_EMP
                         group by TITLE
                        )
;

-- 6.create 절에서의 subQUERY
-- AS는 해당 쿼리의 결과를 새로운 테이블로 생성할 때 사용하는 키워드
-- table 생성할 때만 그런 keyword.
-- column 명 옆에서는 별명  
CREATE table emp_113(id, name, mailid, start_date) as 
select id, name, mailid, start_date 
from STUDENT.S_EMP
WHERE DEPT_ID = 113;

--7. dml문에서의 subQUERY - 자주 쓰임
-- INSERT 
INSERT INTO emp_113
	(id, name, mailid, start_date) 
	select id, name, mailid, start_date 
	from STUDENT.S_EMP
WHERE start_date = '16/01/01';

--update
UPDATE INTO STUDENT.S_EMP
set dept_id(
	select dept_id 
	from STUDENT.S_EMP
	where title = '사장'
)
WHERE name = '안창환'
;

 

 

• multi row 연산자
• 종류
  1. in
  2. not in
  3. any
  4. all
  5. exists
  6. not exists
• 기본적으로 subquery는 나의 정보를 어떤 table에서 무언가와 비교해서 얻어낼 때 사용

-- 본인의 급여가 각 부서별 평균 급여중 어느한 부서의 평균 급여보다 적은 경우, 그사람 정보 출력하기
SELECT NAME , SALARY , DEPT_ID 
FRom S_EMP se 
WHERE se.SALARY < ANY (SELECT AVG(SALARY) from S_EMP se2 group by DEPT_ID);

-- 본인이 다른 사람의 관리자로 되어 있는 경우, 정보 출력
SELECT name, title, dept_id 
from S_EMP se 
WHERE EXISTS (select id from S_EMP se2 WHERE MANAGER_ID = se.ID)