티스토리 뷰

상호관련 서브쿼리

단독으로 사용할 순 없고 메인쿼리 결과를 참고해야 실행할 수 있는 쿼리

 

입사 이래로 담당업무를 두 번 이상 변경한 적이 있는 사원 조회

SELECT em.employee_id, last_name, e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
	    FROM job_history
            WHERE employee_id = e.employee_id);

 

delete 문에서도 상호관련 서브쿼리를 쓸 수 있다.

DELETE FROM emp E
WHERE employee_id = 
		(SELECT employee_id
    		FROM emp_history
    		WHERE employee_id = E.employee_id);

 

WITH 절

메인쿼리 안에 동일한 서브쿼리를 여러 번 중첩해서 사용할 수 있다.

WITH CNT_DEPT AS
(
SELECT department_id,
COUNT(1) NUM_EMP
FROM EMPLOYEES
GROUP BY department_id
)
-- 메인쿼리
SELECT employee_id,
SALARY/NUM_EMP
FROM EMPLOYEES E
JOIN CNT_DEPT C
ON (e.department_id = c.department_id);

여기서 COUNT(1)은 COUNT(*)와 같다. 1은 Row_id 컬럼을 기준!

 

 

WITH
dept_costs AS(
    -- 1
    SELECT d.department_name, SUM(e.salary) AS dept_total
    FROM employees e JOIN departments d
    ON e.department_id = d.department_id
    GROUP BY d.department_name),
avg_cost AS (
    -- 2
    SELECT SUM(dept_total)/COUNT(*) AS dept_avg
    FROM dept_costs)
-- 메인쿼리
SELECT *
FROM dept_costs
WHERE dept_total >
	(SELECT dept_avg
        FROM avg_cost)
ORDER BY department_name;

 

dept_costs

d_name dept_total
IT
Sales
Test
Edu
50000
40000
45000
60000

avg_cost

dept_avg
48000

avg_cost보다 더 큰 dept_costs를 출력 => 이 테이블에서는 IT와 Edu가 출력 

부서별 평균 합계가 우리 회사 총 평균보다 큰 경우를 출력

 

 

insert 에서의 서브쿼리

insert into (select * 
	     from departments)
values (400, 'ABC', 107, 1700);
(==)
insert into departments
values (400, 'ABC', 107, 1700);


insert into departments(department_id, department_name)
values (400, 'ABC');
(==)
insert into (select department_id, department_name
	    from departments)
values (400, 'ABC');

 

특정 조건을 만족할 때만 실행되기를 원할 때 with check option을 붙인다.

부서 아이디가 400이상 일 때만 insert 되도록

-- 400일 때만 insert하겠다!
insert into (select department_id, department_name
		from departments
        where department_id = 400
        with check option)
values (420, 'ABC');

 

values에 390이면 안 들어감 !

 

'배운 것 기록 > DB' 카테고리의 다른 글

DB 정리용  (0) 2022.09.12
[Oracle] 집합연산자  (0) 2022.09.08
[Oracle] 다른 DB 접속하는 방법 / DB 전환 / echo  (0) 2022.09.02
[Oracle] DB 종료 / DB 생성  (0) 2022.09.01
[Oracle] ROLE  (0) 2022.08.30
댓글
최근에 올라온 글
«   2024/12   »
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31
글 보관함