--테이블 자리에 쿼리가 옴
--서브쿼리
SELECT *FROM (
SELECT WEEKNO,
    DAY,
    DAYNO
FROM CAL
);

실습문제_3장-1.pdf

--테이블 자리에 쿼리가 옴
--서브쿼리
SELECT *FROM (
SELECT WEEKNO,
    DAY,
    DAYNO
FROM CAL)
PIVOT(
    (MAX(DAYNO))
    FOR DAY IN('SUN','MON','TUE','WED','THU','FRI','SAT')
)
ORDER BY WEEKNO;

실습문제_3장-2.pdf

내 코드

SELECT * 
FROM (SELECT 
    DEPTNO,
    JOB
FROM EMP
ORDER BY DEPTNO,JOB)
PIVOT(
    COUNT(JOB)
    FOR JOB IN('CLERK' "CLERK",
    'MANAGER' "MANAGER"
    ,'PRESIDNET' "PRESIDENT",
    'ANALYST' "ANALYST",
    'SALESMAN' "SALESMAN")
)
ORDER BY DEPTNO;

정답코드

SELECT DEPTNO
    ,SUM(DECODE(JOB,'CLERK',1,0)) "CLERK"
    ,SUM(DECODE(JOB,'MANAGER',1,0)) "MANAGER"
    ,SUM(DECODE(JOB,'PRESIDENT',1,0)) "PRESIDENT"
    ,SUM(DECODE(JOB,'ANALYST',1,0)) "ANALYST"
    ,SUM(DECODE(JOB,'SALESMAN',1,0)) "SALESMAN"
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

SELECT DEPTNO
    ,COUNT(DECODE(JOB,'CLERK',1)) "CLERK"
    ,COUNT(DECODE(JOB,'MANAGER',1)) "MANAGER"
    ,COUNT(DECODE(JOB,'PRESIDENT',1)) "PRESIDENT"
    ,COUNT(DECODE(JOB,'ANALYST',1)) "ANALYST"
    ,COUNT(DECODE(JOB,'SALESMAN',1)) "SALESMAN"
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

실습문제_3장-3.pdf

내 코드

SELECT DEPTNO
    ,COUNT(DECODE(JOB,'CLERK',1)) "CLERK"
    ,SUM(DECODE(JOB,'CLERK',SAL)) "CLERK_SUM"
    ,COUNT(DECODE(JOB,'MANAGER',1)) "MANAGER"
    ,SUM(DECODE(JOB,'MANAGER',SAL)) "MANAGER_SUM"
    ,COUNT(DECODE(JOB,'PRESIDENT',1)) "PRESIDENT"
    ,SUM(DECODE(JOB,'PRESIDENT',SAL)) "PRESIDENT_SUM"
    ,COUNT(DECODE(JOB,'ANALYST',1)) "ANALYST"
    ,SUM(DECODE(JOB,'ANALYST',SAL)) "ANALYST_SUM" 
    ,COUNT(DECODE(JOB,'SALESMAN',1)) "SALESMAN"
    ,SUM(DECODE(JOB,'SALESMAN',SAL)) "SALESMAN_SUM"
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;