[펌][Oracle DB ◈] 고난도 문제 연습

http://blog.daum.net/gunsu0j/17

[고난도 문제 연습]

-- 1. EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력하라.

select deptno, count(*), sum(sal)

from emp

group by deptno

having count(*)>4

-- 2. EMP 테이블에서 가장 많은 사원이 속해있는 부서번호와 사원수를 출력하라.

select deptno, count(*)

from emp

group by deptno

having count(deptno) =

(select max(count(*))

from emp

group by deptno)

-- 3. EMP 테이블에서 가장 많은 사원을 갖는 MGR의 사원번호를 출력하라.

select mgr empno

from emp

group by mgr

having count(mgr) =

(select max(count(*))

from emp

group by mgr)

-- 4. EMP 테이블에서 부서번호가 10인 사원수와 부서번호가 30인 사원수를 각각 출력하라.

select

count(decode(deptno, 10, 1)) CNT10,

count(decode(deptno, 30, 1)) CNT20

from emp

-- 5. EMP 테이블에서 사원번호가 7521인 사원의 직업과 같고 사원번호가 7934인 사원의 급여(SAL)보다 많은 사원의 사원번호, 이름, 직업, 급여를 출력하라.

select empno, ename, job, sal

from emp

where job =

(select job from emp

where empno = 7521)

and sal >

(select sal from emp

where empno = 7934)

-- 6. 직업(JOB)별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 부서명을 출력하라.

-- 조건1 : 직업별로 내림차순 정렬

select e.empno, e.ename, e.job, d.dname

from emp e, dept d

where e.deptno = d.deptno

and sal IN

(select min(sal)

from emp

group by job)

order by job desc

-- 7. 각 사원 별 시급을 계산하여 부서번호, 사원이름, 시급을 출력하라.

-- 조건1. 한달 근무일수는 20일, 하루 근무시간은 8시간이다.

-- 조건2. 시급은 소수 두 번째 자리에서 반올림한다.

-- 조건3. 부서별로 오름차순 정렬

-- 조건4. 시급이 많은 순으로 출력

select deptno, ename, round((sal/20/8),1) 시급

from emp

order by deptno, round((sal/20/8),1) desc

-- 8. 각 사원 별 커미션이 0 또는 NULL이고 부서위치가 ‘GO’로 끝나는 사원의 정보를 사원번호, 사원이름, 커미션, 부서번호, 부서명, 부서위치를 출력하라.

-- 조건1. 보너스가 NULL이면 0으로 출력

select

e.empno, e.ename, decode(e.comm,NULL, 'NULL',0) COMM,

e.deptno, d.dname, d.loc 

from emp e, dept d

where e.deptno = d.deptno

and

(e.comm = 0 OR e.comm IS NULL)

and d.loc like '%GO'

-- 9. 각 부서 별 평균 급여가 2000 이상이면 초과, 그렇지 않으면 미만을 출력하라.

select deptno, (case WHEN (avg(sal)>2000) THEN '초과' ELSE '미만' END) 평균급여

from emp

group by deptno

order by deptno

-- 10. 각 부서 별 입사일이 가장 오래된 사원을 한 명씩 선별해 사원번호, 사원명, 부서번호, 입사일을 출력하라.

select empno, ename, deptno, hiredate

from emp

where hiredate IN(

select min(hiredate)

from emp

group by deptno)

-- 11. 1980년~1980년 사이에 입사된 각 부서별 사원수를 부서번호, 부서명, 입사1980, 입사1981, 입사1982로 출력하라.

select

d.deptno, d.dname,

count(decode(to_char(e.hiredate, 'YYYY'), '1980', 1)) 입사1980,

count(decode(to_char(e.hiredate, 'YYYY'), '1981', 1)) 입사1981,

count(decode(to_char(e.hiredate, 'YYYY'), '1982', 1)) 입사1982

from emp e, dept d

where e.deptno = d.deptno

group by d.deptno, d.dname

-- 12. 1981년 5월 31일 이후 입사자 중 커미션이 NULL이거나 0인 사원의 커미션은 500으로 그렇지 않으면 기존 커미션을 출력하라.

select ename, decode(comm, NULL, '500', 0, '500',to_char(comm)) as COMM

from emp

where hiredate>to_date('1981-5-31')

-- 13. 1981년 6월 1일 ~ 1981년 12월 31일 입사자 중 부서명이 SALES인 사원의 부서번호, 사원명, 직업, 입사일을 출력하라.

-- 조건1. 입사일 오름차순 정렬

select e.deptno, d.dname, e.ename, e.job, e.hiredate

from emp e, dept d

where

e.deptno = d.deptno

and e.hiredate>=to_date('1981-6-1')

and e.hiredate<=to_date('1981-12-31')

and d.dname = 'SALES'

order by hiredate asc

-- 14. 현재 시간과 현재 시간으로부터 한 시간 후의 시간을 출력하라.

-- 조건1. 현재시간 포맷은 ‘4자리년-2자일월-2자리일 24시:2자리분:2자리초’로 출력

-- 조건1. 한시간후 포맷은 ‘4자리년-2자일월-2자리일 24시:2자리분:2자리초’로 출력

select

to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') 현재시간,

to_char(sysdate+1/24, 'YYYY-MM-DD HH24:MI:SS') 한시간후

from dual

-- 15. 각 부서별 사원수를 출력하라.

-- 조건1. 부서별 사원수가 없더라도 부서번호, 부서명은 출력

-- 조건2. 부서별 사원수가 0인 경우 ‘없음’ 출력

-- 조건3. 부서번호 오름차순 정렬

select d.deptno, d.dname,

decode(count(ename), 0,'없음',count(ename)) 사원수

from emp e, dept d

where e.deptno(+) = d.deptno

group by d.deptno, d.dname

order by d.deptno

-- 16. 사원 테이블에서 각 사원의 사원번호, 사원명, 매니저번호, 매니저명을 출력하라.

-- 조건1. 각 사원의 급여(SAL)는 매니저 급여보다 많거나 같다.

select

e.empno 사원번호, e.ename 사원명,

e.mgr 매니저사원번호, m.ename 매니저명

from emp e, emp m

where e.mgr = m.empno and e.sal>=m.sal

-- 18. 사원명의 첫 글자가 ‘A’이고, 처음과 끝 사이에 ‘LL’이 들어가는 사원의 커미션이 COMM2일때, 모든 사원의 커미션에 COMM2를 더한 결과를 사원명, COMM, COMM2, COMM+COMM2로 출력하라.

select

DECODE(comm, NULL, 0, comm) comm,

(select comm

from emp

where ename like 'A%LL%') as comm2,

(DECODE(comm, NULL, 0, comm) +

(select comm

from emp

where ename like 'A%LL%')) as "COMM + COMM2"

from emp

order by "COMM + COMM2"

-- 19. 각 부서별로 1981년 5월 31일 이후 입사자의 부서번호, 부서명, 사원번호, 사원명, 입사일을 출력하시오.

-- 조건1. 부서별 사원정보가 없더라도 부서번호, 부서명은 출력

-- 조건2. 부서번호 오름차순 정렬

-- 조건3. 입사일 오름차순 정렬

select d.deptno, d.dname, e.empno, e.ename, e.hiredate

from emp e RIGHT OUTER JOIN dept d

ON e.deptno = d.deptno

and to_char(e.hiredate, 'YYYYMMDD')> '19810531'

order by d.deptno, e.hiredate

-- 20. 입사일로부터 지금까지 근무년수가 30년 이상 미만인 사원의 사원번호, 사원명, 입사일, 근무년수를 출력하라.

-- 조건1. 근무년수는 월을 기준으로 버림 (예:30.4년 = 30년, 30.7년=30년)

select empno,ename,hiredate, trunc((sysdate - hiredate)/365) 근무년수

from emp

where trunc((sysdate - hiredate)/365)<30

한국정보 기술 연구원 KITRI 교육 (http://www.kitri.re.kr/)