9일차 문제(subquery)

 문제) 입사가 가장 빠른 5명?

    select *

    from (select *

          from emp

          order by hiredate)

    where rownum <= 5;

  문제) 입사가 가장 빠른 5명 이후의 입사자들

    select *

    from (select *

          from emp

          order by hiredate)

    where rownum > 5;

                  --> 엉터리

    select rownum, e.*

    from emp e

    order by hiredate;

↓ ↓ ↓ 

    select rownum, e.*

    from (select *

  from emp

  order by hiredate) e;

↓ ↓ ↓ 

    select *

    from (select rownum as no, e.*

          from (select *

        from emp

        order by hiredate) e

         )

    where no > 5;

  문제) employees 테이블의 부서 중에서 평균 급여가 가장 적은 부서는?

    방법 1) subquery + having

  select min(avg(salary))

  from employees

  group by department_id;

      ↓ ↓

  

  select department_id, avg(salary)

  from employees

  group by department_id

  having avg(salary) = (select min(avg(salary))

from employees

group by department_id);

    방법 2) subquery + rownum

  select *

  from (select department_id, avg(salary)

   from employees

   group by department_id

   order by 2)

  where rownum = 1;