14일차 # 6-12: Select Case

문제 : 소속 부서의 평균 급여보다 많은 급여를 받는 사원들을 보여다오.

    i) Join

select *

from emp e,

    (select deptno, avg(sal) as avg_sal

     from emp

     group by deptno) d

where e.deptno = d.deptno

and e.sal > d.avg_sal;

    ii) Correlated Subquery

select * 

from emp e

where sal > (select avg(sal) from emp

            where deptno = e.deptno);

    iii) PL/SQL Subprogram

create or replace function uf_avg_sal(a number) return number

is

 v_ret number;

begin

 select avg(sal) into v_ret

 from emp

 where deptno = a;

 return v_ret;

end;

select * from emp e

where sal > uf_avg_sal(e.deptno);

  문제 : 직속부하직원이 3명 이상인 사원들의 모든 정보를 보여다오.

   *) 사원번호가 7566인 사원의 직속부하직원 수는? --> 서브쿼리로 사용할 것입니다.

select count(*)

from emp

where empno = 7566;

 COUNT(*)

----------

        1 --> 엉터리

↓ ↓ ↓ 

select count(*)

from emp

where mgr = 7566;

 COUNT(*)

----------

        2 --> 제대로

  ---------------------------------------

↓ ↓ ↓ 

select *

from emp o

where 2 < (select count(*)

  from emp i

  where i.mgr = o.empno);

  * JOIN 이용

select e1.empno, e1.ename, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno

from emp e1 join emp e2

   on (e1.empno = e2.mgr)

group by e1.empno, e1.ename, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno

having count(e1.empno) > 2;

==> 단순 무식한 방법

↓ ↓ ↓ 

select *

from emp e1 join (select mgr, count(*) as num

 from emp

 group by mgr) e2

   on (e1.empno = e2.mgr)

where e2.num > 2;

  문제 : 근무하는 직원이 4명 이상인 부서의 모든 정보를 알려다오.

select *

from dept

where 3 < (select count(*)

  from emp

  where dept.deptno = deptno);