728x90
반응형
아래 예제는 오라클에서 기본제공하는 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);
728x90
반응형
'기타 IT 지식 > SQL' 카테고리의 다른 글
[ Oracle ] 프로시저와 sql*plus 예제 (프로시저 호출하는 방법) (0) | 2021.05.10 |
---|---|
[ Oracle ] Oracle PL/SQL 화면출력 & 간단 예제 (0) | 2021.05.03 |
[ Oracle ] 서브쿼리(SubQuery) | 서브쿼리 예제 (0) | 2021.04.05 |
[ Oracle ] oracle 에서 기본으로 제공해주는 HR 스키마를 가지고 활용해보자! (0) | 2021.03.29 |
[ Oracle ] Oracle Self Join 예제, 쓰는 이유 (0) | 2021.03.29 |