티스토리 뷰
상호관련 서브쿼리
단독으로 사용할 순 없고 메인쿼리 결과를 참고해야 실행할 수 있는 쿼리
입사 이래로 담당업무를 두 번 이상 변경한 적이 있는 사원 조회
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 |
댓글