상관서브쿼리(== 서브상관쿼리)
상관서브쿼리(= 서브상관쿼리) 이라함은
Main Query(= 외부쿼리)에서 사용된 테이블(뷰)에 존재하는 컬럼이
Sub Query(=내부쿼리)의 조건절(where절, having절)에 사용되어질 때를
상관서브쿼리(= 서브상관쿼리) 라고 부른다.
-- employees 테이블에서 기본급여에 대해 전체등수 및 부서내등수를 구하기
-- 첫번째 방법은 rank() 함수를 사용하여 구하는 것이다.
select department_id as 부서번호
, employee_id as 사원번호
, salary as 기본급여
, rank() over( order by salary desc ) as 전체등수
, rank() over( partition by department_id
order by salary desc) as 부서내등수
from employees
order by 1, 3 desc;
-- 두번째 방법은 상관서브쿼리(= 서브상관쿼리) 를 사용하여 구하는 것이다.
-- 자신의 기본급여가 24000이라면
select count(*) + 1 as 기본급여등수
from employees
where salary > 24000;-- 자신의 기본급여가 17000이라면
select count(*) + 1 as 기본급여등수
from employees
where salary > 17000;...
select E.department_id as 부서번호 -- E. 는 생략 가능
, E.employee_id as 사원번호 -- E. 는 생략 가능
, E.salary as 기본급여 -- E. 는 생략 가능
, ( select count(*) + 1
from employees
where salary > E.salary ) as 전체등수 -- ()안의 E. 는 생략 불가, main query 안에 있는 salary 들이 하나하나 들어옴
, ( select count(*) + 1
from employees
where department_id = E.department_id and
salary > E.salary ) AS 부서별등수
from employees E
order by 1, 3 desc;
------ ====== **** Sub Query 를 사용하여 테이블을 생성할 수 있다. **** ====== ------
create table tbl_employees_3060
as
select department_id
, employee_id
, first_name || ' ' || last_name AS ENAME
, nvl(salary + (salary * commission_pct), salary) AS MONTHSAL
, case when substr(jubun, 7, 1) in('1','3') then '남' else '여' end AS GENDER
, jubun
from employees
where department_id in (30, 60);
-- Table TBL_EMPLOYEES_3060이(가) 생성되었습니다.
------ ***** !!!! 필수로 꼭 암기하기 !!!! ***** ------
----- 상관서브쿼리(= 서브상관쿼리) 를 사용한 UPDATE 처리하기 -----
회사에 입사해서 delete 또는 update 를 할 때 먼저 반드시 해당 테이블을 백업해두고
그 뒤에 delete 또는 update 를 해야 안전하다.
create table tbl_employees_backup
as
select *
from employees;
-- Table TBL_EMPLOYEES_BACKUP이(가) 생성되었습니다.
employees 테이블의 모든 사원들의 성을 '차' 이름을 '은우'로 변경
select *
from tbl_employees_backup;
update employees set first_name = '은우', last_name = '차'
commit;employees 테이블의 '차' '은우' 를 백업테이블을 이용해서 원래의 이름으로 변경하기
update employees E set first_name = ( select first_name
from tbl_employees_backup
where employee_id = E.employee_id )
, last_name = ( select last_name
from tbl_employees_backup
where employee_id = E.employee_id );
-- 107개 행 이(가) 업데이트되었습니다.
commit;
-- 커밋 완료.
----- **** Sub Query 절을 사용하여 데이터를 입력(insert)할 수 있다.**** -----
select count(*)
from TBL_EMPLOYEES_SUB;
-- 0비어있는 테이블에 데이터( 부서번호가 50, 80번인 부서의 여성 사원 정보 )를 삽입
insert into TBL_EMPLOYEES_SUB
select *
from employees
where department_id in ( 50,80 ) AND
substr(jubun, 7, 1) in ('2','4');
-- 37개 행 이(가) 삽입되었습니다.
commit;
-- 커밋 완료.
----- **** Sub Query 절을 사용하여 데이터를 수정(update)할 수 있다.**** -----
--- tbl_employees_sub 테이블에서 부서번호 50번에 해당하는 사원들의 commission_pct 값을
employees 테이블에서 가장 적게 받는 commission_pct 값으로 수정하기 ---
update tbl_employees_sub set commission_pct = (employees 테이블에서 가장 적게 받는 commission_pct 값)
where dapartment_id = 50;
-- employees 테이블에서 가장 적게 받는 commission_pct 값
-- ==>
select min(commission_pct)
from employees;
update tbl_employees_sub set commission_pct = (select min(commission_pct)
from employees)
where department_id = 50;
-- 22개 행 이(가) 업데이트되었습니다.
commit;
----- **** Sub Query 절을 사용하여 데이터를 삭제(delete)할 수 있다.**** -----
--- tbl_employees_sub 테이블에서 salary 가 평균 salary 보다 작은 행들만 삭제하기
delete from TBL_EMPLOYEES_SUB
where salary < ( tbl_employees_sub 테이블에서 평균 salary );
tbl_employees_sub 테이블에서 평균 salary
==>
select avg(salary)
from tbl_employees_sub; -- 5645.945945945945945945945945945945945946
delete from TBL_EMPLOYEES_SUB
where salary < ( select avg(salary)
from tbl_employees_sub;
-- 21개 행 이(가) 삭제되었습니다.
commit;