[ Oracle ] oracle 에서 기본으로 제공해주는 HR 스키마를 가지고 활용해보자!

 

 

 

오라클에서 기본으로 제공해주는 hr 스키마는 위 그림과 같은 구조를 가진다.

 

이제 이 스키마를 가지고 여러 문제들을 해결하면서 sql 을 공부해보자!

 

 

 

-- 1-1.  사원의 first_name이 기억나지 않는다. 그 last_name에 'smith'라는 단어가 들어가는데 대문자,
-- 소문자도 모른다. 그 사원의 사번(employee_id), first_name, last_name을 출력하라.


select employee_id, last_name,first_name from EMPLOYEES where lower(last_name) like '%smith%';

--1-2.  고용일자(hire_date)가 오늘일자와 같은 달이고 고용일자의 요일이 월요일이나 
--금요일인 사원에 대한 사번(employee_id), first_name, last_name, 고용일자를 최근 고용된 사원순으로 출력하라. 

select employee_id,first_name,last_name,hire_date from EMPLOYEES 
where to_char(sysdate,'mm')= to_char(hire_date,'mm') and 
(to_char(hire_date,'day') = '월요일' or to_char(hire_date,'day') = '금요일')
order by 4 desc;


-- 1-3.  부서별로 사원의 수가 5명 이상인 부서의 부서번호(department_id)와 
--부서명(department_name), 부서장사번(manager_id)을 인원수순으로 출력하라.
select d.department_id, d.department_name, d.manager_id, count(d.department_id) 
from EMPLOYEES e join DEPARTMENTS d on d.department_id = e.department_id 
group by d.department_id,d.department_name,d.manager_id having count(d.department_id) >= 5
;
-- 1-4.   근속년수가 15년 이상인 사원에 대해서 부서별로 얼마나 많은 급여가 지급되는지 알고 싶다.
-- 부서별 해당 사원이 3명 이상인 부서만 부서번호, 부서별 급여합계를 급여합계가 높은 순으로 출력하라.

select department_id, sum(salary) from employees
where (hire_date-sysdate)/365 >= 15
group by department_id
having count(*) >= 3 order by 2 desc;

-- 1-5.   사원별로 급여에 급여*commission비율을 더한 금액을 모든 사원에게 지급하려고 한다.  
-- 사번(employee_id), 급여(salary), commission비율(commission_pct), 지급금액을 사번순으로 출력하라.
--(단, nls_currency화폐단위가 뒤에 나오도록, 천단위가 콤마로 표시되고, 일의 자리에서 반올림하여라)
select employee_id, salary, commission_pct,
to_char(round(salary+salary*nvl(commission_pct,0),-1)
,'999,999,999l') as "지급금액"
from employees;

-- 모든 사원의 job이력을 알고 싶다. 사번(employee_id), first_name, last_name, 
--job이력의 횟수, job이력이 한번도 없으면 ‘zero’, 한번 있으면 ‘one’, 
--두 번 있으면 ‘two’, 나머지는 ‘many’라고 등급을 정렬하여 출력하라.

select e.employee_id, e.first_name, e.last_name, 
count(h.start_date)  as "job이력의 횟수",
decode(count(h.start_date),0,'zero',1,'one',2,'two','many') as "등급" 
from employees e left outer join job_history h 
on e.employee_id = h.employee_id
group by e.employee_id, e.first_name, e.last_name
order by 4;

 

 

1. 사원의 first_name이 기억나지 않는다. 그 last_name에 'smith'라는 단어가 들어가는데 대문자,
소문자도 모른다. 그 사원의 사번(employee_id), first_name, last_name을 출력하라.

 

 

 

select employee_id, last_name,first_name from EMPLOYEES where lower(last_name) like '%smith%';

 

 

 

 

 

 

 

 

2.  고용일자(hire_date)가 오늘일자와 같은 달이고 고용일자의 요일이 월요일이나 
금요일인 사원에 대한 사번(employee_id), first_name, last_name, 고용일자를 최근 고용된 사원순으로 출력하라. 

 

 

 

select employee_id,first_name,last_name,hire_date from EMPLOYEES 
where to_char(sysdate,'mm')= to_char(hire_date,'mm') and 
(to_char(hire_date,'day') = '월요일' or to_char(hire_date,'day') = '금요일')
order by 4 desc;

 

 

 

 

3.  부서별로 사원의 수가 5명 이상인 부서의 부서번호(department_id)와 
부서명(department_name), 부서장사번(manager_id)을 인원수순으로 출력하라.

 

select d.department_id, d.department_name, d.manager_id, count(d.department_id) 
from EMPLOYEES e join DEPARTMENTS d on d.department_id = e.department_id 
group by d.department_id,d.department_name,d.manager_id having count(d.department_id) >= 5
order by 4;

 

 

 

 

 

 

4.  근속년수가 15년 이상인 사원에 대해서 부서별로 얼마나 많은 급여가 지급되는지 알고 싶다.
부서별 해당 사원이 3명 이상인 부서만 부서번호, 부서별 급여합계를 급여합계가 높은 순으로 출력하라.

 

 

select department_id as "부서번호",
sum(salary) as "부서별 급여합계"
from employees
where (sysdate-hire_date)/365 >= 15
group by department_id
having count(*) >= 3 order by 2 desc;

 

 

 

 

 

 

5. 사원별로 급여에 급여*commission비율을 더한 금액을 모든 사원에게 지급하려고 한다.  
사번(employee_id), 급여(salary), commission비율(commission_pct), 지급금액을 사번순으로 출력하라.
(단, nls_currency화폐단위가 뒤에 나오도록, 천단위가 콤마로 표시되고, 일의 자리에서 반올림하여라)

select employee_id, salary, commission_pct,
to_char(round(salary+salary*nvl(commission_pct,0),-1)
,'999,999,999l') as "지급금액"
from employees;

 

 

 

 

 

 

6. 모든 사원의 job이력을 알고 싶다. 사번(employee_id), first_name, last_name, 
 job이력의 횟수, job이력이 한번도 없으면 ‘zero’, 한번 있으면 ‘one’, 
 두 번 있으면 ‘two’, 나머지는 ‘many’라고 등급을 정렬하여 출력하라.

select e.employee_id, e.first_name, e.last_name, 
count(h.start_date)  as "job이력의 횟수",
decode(count(h.start_date),0,'zero',1,'one',2,'two','many') as "등급" 
from employees e left outer join job_history h 
on e.employee_id = h.employee_id
group by e.employee_id, e.first_name, e.last_name
order by 4;

 

 

 

 

 

 

-- 집합연산자(union, union all, intersect, minus)

 

7-1. 모든 사원의 현재 및 이전의 job 이력을 출력!

 

select employee_id, job_id from employees
union all
select employee_id, job_id
from job_history
order by 1,2;

 

 

 

 

 

7-2. 같은 job을 두번이상 하고 있는 사원의 사번과 job를 출력

 

 

select employee_id, job_id from employees
intersect
select employee_id, job_id
from job_history
order by 1,2;

 

 

 

7-3. 한번도 job 발령이 된적 없는 사원의 사번을 출력

 

 

select employee_id from employees
minus
select employee_id
from job_history
order by 1;

 

 

 

 

총 100 개의 사원 번호가 출력되면 된다.

 

 

8.  부서별로 ‘salary+salary*commission_pct’의 합계가 100000이상이면 
 ‘good’, 5000이상이면‘average’,1이상이면 ‘bad’, 나머지는 ‘ghost’ 
등급이 나오도록 부서번호(department_id), 총급여합계, 등급을 출력하라!

 

select department_id, sum(salary+salary* nvl(commission_pct,0)) as "총 급여 합계",
case
when sum(salary + salary*nvl(commission_pct,0)) >= 100000 then 'good'
when sum(salary + salary*nvl(commission_pct,0)) >= 5000 then 'average'
when sum(salary + salary*nvl(commission_pct,0)) >= 1 then 'bad'
else 'ghost'  end as "등급"
from employees
group by department_id;