실습문제_DECODE03.pdf

SELECT EMAIL,
    DECODE(INSTR(EMAIL,'@'),8,
    (REPLACE(EMAIL,SUBSTR(EMAIL,1,7),'???????')),7,
    (REPLACE(EMAIL,SUBSTR(EMAIL,1,6),'??????')),5,
    (REPLACE(EMAIL,SUBSTR(EMAIL,1,4),'?????'))) "EMAIL_?",
    HPAGE,
    DECODE(INSTR(HPAGE,'.',-1)-1-INSTR(HPAGE,'.'),3,
    REPLACE(HPAGE,
            SUBSTR(HPAGE,INSTR(HPAGE,'.')+1,3)
            ,'***'),5,
    REPLACE(HPAGE,
            SUBSTR(HPAGE,INSTR(HPAGE,'.')+1,5),
            '*****')) "HPAGE_*"
FROM PROFESSOR
WHERE HPAGE IS NOT NULL;

SELECT INSTR(HPAGE,'.',-1)-INSTR(HPAGE,'.')
FROM PROFESSOR;

20230509104303.png

실습문제_CASE03.pdf

SELECT EMPNO
    ,ENAME
    ,COMM
    ,CASE 
    WHEN COMM IS NULL THEN '해당사항 없음'
    WHEN COMM = 0  THEN '수당 없음'
    WHEN COMM>0 THEN '수당 :'||COMM
    END "COMM_TEXT"
FROM EMP;

20230509104558.png

실습문제_3장-X3.pdf

SELECT NAME,
    JUMIN,
    DEPTNO1,
    DECODE(SUBSTR(JUMIN,7,1),1,'M','F') "MF",
    WEIGHT,  
    SUM(WEIGHT) OVER(PARTITION BY DEPTNO1,SUBSTR(JUMIN,7,1) ORDER BY WEIGHT DESC  ) "TOTAL"
FROM STUDENT;

20230509115557.png

실습문제_3장-Y1.pdf

SELECT DISTINCT 
    SUBSTR(EMAIL,
        INSTR(EMAIL,'@',1)+1) "DOMAIN",
    
    COUNT(*)"EA",
    SUM(COUNT(*)) OVER() "SUM_DOMAIN",
    COUNT(*)/SUM(COUNT(*)) OVER() *100 "%"

FROM PROFESSOR
GROUP BY EMAIL;

20230509125357.png

실습문제_3장-Y2.pdf

SELECT NAME, 
    TEL, 
    TO_CHAR(PAY,'999,999,999') "PAY",
    TO_CHAR(SUM(PAY) OVER(
        PARTITION BY SUBSTR(TEL,0,INSTR(TEL,')',1)-1)),
        '999,999,999')
        "TOTAL_AREA",
    ROUND(PAY/(SUM(PAY) OVER(
        PARTITION BY SUBSTR(TEL,0,INSTR(TEL,')',1)-1))) * 100,2) "RATIO"
FROM EMP2
ORDER BY TEL;

20230509130906.png

실습문제_3장-A1.pdf

실습문제_3장-A2.pdf

SELECT
    COUNT(*)||'EA' "TOTAL",
    SUM(DECODE(SUBSTR(BIRTHDAY,4,2),'01',1,0))||'EA' "JAN",
    SUM(DECODE(SUBSTR(BIRTHDAY,4,2),'02',1,0))||'EA' "FEB",
    SUM(DECODE(SUBSTR(BIRTHDAY,4,2),'03',1,0))||'EA' "MAR",
    SUM(DECODE(SUBSTR(BIRTHDAY,4,2),'04',1,0))||'EA' "APR",
    SUM(DECODE(SUBSTR(BIRTHDAY,4,2),'05',1,0))||'EA' "MAY",
    SUM(DECODE(SUBSTR(BIRTHDAY,4,2),'06',1,0))||'EA' "JUN",
    SUM(DECODE(SUBSTR(BIRTHDAY,4,2),'07',1,0))||'EA' "JUL",
    SUM(DECODE(SUBSTR(BIRTHDAY,4,2),'08',1,0))||'EA' "AUG",
    SUM(DECODE(SUBSTR(BIRTHDAY,4,2),'09',1,0))||'EA' "SEP",
    SUM(DECODE(SUBSTR(BIRTHDAY,4,2),'10',1,0))||'EA' "OCT",
    SUM(DECODE(SUBSTR(BIRTHDAY,4,2),'11',1,0))||'EA' "NOV",
    SUM(DECODE(SUBSTR(BIRTHDAY,4,2),'12',1,0))||'EA' "DEC"
FROM STUDENT;

20230509150335.png

실습문제_3장-A3.pdf

SELECT
    COUNT(*) "TOTAL",
    SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'02',1,0)) "SEOUL",
    SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'031',1,0)) "GYEONGGI",
    SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'051',1,0)) "BUSAN",
    SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'052',1,0)) "ULSAN",
    SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'053',1,0)) "DAEGU",
    SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'055',1,0)) "GYEONGNAM"
   
FROM STUDENT;

20230509154841.png

실습문제_3장-A4.pdf

SELECT DEPTNO,
    ENAME,
    SAL,
    SUM(SAL) OVER(ORDER BY SAL ASC) "TOTAL"
FROM EMP;

20230509155019.png

실습문제_3장-A5.pdf

select * from fruit
pivot( SUM(PRICE) FOR NAME IN('apple','grape','orange'));

20230509155947.png

실습문제_3장-A6.pdf

SELECT
    COUNT(*)||'EA('||count(*)/count(*)*100||'%)' 
    "TOTAL",
    SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'02',1,0)) 
    ||'EA('||SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'02',1,0)) /count(*)*100||'%)'
    "SEOUL",
    SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'031',1,0)) 
    ||'EA('||SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'031',1,0)) /count(*)*100||'%)'
		"GYEONGGI",
    SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'051',1,0)) 
    ||'EA('||SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'051',1,0)) /count(*)*100||'%)'
    "BUSAN",
    SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'052',1,0)) 
    ||'EA('||SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'052',1,0)) /count(*)*100||'%)'
    "ULSAN",
    SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'053',1,0)) 
    ||'EA('||SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'053',1,0)) /count(*)*100||'%)'
    "DAEGU",
    SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'055',1,0)) 
    ||'EA('||SUM(DECODE(SUBSTR(TEL,0,INSTR(TEL,')')-1),'055',1,0)) /count(*)*100||'%)'
    "GYEONGNAM"
   
FROM STUDENT;

20230509165659.png

실습문제_3장-A7.pdf

SELECT DEPTNO,
    ENAME,
    SAL,
    SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ASC) "TOTAL"
FROM EMP
ORDER BY DEPTNO;

20230509165819.png

실습문제_3장-A8.pdf

SELECT DEPTNO,
    ENAME,
    SUM(SAL) OVER() "TOTAL_SAL" ,
    ROUND(SAL/SUM(SAL) OVER()*100,2) "%"
FROM EMP
ORDER BY SAL DESC;

20230509171036.png

실습문제_3장-A9.pdf

SELECT DEPTNO,
    ENAME,
    SAL,
    SUM(SAL) OVER(PARTITION BY DEPTNO) "SUM_DEPT" ,
    ROUND(SAL/SUM(SAL) OVER(PARTITION BY DEPTNO)*100,2) "%"
FROM EMP
ORDER BY DEPTNO;

20230509173607.png

실습문제_3장-A10.pdf

SELECT * FROM LOAN;

SELECT DISTINCT L_DATE "대출일자",
    L_CODE "대출종목코드",
    L_QTY "대출건수",
    L_TOTAL "대출총액",
    SUM(L_TOTAL) OVER(ORDER BY L_DATE DESC)  "누적대출금액"
FROM LOAN
WHERE L_STORE=1000;

20230509180146.png

실습문제_3장-A11.pdf

SELECT * FROM LOAN;

SELECT DISTINCT 
    L_CODE "대출종목코드",
    L_STORE "대출지점",
    L_DATE "대출일자",
    L_QTY "대출건수",
    L_TOTAL "대출액",
    
    SUM(L_TOTAL) OVER(PARTITION BY L_CODE,L_STORE ORDER BY L_DATE)  "누적대출금액"
FROM LOAN
ORDER BY L_CODE;

20230509181234.png