[Database] 9. 서브 쿼리

-- 서브쿼리 (select안에 select 사용 방법)

-- 서브 쿼리 종류 3가지

-- DALLAS에 사는 직원을 출력해주세요
select *
from emp
where deptno = 20;

select deptno
from dept
where loc = 'dallas';

select *
from emp
where deptno = (select deptno
from dept
where loc = 'dallas');

-- research부서의 직원 출력
select *
from dept;

select deptno
from dept
where deptno = 20;

select *
from emp
where deptno = (select deptno
from dept
where deptno = 20);

select * 
from (
	select ename, sal*12 '연봉'
	from emp
) e
where e.연봉 = 9600;

select *
from
(
select avg(sal) 'avg_sal'
from emp
group by deptno
) e
where e.avg_sal < 2000;

select *
from dept;

select *
from emp
order by deptno;

select count(*)
from emp
where deptno = 10;

select d.deptno,d.dname,d.loc, (select count(*)
from emp
where deptno = d.deptno) '직원수'
from dept d;