Oracle DB ( join 실습문제 )

--실습1 급여가 2000초과인 사원들의 부서정보 , 사원 정보를 아래와 같이 출력
select d.deptno , d.dname , e.empno , e.ename , e.sal
from dept d join emp e  on d.deptno = e.deptno and  e.sal > 2000;

select d.deptno , d.dname , e.empno , e.ename , e.sal
from dept d  ,  emp e 
where d.deptno = e.deptno and  e.sal > 2000;

--실습2 각 부서별 평균 급여 , 최대 급여 , 최소 급여 , 사원수를 출력
select d.deptno, d.dname,  round(avg(sal)) as avgsal , max(sal) as maxsal, min(sal)  as minsal ,count(*) as cnt
from dept d  ,  emp e 
where d.deptno = e.deptno 
group by d.deptno,  d.dname;

select d.deptno, d.dname,  round(avg(sal)) as avgsal , max(sal) as maxsal, min(sal)  as minsal ,count(*) as cnt
from dept d  join  emp e on  d.deptno = e.deptno 
group by d.deptno,  d.dname;

--실습3 모든 부서정보와 사원정보를 아래와 같이 부서번호 , 사원이름 순으로 정렬
select d.deptno , d.dname , e.empno, e.ename , job , sal
from dept d full outer join emp e on d.deptno = e.deptno order by d.deptno , e.ename ;

select d.deptno , d.dname , e.empno, e.ename , job , sal
from dept d , emp e 
where d.deptno = e.deptno order by d.deptno , e.ename ;

-- join
--자신의 담당 매니저의 고용일보다 빠른 입사자 찾기 (self join - empoyees )
select e1.hire_date , e1.last_name , e1.manager_id
from employees e1 , employees e2
where e1.manager_id  = e2.employee_id and e1.hire_date < e2.hire_date;


--도시 이름이 T로 시작하는 지역에 사는 사원들의 사번 , last_name , 부서번호 , 도시 조회
--(employees , departments , locations) inner join
select e.employee_id , e.last_name , e.department_id  , l.city
from employees e , locations l , departments d
where e.department_id = d.department_id
and d.location_id = l.location_id and l.city  like 'T%';

--위치 id가 1700인 사원들의 사번 last_name 부서번호 급여 조회
--employees , departments , locations inner join
SELECT e.employee_id, e.last_name, d.department_id, e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id
WHERE l.location_id = 1700;


-- 부서명 , 위치id , 각 부서별 사원 총 수, 각 부서별 평균 연봉 조회
--평균 연봉은 소수점 2자리 까지만
--employees , departments, inner join
SELECT d.department_name, d.location_id, COUNT(e.employee_id) AS total_employees, 
ROUND(AVG(e.salary), 2) AS avg_salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name, d.location_id;

--executive 부서에 근무하는 사원들의 부서번호 last_name job_id 조회
--employees , departments inner join
select e.department_id , e.last_name , e.job_id
from employees e , departments d 
where e.department_id = d.department_id and d.department_name = 'Executive' ;

--각 사원별 소속부서에서 자신보다 늦게 고용되었으나 보다 많은 연봉을 받는 사원이 존재하는 모든 사원들의
--부서번호 , 이름 (first_name , last_name 연결) salary , hire_date 조회
-- employees self join

select e1.department_id , e1.first_name ||  e1.last_name as name , e2.salary , e2.hire_date
from employees e1 , employees e2
where e1.department_id =  e2.department_id and e1.hire_date < e2.hire_date and e1.salary < e2.salary;