#39_140806_TUNING_SQL 재작성

# SQL 재작성

문제104. 아래의 SQL을 재작성하시오(아래처럼 조인을 안하고도 똑 같은 결과가 나오도록 하시오)

# 튜닝 전

select decode(no,1,deptno,2, null), sum(sal)   from  emp  e, (select rownum no from dual                 connect by level <=2 ) d   group by decode(no,1,deptno,2, null)   order by decode(no,1,deptno,2, null);

# 튜닝 후

SELECT deptno, SUM(sal) from EMP GROUP BY ROLLUP(deptno);

문제105. 아래의 SQL을 재작성 하시오(조인을 안하고도 똑 같은 결과가 나오도록 하시오)

# 튜닝 전

select empno, ename,sal,   ( select sum(Sal) from emp e where e.empno <= b.empno) sumsal   from emp b  order by empno;

# 튜닝 후

SELECT empno, ename, sal,        SUM(sal) over(ORDER BY empno) sumsal FROM EMP;

점심문제. 부서번호, 사원번호, 이름, 월급이 아래처럼 자기 자신의 월급이 나오면서 부서번호의 토탈월급과 토탈월급이 출려되게 하시오

select deptno deptno,no      ,decode(no, 1, to_char(empno), 2, 'deptsum') empno      ,sum(sal) salsum, round(avg(sal)) salavg from   emp a,(select rownum no from dual connect by level <= 2) group by deptno, no, decode(no, 1, to_char(empno),2, 'deptsum') order by 1, 2;

SELECT * FROM  (SELECT   deptno, NVL(TO_CHAR(empno), 'deptsum') AS empno, ename,                 SUM(sal) AS sum_sal, ROUND(AVG(sal)) AS avg_sal        FROM     emp        GROUP BY ROLLUP(deptno, (empno, ename))) WHERE  deptno IS NOT NULL; - 주의할 점은 nvl 사용시에 컬럼과 value 값이 데이터형이 다르다면 이를 맞춰줘야 한다. - 그렇기 때문에 nvl에 to_char를 이용해서 empno를 문자형으로 바꿔줄 필요가 있다 - 1. deptno, empno, ename    2. deptno   - 위 2가지가 결과가 나온다

마지막문제. 아래의 SQL을 union all 를 이용해서 똑 같은 결과를 출력하시오

SELECT deptno, job, SUM(sal) FROM EMP GROUP BY ROLLUP(deptno,job);

SELECT DISTINCT deptno,  job, SUM(sal) over(PARTITION BY deptno,job) sumsal FROM EMP WHERE deptno = 10 UNION ALL SELECT 10 AS deptno, NULL AS job, SUM(sal) AS sumsal FROM EMP WHERE deptno =10 UNION ALL SELECT DISTINCT deptno, job, SUM(sal) over(PARTITION BY deptno,job) sumsal FROM EMP WHERE deptno = 20 UNION ALL SELECT 20 AS deptno, NULL AS job, SUM(sal) AS sumsal FROM EMP WHERE deptno = 20 UNION all SELECT DISTINCT deptno, job, SUM(sal) over(PARTITION BY deptno,job) sumsal FROM EMP WHERE deptno = 30 UNION ALL SELECT 30 AS deptno, NULL AS job, SUM(sal) AS sumsal FROM EMP WHERE deptno =30 UNION ALL SELECT NULL AS deptno, NULL AS job, SUM(sal) AS sumsal  FROM EMP ORDER BY deptno, job; - union all을 이용해서 합친 후에 order by 를 쓰기 위해서는 맨 마지막에 추가해야 하고 모든 컬럼의 명칭이 같아야 한다

점심문제. 직업, 이름, 월급, 각각 직업별 최대월급을 받는 사원의 이름, 각각 직업별 최대월급, 각각 직업별 최소월급을 받는 사원의 이름, 각각 직업별 최소월급을 출력하시오. 이 SQL을 분석함수를 이용하지 않았을때의 SQL로 작성하시오

UPDATE EMP

SET sal = 1300

WHERE ename='MARTIN';

UPDATE EMP

SET sal = 2900

WHERE ename='SCOTT';

select job, ename, sal,      first_value(ename) over(partition by job ORDER BY sal desc) ename1,      FIRST_VALUE(sal) OVER(PARTITION BY job ORDER BY sal desc) maxsal,      first_value(ename) over(partition by job ORDER BY sal asc) ename1,      FIRST_VALUE(sal) OVER(PARTITION BY job ORDER BY sal asc) maxsal FROM EMP;

SELECT e.job, e.ename, f.ename, f.maxsal, g.ename, g.minsal FROM EMP e, (SELECT ename, job, sal maxsal             FROM EMP             WHERE ename<>'MARTIN' AND sal in (SELECT MAX(sal) FROM EMP GROUP BY job)             ) f,             (SELECT ename, job, sal minsal             FROM EMP             WHERE sal in (SELECT min(sal) FROM EMP GROUP BY job)             ) g WHERE e.job=f.job AND f.job=g.job;

 

 

점심문제. 아래의 SQL을 rank()를 제외한 분석함수를 쓰지 않고 결과를 같게 출력하시오

 

select  deptno,ename, min(sal) as sal,  nth_value(min(sal),1) over (partition by deptno order by min(sal) desc             rows between unbounded preceding and unbounded following) rank1,  nth_value(min(sal),2) over (partition by deptno order by min(sal) desc             rows between unbounded preceding and unbounded following) rank2,  nth_value(min(sal),3) over (partition by deptno order by min(sal) desc             rows between unbounded preceding and unbounded following) rank3 from emp group by  deptno,ename; - rows between unbounded preceding and unbounded following unbounded 문법은 부서번호 partition에서 처음부터 끝까지 찾으라는 의미 - nth_value : 안에는 단순하게 sal은 안들어가고 min(sal)로 들어가야한다

SELECT e.deptno, e.ename, e.sal, f.sal, g.sal, h.sal FROM EMP e,      (SELECT e.empno, e.ename, e.deptno, f.sal      FROM EMP e,          (SELECT empno, sal, RANK() OVER(PARTITION BY deptno ORDER BY sal desc) rnk          FROM EMP) f      WHERE e.empno=f.empno AND rnk =1) f,      (SELECT e.empno, e.ename, e.deptno, f.sal      FROM EMP e,          (SELECT empno, sal, RANK() OVER(PARTITION BY deptno ORDER BY sal desc) rnk          FROM EMP) f      WHERE e.empno=f.empno AND rnk =2) g,      (SELECT e.empno, e.ename, e.deptno, f.sal      FROM EMP e,          (SELECT empno, sal, RANK() OVER(PARTITION BY deptno ORDER BY sal desc) rnk          FROM EMP) f      WHERE e.empno=f.empno AND rnk =3) h WHERE e.deptno=f.deptno AND e.deptno=g.deptno AND e.deptno=h.deptno;