SQL 문제 및 문제풀이 (~JOIN)

--(1)부서위치 코드가 1700인 곳에서 근무하는 사원의 성, 부서명, 직무명을 출력하시오

--(2)Steven King에게 보고하는 사원의 사원코드, 성, 급여를 출력하시오

--(3)Executive 부서의 사원번호, 성, 부서코드, 직무코드를 출력하시오

--(4)부서별로 평균급여를 출력하시오

--(5)입사년도별 부서별 평균 급여를 출력하시오

--(6)우리회사의 총인원및 입사년도별 인원을 출력하시오

--(7) 각 관리자보다 먼저 채용된 사원의 사원이름, 입사일, 관리자이름, 관리자의 입사일을 출력하시오

--(8) 각 부서별 평균급여가 6000을 초과한 부서이름과 평균 급여를 출력하시오

 

 

 

 

--(1)부서위치 코드가 1700인 곳에서 근무하는 사원의 성, 부서명, 직무명을 출력하시오
--1번풀이
select first_name, department_name, job_title
from employees e, departments d, jobs j
where e.department_id = d.department_id
    and e.job_id = j.job_id
    and location_id = 1700;
 
--2번풀이
select first_name, department_name, job_title
from employees e inner join departments d
on e.department_id = d.department_id
    inner join jobs j
    on e.job_id = j.job_id
where d.location_id=1700;
 
 
--(2)Steven King에게 보고하는 사원의 사원코드, 성, 급여를 출력하시오
--확인용
select employee_id, first_name
from employees
where employee_id = 100;
 
--셀프조인 쓰는 방법
select e1.employee_id, e1.last_name, e1.salary
from employees e1, employees e2
where e1.manager_id = e2.employee_id
    and e2.first_name = 'Steven'
        and lower(e2.last_name) = 'king';
 
--inner join 쓰는방법
select e1.employee_id, e1.last_name, e1.salary
from employees e1 inner join employees e2
    on e1.manager_id = e2.employee_id
where e2.first_name = 'Steven'
    and lower(e2.last_name) = 'king';
 
 
--(3)Executive 부서의 사원번호, 성, 부서코드, 직무코드를 출력하시오
select e.employee_id, e.last_name, e.department_id, e.job_id
from employees e, departments d
where e.department_id = d.department_id
    and lower(department_name) = 'executive';
    
select e.employee_id, e.last_name, e.department_id, e.job_id
from employees e inner join departments d
    on e.department_id = d.department_id
    where department_name = 'Executive';
 
 
--(4)부서별로 평균급여를 출력하시오
select nvl(department_id, 0), round(avg(salary))
from employees
group by department_id;
 
select d.department_name ,round(avg(salary)) as "부서별평균급여"
from employees e, departments d
where e.department_id = d.department_id
group by department_name;
 
--inner join 사용가능
select d.department_name ,round(avg(salary)) as "부서별평균급여"
from employees e inner join departments d
on e.department_id = d.department_id
group by department_name;
 
 
--(5)입사년도별 부서별 평균 급여를 출력하시오
select distinct to_char(hire_date,'yyyy')
from employees
order by to_char(hire_date,'yyyy');
 
select to_char(hire_date,'yyyy') as "hire_year", department_id, avg(salary) as avgsal
 
from employees
 
group by to_char(hire_date,'yyyy'), department_id
 
order by "hire_year", department_id;
 
 
 
--(6)우리회사의 총인원및 입사년도별 인원을 출력하시오.
select count(first_name),
count(decode(to_char(hire_date,'yyyy'), 2001, 1)) as "2001",
count(decode(to_char(hire_date,'yyyy'), 2002, 1)) as "2002",
count(decode(to_char(hire_date,'yyyy'), 2003, 1)) as "2003",
count(decode(to_char(hire_date,'yyyy'), 2004, 1)) as "2004",
count(decode(to_char(hire_date,'yyyy'), 2005, 1)) as "2005",
count(decode(to_char(hire_date,'yyyy'), 2006, 1)) as "2006",
count(decode(to_char(hire_date,'yyyy'), 2007, 1)) as "2007",
count(decode(to_char(hire_date,'yyyy'), 2008, 1)) as "2008"
from employees;
 
 
--(7) 각 관리자보다 먼저 채용된 사원의 사원이름, 입사일, 관리자이름, 관리자의 입사일을 출력하시오
select distinct manager_id from employees;
select * from employees;
 
select e1.last_name, e1.hire_date, e2.last_name, e2.hire_date
from employees e1, employees e2
where e1.manager_id = e2.employee_id
    and e1.hire_date < e2.hire_date
    order by e1.hire_date;
    
 
--(8) 각 부서별 평균급여가 6000을 초과한 부서이름과 평균 급여를 출력하시오.
select department_id, round(avg(salary))
from employees
group by department_id
having avg(salary) < 6000;