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
방법2select 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';
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;
'프로그래밍 정리 > Oracle' 카테고리의 다른 글
Oracle 기본정리 - sub query2(where절에 select 넣어보기) (0) | 2019.12.04 |
---|---|
Oracle 기본정리 - sub query1(where절에 select 넣어보기) (0) | 2019.12.04 |
Oracle 기본정리 - Join 기본편(cross, equi, left, right join) (0) | 2019.11.29 |
Oracle 기본정리 - scott계정 테이블사용하기(emp,dept....) (0) | 2019.11.29 |
Oracle 기본정리 - Group By 응용편 (0) | 2019.11.29 |