[DB] 오라클 서브쿼리 예제

아래 예제는 오라클에서 기본제공하는 hr 스키마를 이용했습니다.

 

 

 

 

 

 

--ex 2-1) 부서별로 사원의 수가 5명 이상인 부서의
-- 부서번호와 부서명 
-- , 부서장사번, first_name을 인원수순으로 출력하라.

select d.department_id, d.department_name, d.manager_id, 
        (select first_name from employees
        where d.manager_id = employee_id
        ), count(*)
from departments d join employees e
    on d.department_id = e.department_id
group by d.department_id, d.department_name, d.manager_id
having count(*) >=5
order by 5 desc;

 

 

 

 

 

 

-- ex 2-2) 부서별로 얼마나 많은 급여가 지급되는지 알고 싶다.
-- 전체 총급여에서 부서가 차지하는 비율을 볼 수 있도록 작성하라. 
-- 부서번호,부서별 급여합계, 비율(%가 맨뒤에 오도록)을 비율순으로 출력하라.

select department_id, sum(salary), 
lpad(round((sum(salary)/(select sum(salary) from employees))*100) || '%',10)
from employees
group by department_id
order by 3;

 

 

 

 

 

 

-- ex 2-3)직속상사가 없는 사원을 포함하여 
-- 모든 사원의 직속상사가 나오도록 하고 싶다. 
--사번(employee_id), first_name, 직속상사사번(manager_id), 
-- 직속상사first_name을 사번순으로 출력하라. (correlated subquery이용)

select employee_id, first_name, manager_id, 
    (select first_name from employees where e.manager_id = employee_id)
from employees e
order by 1;

 

 

...

 

 

 

 

 

-- ex 2-4)모든 급여의 평균급여보다 시(city)별로 
-- 지급된 평균급여가 적은 시에 대하여 
-- 시(city), 시별 평균급여를 출력하시오.


select l.city,
round(avg(e.salary)) 
from locations l 
join departments d on l.location_id=d.location_id
join employees e on d.department_id = e.department_id
group by l.city
having avg(e.salary)<(select avg(salary) from employees);

 

 

 

 

 

 

-- ex 2-5)두 번이상의 job이력을 가진 모든 사원의 사번, 
-- first_name, 현재부서번호, 현재부서명, job이력부서번호, 
-- job이력부서명을 출력하시오.


select e.employee_id, e.first_name,
e.department_id, d.department_name,
h.department_id, (select department_name from departments where
        department_id = h.department_id)
from employees e 
join departments d on e.department_id = d.department_id
join job_history h on h.employee_id = e.employee_id
where e.employee_id in 
    (select employee_id from job_history group by employee_id having count(*)>=2);

-- 다른 방법

select e.employee_id, e.first_name,
e.department_id, d.department_name,
h.department_id, (select department_name from departments where
        department_id = h.department_id)
from employees e 
join departments d on e.department_id = d.department_id
join job_history h on h.employee_id = e.employee_id
where 2<=(select count(*) from job_history where e.employee_id=employee_id);

 

 

 

 

 

 

-- ex 2-6) job이력이 있는 모든 사원의 
-- 사번, first_name, 현재 job번호, 현재 job명을 출력하시오.
-- (exists 이용)

select e.employee_id, e.first_name, e.job_id, j.job_title
from employees e join jobs j on e.job_id = j.job_id
where exists (select *
                from job_history where e.employee_id=employee_id);
                

 

 

 

 

 

 

 

            
-- ex 2-7)CTAS기법으로 employees를 testemp2 테이블로 복사하고 
-- testemp2의 job_id가 ‘ST_MAN’인 사원의 salary를 
-- 부서장사번이 ‘100’인 부서의 평균월급으로 update하라.

create table testemp3 as select * from employees;

update testemp3 t
set salary = (select avg(salary) from employees e 
                join departments d on d.department_id = e.department_id
                where d.manager_id='100')
where job_id='ST_MAN';

 

 

 

 

 

 

-- ex 2-8)부서명이 ‘Marketing’인 사원 중에서 
-- 그 부서의 평균 급여보다 
-- 더 높은 급여를 받는 사원의 first_name과 급여를 출력하라.
select d.department_id,avg(e.salary) from employees e join departments d
            on d.department_id = e.department_id group by d.department_id;

select first_name, salary from employees e
where 
    (select department_name from departments d
        where e.department_id = d.department_id)='Marketing' and
     salary >
     (select avg(salary) from employees e1 join departments d
            on e1.department_id = d.department_id);