P.212

연습문제 1

emp 테이블을 사용하여 사원 중에서 급여(sal)와 보너스(comm)를 합친 금액이 가장 많은 경우와 가장 적은 경우, 평균 급액을 구하세요. 단, 보너스가 없을 경우는 보너스를 0으로 계산하고 출력 금액은 모두 소수점 첫째 자리까지만 나오게 하세요.
결과 화면

       MAX        MIN        AVG
---------- ---------- ----------
      5000        800     2260.4

SQL문

SELECT MAX(sal+NVL(comm, 0)) "MAX", MIN(sal+NVL(comm, 0)) "MIN",
       ROUND(AVG(sal+NVL(comm, 0)), 1) "AVG"
FROM emp;

연습문제 2

Student 테이블의 birthday 컬럼을 참조해서 아래와 같이 월별로 생일자 수를 출력하세요.
결과 화면

TOTAL   JAN   FEV   MAR   APR   MAY   JUN   JUL   AUG   SEP   OCT   NOV   DEC
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
20EA    3EA   3EA   2EA   2EA   0EA   1EA   0EA   2EA   2EA   2EA   1EA   2EA

SQL문

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

연습문제 3

Student 테이블의 tel 컬럼을 참고하여 아래와 같이 지역별 인원수를 출력하세요. 단, 02 - SEOUL, 031-GYEONGGI, 051-BUSAN, 052-ULSAN, 053-DAEGU, 055-GYEONGNAM으로 출력하세요.
결과 화면

     TOTAL      SEOUL   GYEONGGI      BUSAN      ULSAN      DAEGU  GYEONGNAM
---------- ---------- ---------- ---------- ---------- ---------- ----------
        20          6          2          4          0          2          6

SQL문

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

연습문제 4

먼저 emp 테이블에 아래 두 건의 데이터를 입력한 후 작업하세요.
EMP테이블을 사용하여 아래의 화면과 같이 부서별로 직급별로 급여 합계 결과를 출력하세요.

INSERT INTO emp (empno, deptno, ename, sal)
VALUES (1000, 10, 'Tiger', 3600);
INSERT INTO emp (empno, deptno, ename, sal)
VALUES (2000, 10, 'Cat', 3000);

출력 결과 화면

    DEPTNO      CLERK    MANAGER  PRESIDENT    ANALYST   SALESMAN      TOTAL
---------- ---------- ---------- ---------- ---------- ---------- ----------
        10       1300       2450       5000          0          0       8750
        20        800       2975          0       3000          0       6775
        30        950       2850          0          0       5600       9400
                 3050       8275       5000       3000       5600      24925

SQL문

SELECT deptno,
       SUM(DECODE(job, 'CLERK', sal, 0)) "CLERK",
       SUM(DECODE(job, 'MANAGER', sal, 0)) "MANAGER",
       SUM(DECODE(job, 'PRESIDENT', sal, 0)) "PRESIDENT",
       SUM(DECODE(job, 'ANALYST', sal, 0)) "ANALYST",
       SUM(DECODE(job, 'SALESMAN', sal, 0)) "SALESMAN",
       SUM(NVL2(job, sal, 0)) "TOTAL"
FROM emp
GROUP BY ROLLUP(deptno)
ORDER BY deptno ASC;

연습문제 5

emp 테이블을 사용하여 직원들의 급여와 전체 급여의 누적 급여금액이 아래와 같도록 출력하세요. 단, 급여를 오름차순으로 정렬해서 출력하세요.
결과 화면

    DEPTNO ENAME                       SAL      TOTAL
---------- -------------------- ---------- ----------
        20 SMITH                       800        800
        30 JAMES                       950       1750
        30 MARTIN                     1250       4250
        30 WARD                       1250       4250
        10 MILLER                     1300       5550
        30 TURNER                     1500       7050
        30 ALLEN                      1600       8650
        10 CLARK                      2450      11100
        30 BLAKE                      2850      13950
        20 JONES                      2975      16925
        10 Cat                        3000      22925
        20 FORD                       3000      22925
        10 Tiger                      3600      26525
        10 KING                       5000      31525

SQL문

SELECT deptno, ename, sal, SUM(sal) OVER(ORDER BY sal) "TOTAL"
FROM emp
ORDER BY sal;

연습문제 6

fruit 테이블을 아래와 같은 형태로 출력하세요.
결과 화면

     APPLE      GRAPE     ORANGE
---------- ---------- ----------
       100        200        300

PIVOT 기능을 사용한 SQL문

SELECT * FROM fruit
PIVOT
(    SUM(price) FOR name IN ('apple' as "APPLE",
                            'grape' as "GRAPE",
                            'orange' as "ORANGE")
);

DECODE 함수를 활용한 SQL문

SELECT SUM(DECODE(name, 'apple', price, 0)) "APPLE",
       SUM(DECODE(name, 'grape', price, 0)) "GRAPE",
       SUM(DECODE(name, 'orange', price, 0)) "ORANGE"
FROM fruit;

연습문제7

student 테이블의 Tel 컬럼을 사용하여 아래와 같이 지역별 인원수와 전체 대비 차지하는 비율을 출력하세요. 단, 02-SEOUL, 031-GYEONGGI, 051-BUSAN, 052-ULSAN. 053-DAEGU, 055-GYEONGNAM으로 출력하세요.
결과 화면

TOTAL        SEOUL        GYEONGGI     BUSAN        ULSAN        DAEGU        GYEONGNAM
------------ ------------ ------------ ------------ ------------ ------------ ------------
20EA (100%)  6EA (30%)    2EA (10%)    4EA (20%)    0EA (0%)     2EA (10%)    6EA (30%)

SQL문

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

연습문제 8

emp 테이블을 사용하여 아래와 같이 부서별로 급여 누적 합계가 나오도록 출력하세요. 단, 부서 번호로 오름차순 출력하세요.
결과 화면

    DEPTNO ENAME                       SAL      TOTAL
---------- -------------------- ---------- ----------
        10 MILLER                     1300       1300
        10 CLARK                      2450       3750
        10 Cat                        3000       6750
        10 Tiger                      3600      10350
        10 KING                       5000      15350
        20 SMITH                       800        800
        20 JONES                      2975       3775
        20 FORD                       3000       6775
        30 JAMES                       950        950
        30 WARD                       1250       3450
        30 MARTIN                     1250       3450
        30 TURNER                     1500       4950
        30 ALLEN                      1600       6550
        30 BLAKE                      2850       9400

SQL문

SELECT deptno, ename, sal, SUM(sal) OVER(PARTITION BY deptno ORDER BY sal) "TOTAL"
FROM emp;

연습문제 9

emp 테이블을 사용하여 아래와 같이 각 사원의 급여액이 전체 직원 급여 총액에서 몇 %의 비율을 차지하는지 출력하세요. 단, 급여 비중이 높은 사람이 먼저 출력되도록 하세요.
결과 화면

    DEPTNO ENAME                       SAL  TOTAL_SAL          %
---------- -------------------- ---------- ---------- ----------
        10 KING                       5000      31525      15.86
        10 Tiger                      3600      31525      11.42
        10 Cat                        3000      31525       9.52
        20 FORD                       3000      31525       9.52
        20 JONES                      2975      31525       9.44
        30 BLAKE                      2850      31525       9.04
        10 CLARK                      2450      31525       7.77
        30 ALLEN                      1600      31525       5.08
        30 TURNER                     1500      31525       4.76
        10 MILLER                     1300      31525       4.12
        30 MARTIN                     1250      31525       3.97
        30 WARD                       1250      31525       3.97
        30 JAMES                       950      31525       3.01
        20 SMITH                       800      31525       2.54

SQL문

SELECT deptno, ename, sal, SUM(sal) OVER() "TOTAL_SAL", 
       ROUND(RATIO_TO_REPORT(SUM(sal)) OVER()*100,2) "%"
FROM emp
GROUP BY deptno, ename, sal
ORDER BY sal DESC;

연습문제 10

emp 테이블을 조회하여 아래와 같이 각 직원들의 급여가 해당 부서 합계금액에서 몇 %의 비중을 차지하는지를 출력하세요. 단, 부서 번호를 기준으로 오름차순으로 출력하세요.
결과 화면

    DEPTNO ENAME                       SAL   SUM_DEPT          %
---------- -------------------- ---------- ---------- ----------
        10 CLARK                      2450      15350      15.96
        10 Cat                        3000      15350      19.54
        10 KING                       5000      15350      32.57
        10 MILLER                     1300      15350       8.47
        10 Tiger                      3600      15350      23.45
        20 FORD                       3000       6775      44.28
        20 JONES                      2975       6775      43.91
        20 SMITH                       800       6775      11.81
        30 ALLEN                      1600       9400      17.02
        30 BLAKE                      2850       9400      30.32
        30 JAMES                       950       9400      10.11
        30 MARTIN                     1250       9400       13.3
        30 TURNER                     1500       9400      15.96
        30 WARD                       1250       9400       13.3

SQL문

SELECT deptno, ename, sal, SUM(sal) OVER(PARTITION BY deptno) "SUM_DEPT",
       ROUND(RATIO_TO_REPORT(SUM(sal)) OVER(PARTITION BY deptno)*100,2) "%"
FROM emp
GROUP BY deptno, ename, sal;

연습문제 11

loan 테이블을 사용하여 1000번 지점의 대출 내역을 출력하되 대출일자, 대출종목코드, 대출건수, 대출총액, 누적대출금액을 아래와 같이 출력하세요.
결과 화면

대출일자         대출종목코드   대출건수   대출총액 누적대출금액
---------------- ------------ ---------- ---------- ------------
20110101                  100          3       2400         2400
20110102                  102          2       2000         7400
20110102                  105          2       3000         7400
20110103                  100          2       1600         9000

SQL문

SELECT l_date "대출일자", l_code "대출종목코드", l_qty "대출건수", l_total "대출총액",
       sum(l_total) over(ORDER BY l_date) "누적대출금액"
FROM loan
WHERE l_store = 1000;

연습문제 12

loan 테이블을 사용하여 전체 지점의 대출종목코드, 대출지점, 대출일자, 대출건수, 대출액을 대출코드와 대출지점별로 누적 합계를 구하세요.
결과 화면

대출종목코드 대출지점   대출일자           대출건수     대출액 누적대출금액
------------ ---------- ---------------- ---------- ---------- ------------
         100 1000       20110101                  3       2400         2400
         100 1000       20110103                  2       1600         4000
         100 1001       20110103                  3       2400         2400
         100 1002       20110104                  2       1600         1600
         100 1003       20110104                  4       3200         3200
         100 1004       20110103                 10       8000         8000
         100 1004       20110104                  5       4000        12000
         101 1001       20110101                  5       4500         4500
         101 1001       20110104                  3       2700         7200
         101 1002       20110104                  4       3600         3600
         101 1003       20110103                  4       3600         3600
         101 1003       20110104                  3       2700         6300
         102 1000       20110102                  2       2000         2000
         102 1001       20110104                  4       4000         4000
         102 1002       20110104                  2       2000         2000
         102 1003       20110101                  2       2000         2000
         103 1002       20110102                  5       4500         4500
         103 1003       20110104                  2       1800         1800
         103 1004       20110101                  6       5400         5400
         104 1002       20110102                  3       2400         2400
         105 1000       20110102                  2       3000         3000

SQL문

SELECT 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;

연습문제 13

loan 테이블을 조회하여 1000번 지점의 대출 내역을 대출 코드별로 합쳐서 대출일자, 대출구분코드, 대출건수, 대출총액, 코드별 누적대출금액을 아래와 같이 출력하세요.
결과 화면

대출일자         대출구분코드   대출건수   대출총액 누적대출금액
---------------- ------------ ---------- ---------- ------------
20110103                  100          2       1600         1600
20110101                  100          3       2400         4000
20110102                  102          2       2000         2000
20110102                  105          2       3000         3000

SQL문

SELECT l_date "대출일자", l_code "대출구분코드", l_qty "대출건수", l_total "대출총액",
       sum(l_total) over(PARTITION BY l_code ORDER BY l_total) "누적대출금액"
FROM loan
WHERE l_store = 1000;

연습문제 14

professor 테이블에서 각 교수들의 급여를 구하고 각 교수의 급여액이 전체 교수의 급여 합계에서 차지하는 비율을 출력하세요.
결과 화면

    DEPTNO NAME                                            PAY  TOTAL PAY    RATIO %
---------- ---------------------------------------- ---------- ---------- ----------
       201 Meryl Streep                                    570       5920       9.63
       101 Audie Murphy                                    550       5920       9.29
       103 Emma Thompson                                   530       5920       8.95
       203 Meg Ryan                                        500       5920       8.45
       102 Whoopi Goldberg                                 490       5920       8.28
       101 Angela Bassett                                  380       5920       6.42
       102 Michelle Pfeiffer                               350       5920       5.91
       103 Julia Roberts                                   330       5920       5.57
       201 Susan Sarandon                                  330       5920       5.57
       202 Nicole Kidman                                   310       5920       5.24
       301 Jodie Foster                                    290       5920        4.9
       103 Sharon Stone                                    290       5920        4.9
       101 Jessica Lange                                   270       5920       4.56
       202 Holly Hunter                                    260       5920       4.39
       102 Winona Ryder                                    250       5920       4.22
       301 Andie Macdowell                                 220       5920       3.72

SQL문

SELECT deptno, name, pay, SUM(pay) OVER() "TOTAL PAY",
       ROUND(RATIO_TO_REPORT(SUM(pay)) OVER()*100, 2) "RATIO %"
FROM professor
GROUP BY deptno, name, pay
ORDER BY pay DESC;

연습문제 15

professor 테이블을 조회하여 학과번호, 교수명, 급여, 학과별 급여 합계를 구하고 각 교수의 급여가 해당 학과별 급여 합계에서 차지하는 비율을 출력하세요.
결과 화면

    DEPTNO NAME                                            PAY  TOTAL PAY    RATIO %
---------- ---------------------------------------- ---------- ---------- ----------
       101 Angela Bassett                                  380       1200      31.67
       101 Audie Murphy                                    550       1200      45.83
       101 Jessica Lange                                   270       1200       22.5
       102 Michelle Pfeiffer                               350       1090      32.11
       102 Whoopi Goldberg                                 490       1090      44.95
       102 Winona Ryder                                    250       1090      22.94
       103 Emma Thompson                                   530       1150      46.09
       103 Julia Roberts                                   330       1150       28.7
       103 Sharon Stone                                    290       1150      25.22
       201 Meryl Streep                                    570        900      63.33
       201 Susan Sarandon                                  330        900      36.67
       202 Holly Hunter                                    260        570      45.61
       202 Nicole Kidman                                   310        570      54.39
       203 Meg Ryan                                        500        500        100
       301 Andie Macdowell                                 220        510      43.14
       301 Jodie Foster                                    290        510      56.86

SQL문

SELECT deptno, name, pay, SUM(pay) OVER(PARTITION BY deptno) "TOTAL PAY",
       ROUND(RATIO_TO_REPORT(SUM(pay)) OVER(PARTITION BY deptno)*100, 2) "RATIO %"
FROM professor
GROUP BY deptno, name, pay;

출처