Database Oracle SQL - 실습 4

select * from employees;

#1번 문제 
select max(salary) as "Maximum",
min(salary) as "Minimum",
sum(salary) as "Sum",
round(Avg(salary)) as "avg"
from employees;

#2번 문제 
select
JOB_ID,
max(salary) as "Maximum",
min(salary) as "Minimum",
sum(salary) as "Sum",
round(Avg(salary)) as "avg"
from employees
group by JOB_ID 
order by JOB_ID;

#3번 문제
select JOB_ID, count(JOB_ID) as "count"
from employees
group by JOB_ID;

#4번 문제
select SUM(count(JOB_ID)) as "Number of Manager" 
from employees
group by JOB_ID
having JOB_ID LIKE '%MAN%'
OR JOB_ID LIKE '%MGR%';

#5번 문제
select MAX(salary) - MIN(salary) as "DIFFERENCE"
from employees;

#6번 문제
select 
manager_id,
min(salary)
from employees
where manager_id IS NOT NULL 
AND salary > 6000
group by manager_id
order by min(salary) desc;

#7번 문제
select 
d.department_name,
d.location_id,
count(e.department_id) "number of people",
round(avg(e.salary),2) "salary"
from departments d ,employees e
where e.department_id = d.department_id
group by d.department_name, d.location_id ;

#8번 문제
SELECT 
COUNT(*) total, 
SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2005, 1, 0)) "2005", 
SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2006, 1, 0)) "2006",
SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2007, 1, 0)) "2007",
SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2008, 1, 0)) "2008"      
FROM employees;

#9번 문제
select JOB_ID,
sum(decode(DEPARTMENT_ID, 20, salary,0)) "DEPT 20",
sum(decode(DEPARTMENT_ID, 50, salary,0)) "DEPT 50",
sum(decode(DEPARTMENT_ID, 70, salary,0)) "DEPT 70",
sum(decode(DEPARTMENT_ID, 90, salary,0)) "DEPT 90",
sum(salary) "TOTAL"
from employees
group by JOB_ID
order by job_id;