데이터베이스 퀴즈 및 튜닝 문제
추석 잘 보내셨나요? 문제 하나 들어갑니다.
scott.emp 테이블을 이용하여 각 사원의 급여와 부서별 급여 합계 및 평균을 구하는 쿼리를 작성하세요.
쿼리의 결과는 다음과 같습니다.
|
DEPTNO |
EMPNO |
ENAME |
SAL |
|
10 |
7782 |
CLARK |
2450 |
|
10 |
7839 |
KING |
5000 |
|
10 |
7934 |
MILLER |
1300 |
|
10 |
|
합계 |
8750 |
|
10 |
|
평균 |
2916.67 |
|
20 |
7369 |
SMITH |
800 |
|
20 |
7566 |
JONES |
2975 |
|
20 |
7788 |
SCOTT |
3000 |
|
20 |
7876 |
ADAMS |
1100 |
|
20 |
7902 |
FORD |
3000 |
|
20 |
|
합계 |
10875 |
|
20 |
|
평균 |
2175 |
|
30 |
7499 |
ALLEN |
1600 |
|
30 |
7521 |
WARD |
1250 |
|
30 |
7654 |
MARTIN |
1250 |
|
30 |
7698 |
BLAKE |
2850 |
|
30 |
7844 |
TURNER |
1500 |
|
30 |
7900 |
JAMES |
950 |
|
30 |
|
합계 |
9400 |
|
30 |
|
평균 |
1566.67 |
힌트 : ROLLUP, GROUP_ID() 이용
[답안보기] <== 트리플 클릭
SELECT deptno
, empno
, DECODE(GROUP_ID(),0,NVL(ename,'합계'),'평균') ename
, DECODE(GROUP_ID(),0,SUM(sal),ROUND(AVG(sal),2)) sal
FROM scott.emp
GROUP BY deptno, ROLLUP(deptno, (empno, ename))
ORDER BY deptno, GROUP_ID(), empno
by 김강환
[2008.09.16 18:29:13]
오랫만에 ROLLUP을 보니 또 헷갈리네요 ㅋ~ 마농님 덕분에 다시 한번 공부하게 되었습니다. 참고로 ROLLUP을 사용하지 못하는 상황이라면 아래 처럼...
SELECT
DEPTNO
,DECODE(LV,1,EMPNO||'',2,'',3,'') EMPNO
,DECODE(LV,1,ENAME,2,'합계',3,'평균') ENAME
,DECODE(MIN(LV),1,MIN(SAL),2,SUM(SAL),3,ROUND(AVG(SAL),2)) SAL
FROM EMP A
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL<=3)
GROUP BY DEPTNO
,DECODE(LV,1,EMPNO||'',2,'',3,'')
,DECODE(LV,1,ENAME,2,'합계',3,'평균')
ORDER BY DEPTNO,MIN(LV)
SELECT
DEPTNO
,DECODE(LV,1,EMPNO||'',2,'',3,'') EMPNO
,DECODE(LV,1,ENAME,2,'합계',3,'평균') ENAME
,DECODE(MIN(LV),1,MIN(SAL),2,SUM(SAL),3,ROUND(AVG(SAL),2)) SAL
FROM EMP A
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL<=3)
GROUP BY DEPTNO
,DECODE(LV,1,EMPNO||'',2,'',3,'')
,DECODE(LV,1,ENAME,2,'합계',3,'평균')
ORDER BY DEPTNO,MIN(LV)
by 호야
[2008.09.18 18:30:41]
소수 자리가.. 막 나와요.ㅠ..ㅠ 우힝 시간이 없어서 요기 까지만..
처음으로 풀어봤네요^^; 마농님 사랑해요..-_- 남자는 싫겠지만
select deptno, empno,nvl(decode(group_id(),1,ename,'평균'),'합계') ename
,decode(group_id(),1,sum(sal),sum(sal)/count(sal)) sal
from emp
group by deptno,rollup(deptno,(empno,ename))
order by deptno,empno,ename desc
처음으로 풀어봤네요^^; 마농님 사랑해요..-_- 남자는 싫겠지만
select deptno, empno,nvl(decode(group_id(),1,ename,'평균'),'합계') ename
,decode(group_id(),1,sum(sal),sum(sal)/count(sal)) sal
from emp
group by deptno,rollup(deptno,(empno,ename))
order by deptno,empno,ename desc
by 이재현
[2008.12.18 11:51:58]
쪼렙입니다.,, (--)(__)
잘 배우고 있습니다. 감사합니다. ^^
잘 배우고 있습니다. 감사합니다. ^^
by WW
[2008.12.18 15:31:46]
트리플 클릭^^
수고하셨어요.
수고하셨어요.
by 마농
[2009.06.26 17:30:58]
오라클 10G의 Model 기능 사용.
SELECT deptno, empno, ename, sal
FROM scott.emp
MODEL PARTITION BY (deptno)
DIMENSION BY (empno, 0 i)
MEASURES (ename, sal)
RULES(ename['',1] = '합계'
, ename['',2] = '평균'
, sal['',1] = SUM(sal)[ANY, 0]
, sal['',2] = ROUND(AVG(sal)[ANY, 0],2)
)
ORDER BY deptno, empno, i
;
SELECT deptno, empno, ename, sal
FROM scott.emp
MODEL PARTITION BY (deptno)
DIMENSION BY (empno, 0 i)
MEASURES (ename, sal)
RULES(ename['',1] = '합계'
, ename['',2] = '평균'
, sal['',1] = SUM(sal)[ANY, 0]
, sal['',2] = ROUND(AVG(sal)[ANY, 0],2)
)
ORDER BY deptno, empno, i
;
) 버튼을 클릭하여 작성 하시면 됩니다.