Oracle DB ( 서브쿼리 실습문제 )

--서브쿼리  실습문제
--last_name 에 u가 포함된 사원들과 동일 부서에 근무하는 사원들의 사번 , last_name 조회
SELECT e.employee_id, e.last_name
FROM employees e
WHERE e.department_id IN (
  SELECT d.department_id
  FROM employees e2
  INNER JOIN departments d ON e2.department_id = d.department_id
  WHERE e2.last_name LIKE '%u%'
)
AND e.last_name NOT LIKE '%u%';


-- job_id 가  SA_MAN 인 사원들의 최대 연봉보다 높게 받는 사원들의 last_name job_id, salary 조회
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = 'SA_MAN'
AND salary > (
  SELECT MAX(salary)
  FROM employees);


-- 커미션을 받는 사원들의 부서와 연봉이 동일한 사원들의 last_name , department_id , salary 조회
SELECT e.last_name, e.department_id, e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE e.salary IN (
  SELECT e2.salary
  FROM employees e2
  WHERE e2.commission_pct IS NOT NULL
)
AND d.department_id IN (
  SELECT d2.department_id
  FROM employees e3
  INNER JOIN departments d2 ON e3.department_id = d2.department_id
  WHERE e3.commission_pct IS NOT NULL
);


--회사 전체 평균 연봉보다 더 받는 사원들 중 last_name 에 u 가 있는 사원들이 근무하는 부서에서 
--근무하는 사원들의 employee_id , last_name , salary 조회
SELECT e.employee_id, e.last_name, e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
)
AND d.department_id IN (
  SELECT d2.department_id
  
  --실습 1 10번 부서에 근무하는 사원 중 30번 부서에는 존재하지 않는 직책을 가진 사원들의 정보 , 부서정보를 다음과같이 출력
select e.deptno , e.ename , e.job , d.deptno , d.dname , d.loc
from emp e , dept d
where e.deptno = d.deptno and e.job 
not in   (select job from emp where deptno=30) 
and e.deptno=10;

--실습 2 직책이 SLAESMAN인  사람들의 최고 급여보다 높은 급여를 받는 사원들의 사원정보 급여등급 정보를 출력
select e.empno,e.deptno , e.ename , e.job
from emp e , salgrade s
where e.sal between s.losal and hisal and e.sal > (select max(sal) from emp where job = 'SALESMAN' )
order by e.empno;
  FROM employees e2
  INNER JOIN departments d2 ON e2.department_id = d2.department_id
  WHERE e2.last_name LIKE '%u%'
);


-- last_name 이 Davies 인 사람보다 나중에 고용된 사원들의 last_name , hire_date 조회
SELECT last_name, hire_date
FROM employees
WHERE hire_date > (
  SELECT hire_date
  FROM employees
  WHERE last_name = 'Davies'
);


-- last_name 이 King 인 사원을 매니저로 두고있는 모든 사원들이 last_name , salary 조회
SELECT e.last_name, e.salary
FROM employees e
INNER JOIN employees e1 ON e.manager_id = e1.employee_id
WHERE e1.last_name = 'King';

--실습 1 10번 부서에 근무하는 사원 중 30번 부서에는 존재하지 않는 직책을 가진 사원들의 정보 , 부서정보를 다음과같이 출력
select e.deptno , e.ename , e.job , d.deptno , d.dname , d.loc
from emp e , dept d
where e.deptno = d.deptno and e.job 
not in   (select job from emp where deptno=30) 
and e.deptno=10;

--실습 2 직책이 SLAESMAN인  사람들의 최고 급여보다 높은 급여를 받는 사원들의 사원정보 급여등급 정보를 출력
select e.empno,e.deptno , e.ename , e.job
from emp e , salgrade s
where e.sal between s.losal and hisal and e.sal > (select max(sal) from emp where job = 'SALESMAN' )
order by e.empno;