SUB QUERY

 Sub Query 중요. 굉장히 편리하며, 잘하는 사람은 DB잘하는 사람
나머지 select 문을 이야기함.

select last_name, department_name,(select last_name from employees where employee_id = e.manager_id) "MGR_NAME"
from employees e JOIN departments d
                 ON   e.department_id = d.department_id
where department_name = 'Executive'
// JOIN으로 employees 2개 department 1개 사용했던 것을,
// SubQuery를 사용함으로써, JOIN 2개 사용.

select last_name, salary from employees
where salary > (select avg(salary) from employees)
// (select avg(salary) from employees)를 select문에서 실행했다면, 평균값만 나오지만.
// salary가 평균값 이상(salary > (select avg(salary) from employees))인 사람을 구한다.

select e.empno, e.name, e.pay, d.deptno, d.dname
from emp2 e, dept2 d
where e.deptno = d.deptno
and   e.deptno = 10
and   e.pay > (select avg(pay) from emp2)
// Oracle JOIN
select e.empno, e.name, e.pay, d.deptno, d.dname
from emp2 e JOIN dept2 d
ON e.deptno = d.deptno
where e.deptno = 10
and e.pay > (select avg(pay) from emp2)
// ANSI JOIN

select * from emp2
where pay in (select max(pay) from emp2 group by deptno)
// deptno 별로 max(pay)를 보여라.
// where 문에서 SubQuery를 사용하기 위해 in을 사용

select * from emp2
where pay in (select max(pay) from emp2 group by deptno)
and birthday <= '73/01/01'
// 생일이 73년도 이전인 사람들 (birthday column)

select * from emp2
where birthday in (select birthday from emp2 where birthday <='73/01/01')

select * from emp2
where birthday <= (select birthday from emp2 where birthday LIKE '81%')
// 81년도 이전 생일 나오랏

select e.empno, e.name, e.deptno, d.dname, d.area
from (select * from emp2 where deptno = 1000)e,
     (select * from dept2)d
where e.deptno = d.deptno
// (select * from emp2 where deptno = 1000) 이것을  e로 지정
// (select * from dept2) 이것을 d로 지정
// 기존에 table을 지정하는 것이 아닌, table의 select 값을 alios 함.
select e.empno, e.name, e.deptno, d.dname, d.area
from (select * from emp2 where deptno =1000) e JOIN (select * from dept2) d
ON e.deptno = d.deptno
// 동일한 값 출력

 job_id 별 최대 급여자를 나타내라

select employee_id, last_name, job_id, salary from employees
where salary = (select max(salary) from employees group by job_id)
// 반환값이 여러개라 판단하여 오류. 
// 그래서 where salary = (select max(salary) 의 =를 in으로 바꾸어 사용

select employee_id, last_name, job_id, salary from employees
where (job_id, salary) IN (select job_id, max(salary) from employees group by job_id)
// salary 에 중복된 값이 있기에 하나의 값만 나오게 기준을 정해줌.
158 McEwen SA_REP 9000
152 Hall   SA_REP 9000
// 이런식으로 같은 job_id 에 같은 최대 급여자가 나옴.

select distinct last_name, job_id, salary
from employees
where salary in (select max(salary) from employees group by job_id)
order by salary, last_name