[SQL 문제] 함수(FUNCTION) 통합 테스트

< 문제 (FUNCTION) >

--(1) 이름(first_name), 급여, 커미션 금액(salary*commission_pct), 총액(salary+커미션금액)을 구하여 총액이
--    많은 순서로 출력하라. 단, 커미션이 NULL인 사람은 제외한다.
--(2) 50번 부서의 모든 사람들에게 급여의 13%를 보너스로 지불하기로 하였다.
--    이름, 급여, 보너스 금액, 부서 번호를 출력하라.
--(3) 30번 부서의 연봉을 계산하여 이름, 부서번호, 급여, 연봉을 출력하라.
--    단, 연말에 급여의 150%를 보너스로 지급한다.
--(4) 부서 번호가 50인 부서의 시간당 임금을 계산하여 출력하라.
--    단, 1달의 근무일수는 12일이고, 1일 근무시간은 5시간이다.
--    출력양식은 이름, 급여, 시간당 임금(소수이하 1번째 자리에서 반올림)
--    을 출력하라.
--(5) 급여가 $5,000 이상인 사람은 급여의 15%를 회비로 지불하기로 하였다.
--    이를 이름, 급여, 회비(-2자리에서 반올림)를 출력하라.
--(6) 급여가 $5,000에서 $10,000 사이인 모든 사람은 급여의 15%를 경조비로 내기로 하였다.
--    이름, 급여, 경조비(소수점 이하 절삭)를 출력하라.

--(7) 입사일부터 지금까지의 근무일수를 출력하라.
--    부서 번호, 이름, 입사일, 현재일, 근무일수(소수점 이하 절삭),
-- 근무년수(반올림),근무월수(30일 기준,반올림), 근무주수(반올림)를 출력하라.

--(8) 모든 사원의 실수령액을 계산하여 출력하라.
--    단, 급여가 많은 순으로 이름, 급여, 실수령액을 출력하라
--    (실수령액은 급여에 대해 10%의 세금을 뺀 금액)

--(9) 입사일로부터 90일이 지난 후의 사원 이름, 입사일, 90일 후의 날, 급여를 출력하라.
--(10) 입사일로부터 6개월이 지난 후의 입사일, 6개월 후의 날짜, 급여를 출력하라.
--(11) 입사한 달의 근무일수를 계산하여 부서번호, 이름, 입사일, 근무일수를 출력하라.
--(12) 모든 사원의 60일이 지난 후의 월요일은 몇 년, 몇 월, 몇 일 인가를 구하여
--     이름, 입사일, 60일 후 월요일을 출력하라.
--(13) 입사일로부터 오늘까지의 일수를 구하여 이름, 입사일, 근무일수(반올림)를
--     출력하라.
--(14) 입사일을 '2001년 5월 14일'의 형태로 이름, 입사일을 출력하라.
--(15) 이름의 글자수가 6자 이상인 사람의 이름을 앞에서 3자만 구하여
--     소문자로 이름만을 출력하라.
--(16) 50번 부서 월급의 평균, 최고, 최저, 인원수를 구하여 출력하라.
--(17) 각 부서별 급여의 평균, 최고, 최저, 인원수를 구하여 출력하라.
--     (부서번호도 출력)
--(18) 각 부서별 같은 업무를 하는 사람의 인원수를 구하여 부서번호, 업무명, 인원수를 출력하라.
--(19) 같은 업무를 하는 사람의 수가 4명 이상인 업무와 인원수를 출력하라.
--(20) 각 부서별 평균 월급, 전체 월급, 최고 월급, 최저 월급을 구하여 평균 월급이
--     많은 순으로 출력하라. (부서번호도 출력)

 

--(1) 이름(first_name), 급여, 커미션 금액(salary*commission_pct), 총액(salary+커미션금액)을 구하여 총액이
--    많은 순서로 출력하라. 단, 커미션이 NULL인 사람은 제외한다.
select first_name 이름, salary 급여, 
salary*nvl(commission_pct,0) 커미션금액,
salary+salary*nvl(commission_pct,0) 총액
from employees
order by 4 desc;

--(2) 50번 부서의 모든 사람들에게 급여의 13%를 보너스로 지불하기로 하였다.
--    이름, 급여, 보너스 금액, 부서 번호를 출력하라.
select first_name 이름, salary 급여, 
salary*0.13 보너스, department_id 부서번호
from employees
where department_id = 50
order by salary desc;

--(3) 30번 부서의 연봉을 계산하여 이름, 부서번호, 급여, 연봉을 출력하라.
--    단, 연말에 급여의 150%를 보너스로 지급한다.
select first_name 이름, department_id 부서번호, 
salary 급여, salary*12+salary*1.5 연봉
from employees
where department_id = 30;

--(4) 부서 번호가 50인 부서의 시간당 임금을 계산하여 출력하라.
--    단, 1달의 근무일수는 12일이고, 1일 근무시간은 5시간이다.
--    출력양식은 이름, 급여, 시간당 임금(소수이하 1번째 자리에서 반올림)
--    을 출력하라.
select first_name 이름, salary 급여, 
round(salary/(12*5),1) 시간당임금
from employees
where department_id=50
order by 3 desc;

--(5) 급여가 $5,000 이상인 사람은 급여의 15%를 회비로 지불하기로 하였다.
--    이를 이름, 급여, 회비(-2자리에서 반올림)를 출력하라.
select first_name 이름, salary 급여,
round(salary*0.15,2) 회비
from employees
where salary>=5000;

--(6) 급여가 $5,000에서 $10,000 사이인 모든 사람은 급여의 15%를 경조비로 내기로 하였다.
--    이름, 급여, 경조비(소수점 이하 절삭)를 출력하라.
select first_name 이름, salary 급여,
trunc(salary*0.15,0) 경조비
from employees
where salary between 5000 and 10000
order by 3 desc;

--(7) 입사일부터 지금까지의 근무일수를 출력하라.
--    부서 번호, 이름, 입사일, 현재일, 근무일수(소수점 이하 절삭),
--	근무년수(반올림),근무월수(30일 기준,반올림), 근무주수(반올림)를 출력하라.
select 부서번호, 이름, 입사일, 현재일, 근무일수,
round(근무일수/365) 근무년수, round(근무일수/30) 근무월수,
round(근무일수/7) 근무주수
from (
  select department_id 부서번호, first_name 이름,
  hire_date 입사일, sysdate 현재일,
  trunc(sysdate-hire_date) 근무일수
  from employees
);

--(8) 모든 사원의 실수령액을 계산하여 출력하라.
--    단, 급여가 많은 순으로 이름, 급여, 실수령액을 출력하라
--    (실수령액은 급여에 대해 10%의 세금을 뺀 금액)
select first_name 이름, salary 급여, 
salary*0.9 실수령액
from employees
order by salary desc;

--(9) 입사일로부터 90일이 지난 후의 사원 이름, 입사일, 90일 후의 날, 급여를 출력하라.
select first_name 이름, hire_date 입사일, 
hire_date+90 "90일 후의 날", salary 급여
from employees;

--(10) 입사일로부터 6개월이 지난 후의 입사일, 6개월 후의 날짜, 급여를 출력하라.
select hire_date 입사일, add_months(hire_date,6) "6개월 후 입사일", salary 급여
from employees;

--(11) 입사일부터 입사한 달의 마지막날까지 근무일수를 계산하여 부서번호, 이름, 입사일, 근무일수를 출력하라.
select department_id 부서번호, first_name 이름, hire_date 입사일,
ceil(last_day(hire_date)-hire_date) 근무일수
from employees;

--(12) 모든 사원의 60일이 지난 후의 월요일은 몇 년, 몇 월, 몇 일 인가를 구하여
--     이름, 입사일, 60일 후 월요일을 출력하라.
select first_name 이름, hire_date 입사일,
next_day(hire_date+60,'월') "60일 후 월요일"
from employees;

--(13) 입사일로부터 오늘까지의 일수를 구하여 이름, 입사일, 근무일수(반올림)를
--     출력하라.
select department_id 부서번호, first_name 이름, hire_date 입사일,
round(sysdate-hire_date) 근무일수
from employees;

--(14) 입사일을 '2001년 5월 14일'의 형태로 이름, 입사일을 출력하라.
select first_name 이름, 
to_char(hire_date, 'fmyyyy"년" mm"웡" dd"일"') 입사일
from employees;

--(15) 이름의 글자수가 6자 이상인 사람의 이름을 앞에서 3자만 구하여
--     소문자로 이름만을 출력하라.
select lower(substr(first_name,1,3)) 이름
from employees
where length(first_name) >= 6;

--(16) 50번 부서 월급의 평균, 최고, 최저, 인원수를 구하여 출력하라.
select round(avg(salary),2) 평균월급, max(salary) 최고월급, 
min(salary) 최저월급, count(department_id) 인원수
from employees
group by department_id having department_id=50;

--(17) 각 부서별 급여의 평균, 최고, 최저, 인원수를 구하여 출력하라.
--     (부서번호도 출력)
select department_id 부서번호, round(avg(salary),2) 평균급여, 
max(salary) 최고급여,min(salary) 최저급여, count(department_id)
from employees
group by department_id
order by department_id;

--(18) 각 부서별 같은 업무를 하는 사람의 인원수를 구하여 부서번호, 업무명, 인원수를 출력하라.
select department_id 부서번호, job_id 업무명, count(job_id) 인원수
from employees
group by department_id, job_id
order by department_id;

--(19) 같은 업무를 하는 사람의 수가 4명 이상인 업무와 인원수를 출력하라.
select *
from (
  select job_id 업무명, count(job_id) 인원수
  from employees
  group by job_id
)
where 인원수 >= 4;

--(20) 각 부서별 평균 월급, 전체 월급, 최고 월급, 최저 월급을 구하여 평균 월급이
--     많은 순으로 출력하라. (부서번호도 출력)
select * 
from (
  select department_id 부서번호,
  round(avg(salary),2) 평균월급, sum(salary) 전체월급,
  max(salary) 최고월급, min(salary) 최저월급
  from employees
  group by department_id
)
order by 평균월급 desc;