Oracle 기본정리 - Join 응용편(cross, equi, left, right inner join)

Oracle 기본정리 - Join 응용편(cross, equi, left, right inner join)

--hr계정의 테이블 사용

***Select문 실행순서***

--select      --5번
--from        --1번
--where       --2번
--group by    --3번
--having      --4번
--order by    --6번

--성과급을 받는 사원의 사원이름, 급여,성과급을
--급여많이 받는 순으로, 같은 급여를 받을 시 성과급을 많이 받는 사원순으로 출력하시오

select last_name || ' ' || first_name as name, salary, salary*(1+nvl(commission_pct,0)) as "성과급" , commission_pct
from employees
--where commission_pct is not null 써도됨
order by salary desc, commission_pct desc;

--사원번호와 이름, 급여, 부서명, 직책명을 출력하시오

select e.employee_id, e.first_name, d.department_name,j.job_title
from employees e, jobs j, departments d
where e.job_id = j.job_id
AND e.department_id= d.department_id;

--사원번호, 이름, 급여, 부서명, 근무하는 도시와, 국가를 출력

select e.employee_id, e.first_name, d.department_name, l.city, c.country_name
from employees e, departments d, locations l, countries c
where e.department_id= d.department_id
and d.location_id = l.location_id
and l.country_id = c.country_id;

--inner join

--inner join으로 여러개 테이블 합치기

select first_name, department_name,job_title
from employees inner join departments
on employees.department_id = departments.department_id
inner join jobs
on employees.job_id = jobs.job_id;

--문제
--부서위치코드가 1700인 곳에서 근무하는 사원의 성,부서명,직무명을 출력

--일반

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 d.location_id = 1700;

--inner버전

select e.first_name, d.department_name, j.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;

--Steven king 에게 보고하는 사원의 사원코드, 성, 급여를 출력

select e1.employee_id, e1.first_name, e1.salary
from employees e1, employees e2
where e1.manager_id = e2.employee_id
and lower(e2.first_name) = 'steven'
and lower(e2.last_name) = 'king';

--Executive 부서의 사원번호, 성, 부서코드, 직무코드를 출력

방법1

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(d.department_name)='executive';

방법2

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 lower(d.department_name) = 'executive';

--부서별로 평균 급여를 출력

select department_id, trunc(avg(salary))
from employees
group by department_id;

--부서이름별로 평균 급여를 출력

select department_name, avg(salary)
from employees e inner join departments
on e.department_id = d.department_id
group by department_name;

--입사년도별 부서별 평균 급여를 출력

select to_char(hire_date,'yyyy') as "입사년도" , department_id as "부서코드" ,trunc(avg(salary)) as "평균급여"
from employees
group by to_char(hire_date,'yyyy'),department_id
order by to_char(hire_date,'yyyy') asc;

--우리회사의 총인원 및 입사년도별 인원을 출력

--totalcount 2004 2005 2006 2007

select count(*) as totalcount
    , count(decode(to_char(hire_date,'yyyy'),'2004',1))as d04
    , count(decode(to_char(hire_date,'yyyy'),'2005',1))as d05
    , count(decode(to_char(hire_date,'yyyy'),'2006',1))as d06
    , count(decode(to_char(hire_date,'yyyy'),'2007',1))as d07
from employees;

--각 관리자보다 먼저 채용된 사원의 사원이름, 입사일, 관리자이름, 관리자의 입사일 출력

select e1.first_name || ' ' || e1.last_name as "사원이름" , e1.hire_date as "입사일", e2.first_name || ' ' || e2.last_name as "관리자 이름", e2.hire_date as "관리자의 입사일"
from employees e1 inner join employees e2
on e1.manager_id = e2.employee_id
where e1.hire_date < e2.hire_date
order by e1.first_name;

--각 부서별 평균 급여가 6000을 초과한 부서이름과 평균 급여 출력

select d.department_name, round(avg(salary)) as "평균급여"
from employees e inner join departments d
on e.department_id = d.department_id
group by e.department_id, d.department_name
having avg(salary) > 6000;