subquery

where절에 그룹함수로 조건을 주고 싶으나 where절에 그룹함수를 사용하지 못한다.

이럴경우에 subquery를 사용하면 유효하다.

select sawon_name

from sawon

where sawon_pay > (select avg(sawon_pay) from sawon)

SAWON, BUSER, GOGEK 테이블을 사용한다.

문1) 2000년 이후에 입사한 남자 중 급여를 가장 많이 받는 직원은?

select sawon_name

from sawon

where sawon_pay= (select max(sawon_pay) from sawon where to_char(sawon_ibsail,'YYYY') >= 2000 and sawon_gen='남')

 

문2)  평균급여보다 급여를 많이 받는 직원은?

select sawon_name

from sawon

where sawon_pay > (select avg(sawon_pay) from sawon)

 

문3) '한국남' 직원의 입사 이후에 입사한 직원은?

select sawon_name

from sawon

where sawon_ibsail > (select sawon_ibsail from sawon where sawon_name='한국남')

 

문4) 직급이 과장인 사람 중에서 급여를 가장 많이 받는 사람은?

select sawon_name

from sawon

where sawon_jik='과장' and sawon_pay=(select max(sawon_pay) from sawon where sawon_jik='과장')

 

문5) 2000 ~ 2005 사이에 입사한 총무부,영업부,전산부 직원 중 급여가 가장 적은 사람은?

       (직급이 NULL인 자료는 작업에서 제외)

select sawon_name

from sawon

where  sawon_pay = (

  select min(sawon_pay) from ((select sawon_name,sawon_pay

  from (

    select sawon_name,buser_num,sawon_pay

    from sawon

    where to_char(sawon_ibsail,'YYYY') >= 2000 and to_char(sawon_ibsail,'YYYY') <= 2005)

  where buser_num in (select buser_no from buser where buser_name in ('총무부','영업부','전산부')))))

 

문6) 이순라, 이순신과 직급이 같은 사람은 누구인가?

select sawon_name, sawon_jik

from sawon

where sawon_jik in (select sawon_jik from sawon where sawon_name in ('이순라','이순신'))

문7) 과장 중에서 최대급여, 최소급여를 받는 사람은?

select sawon_name, sawon_pay

from (select sawon_name, sawon_pay, max(sawon_pay) 최대급여, min(sawon_pay) 최소급여 from sawon where sawon_jik='과장' group by sawon_name, sawon_pay)

where sawon_pay in (최대급여,최소급여)

 

문8) 20번 부서의 최소급여보다 많은 사람은?

select sawon_name

from sawon

where sawon_pay>(select min(sawon_pay) from sawon where buser_num=20)

 

문9) 30번 부서의 평균급여보다 급여가 많은 '대리' 는 몇명인가?

select count(sawon_name)

from sawon

where sawon_pay>(select avg(sawon_pay) from sawon where buser_num=30) and sawon_jik='대리'

 

문10) 고객을 확보하고 있는 직원들의 이름, 직급, 부서명을 입사일 별로 출력하라.

select sawon_name,sawon_jik, sawon_ibsail, buser_name, sawon_ibsail

from sawon, (select buser_no,buser_name from buser)

where buser_num=buser_no(+) and sawon_no in (select gogek_damsano from gogek) order by sawon.sawon_ibsail

 

문11) 이순신과 같은 부서에 근무하는 직원과 해당 직원이 관리하는 고객 출력

(고객은 나이가 30 이하면 '청년', 40 이하면 '중년', 그 외는 '노년'으로 표시하고, 고객 연장자 부터 출력)

출력 ==>  직원명    부서명     부서전화     직급      고객명    고객전화    고객구분

               한송이    총무부   123-1111     사원      백송이    333-3333       청년   

select sawon_name, buser_name, buser_tel, sawon_jik, gogek_name, gogek_tel

,case

when to_char(sysdate,'YYYY')-(substr(gogek_jumin,1,2)+1900) >= 40 then '노년'

when to_char(sysdate,'YYYY')-(substr(gogek_jumin,1,2)+1900) >= 30 then '중년'

else '청년' end as 구분 

from sawon, (select * from buser), (select * from gogek)

where buser_num=buser_no and sawon_no=gogek_damsano