[5일차 과제]서브쿼리

## 서브쿼리 실습문제  

이름 : 조은수

EX1. Chen와 동일한 부서에 속한 모든 사원의 이름과 입사일을 표시하는 질의를 작성하고 
       Chen 본인은 제외 시키시오.
select last_name,hire_date
from EMP
where department_id=(select department_id
from emp
where last_name='Chen')
AND LAST_NAME!='Chen';


EX2. 자신의 급여가 평균 급여보다 많은 모든 사원의 사원 번호, 이름, 급여를 표시하는 질의를 작성하고 
      급여를 기준으로 내림차순으로 정렬합니다. 
select employee_id,last_name,salary
from emp
where salary> (select avg(salary)
from emp)
order by salary desc;

EX3. 이름에 T 가 들어가는 사원이 속한 부서에서 근무하는 
       모든 사원의 사원 번호와 이름을 표시하는 질의를 작성하시오.
select employee_id,last_name
from emp
where department_id in (select department_id from emp where LAST_NAME like ('T%') OR LAST_NAME LIKE ('%t%'));
===============
select distinct(department_id)

EX4. 부서 위치한 도시가  'Seattle' 인 모든 사원의 이름, 부서 번호 및 직무를 표시합니다. 
select last_name, department_id,job_id
from emp, locations
where city in (select city from locations where city like ('Seattle'));
====================
select Last_name, employee_id,job_id
from emp
where department_id in (select department_id
from dept
where location_id in (select location_id
  form locations
 where city in "seattle"));

EX5. Kochhar 에게 보고하는 모든 사원의 이름과 급여를 표시합니다.
select last_name, salary
from emp
where manager_id = (select manager_id from emp where last_name like ('Kochhar'));
==========
select employee_id

EX6. Sales 부서의 모든 사원에 대한 부서 번호, 이름 직무를 표시하시오.//
select department_id, last_name, job_id
from emp
where department_id = (select department_id from dept where department_name = 'Sales')); 

EX7. 자신의 급여가 평균 급여보다 많으면서 이름에 T가 들어가는 사원과 
       동일한 부서에 근무하는 모든 사원의 사원 번호, 이름 및 급여를 표시하도록 하시오. 
select employee_id,last_name,salary
from emp
where salary> (select avg(salary)
from emp)
AND department_id in (select department_id from emp where LAST_NAME like ('T%') OR LAST_NAME LIKE ('%t%'))
order by salary desc;



EX8. Fox 와 같은 커미션을 받는 사원 중에  
       Fox 급여보다 많이 받는 모든 사원의 이름, 입사일 및 급여를 표시하는 질의를 작성하시오.
select last_name,hire_date,salary
from emp
where COMMISSION_PCT= (select COMMISSION_PCT
from emp
where LAST_NAME='Fox')
AND salary > (select salary
from emp
where LAST_NAME='Fox');

EX9. 사무원 직군의 최고 급여보다 많이 받는 사원을 표시하는 질의를 작성하고 
       결과를 최고 급여에서 최저 급여의 순으로 정렬하시오.
select last_name,salary
from emp 
where salary> (select department_name from dept where depart_name='clerk'
order by salary desc;