오라클 11g와 함께하는 SQL과 PL/SQL 9장 연습문제 정답 정리

*아래 연습문제는 연습문제를 위한 견본데이터베이스가 있어야 실행 가능

★연습문제1

1. 회원관리(EC_Member) 테이블을 이용하여 총회원수를 출력하시오.

SELECT COUNT(*)"총회원수"
FROM   EC_Member;

---

2. 주문처리(EC_Order) 테이블에서 결제금액(CMONEY)이 제일 적은 금액과 제일 많은 금액을 출력하시오.

SELECT MIN(CMONEY),MAX(CMONEY)
FROM   EC_Order;

---

3. 주문처리(EC_Order) 테이블에서 결제한 회원의 주문자ID별 결제 금액(CMONEY)의 합계를 계산하여 [주문자ID, 결제합계]를 출력하시오.

SELECT   ORDER_ID "주문자 ID", TO_CHAR(SUM(CMONEY),'L999,999,999')"결제합계"
FROM     EC_Order
GROUP BY ORDER_ID;

---

4. 상품관리(EC_Product) 테이블의 단가(Unit_Price)를 이용하여 역순으로 상품 단가에 순위를 부여하여 [상품코드, 상품명, 단가, 순위]를 출력하시오.

SELECT PRODUCT_CODE, PRODUCT_NAME, UNIT_PRICE,
         RANK() OVER (ORDER BY UNIT_PRICE DESC) "상품단가순위"
FROM     EC_Product
ORDER BY 3 DESC;

---

5. 회원관리(EC_Member) 테이블을 이용하여 회원의 시도별 분포 인원수와 전체 인원수를 계산하여 출력하시오. (ROLLUP 연산)

SELECT UNIQUE SUBSTR(ADDRESS,1,2)"시도별",
COUNT(*) "인원수"
FROM EC_Member
GROUP BY ROLLUP(SUBSTR(ADDRESS,1,2))
ORDER BY 1;

---

6. 주문처리(EC_Order) 테이블에서 결제 년월별 판매금액계를 출력하시오. GROUPING() 함수를 사용하여 '결제년월'이 연산에 사용되지 않을 경우 '합계금액'을 출력한다. (ROLLUP 연산, CASE() 함수 사용)

SELECT CASE GROUPING(SUBSTR(CDATE,1,5)) WHEN 0 THEN SUBSTR(CDATE,1,5)
                                        WHEN 1 THEN ' 합계금액'
       END "결제년월",
       TO_CHAR(SUM(CMONEY),'L999,999,999') "판매금액계"
FROM EC_Order
GROUP BY ROLLUP(SUBSTR(CDATE,1,5))
ORDER BY GROUPING(SUBSTR(CDATE,1,5)),1;

---

7. 주문처리(EC_Order) 테이블에서 2018년 1/4분기(1월~3월)의 주문자ID, 주문상품, 결제금액을 (주문자ID, 상품코드별), 회원별 집계, 상품코드별 집계, 전체 합계를 계산하여 주문자ID 순으로 출력하시오. (CUBE 연산, CASE() 함수, GROUPING_ID() 함수 사용)

SELECT CASE GROUPING_ID(ORDER_ID, PRODUCT_CODE) WHEN 0 THEN ORDER_ID
                                                WHEN 1 THEN NULL
                                                WHEN 2 THEN ' 상품 집계'
                                                WHEN 3 THEN ' 전체 합계'
       END "주문자_ID",
       CASE GROUPING_ID(ORDER_ID, PRODUCT_CODE) WHEN 0 THEN PRODUCT_CODE
                                                WHEN 1 THEN ' 회원집계'
                                                WHEN 2 THEN PRODUCT_CODE
                                                WHEN 3 THEN NULL
                                                
                                       
       END "주문상품", TO_CHAR(SUM(CMONEY),'L999,999,999') "결제금액"
FROM   EC_Order
WHERE TO_CHAR(CDATE,'YY/MM') >='18/01' AND TO_CHAR(CDATE,'YY/MM')<='18/03'
GROUP  BY CUBE(ORDER_ID,PRODUCT_CODE)
ORDER BY ORDER_ID;

---

★연습문제2

1. 주문처리(EC_Order) 테이블에서 결제한 결제년월별로 [결제년월, 주문건수, 주문 수량 합계, 결제 금액 합계]를 계산하여 결제년월순으로 출력하시오.

SELECT TO_CHAR(Cdate, 'YYYY/MM') "결제년월", Count(2) "주문건수",
       SUM(Order_QTY) "주문수량 합계", TO_CHAR(SUM(CMONEY),'L999,999,999')"결제합계"
FROM EC_ORDER
WHERE Cdate IS NOT NULL
GROUP BY TO_CHAR(Cdate, 'YYYY/MM')
ORDER BY 1;

---

2. 회원관리(EC_Member) 테이블에서 남, 여 회원수를 출력하시오. 단, 성별 코드가 1또는 3이면 남자, 2 또는 4는 여자임.

SELECT DECODE(SUBSTR(REGIST_NO,8,1),1,'남자',2,'여자',3,'남자',4,'여자')
"성별", COUNT(*) "회원수"
FROM EC_MEMBEr
GROUP BY DECODE(SUBSTR(REGIST_NO,8,1),1,'남자',2,'여자',3,'남자',4,'여자');

---

3. 주문처리(EC_Order) 테이블에서 년월별 결제금액이 가장 많은 금액을 결제년월별순으로 출력하시오.

SELECT TO_CHAR(Cdate, 'YYYY/MM') "결제년월", TO_CHAR(MAX(CMoney),'L999,999,999') "결제최대금액"
FROM   EC_ORDER
WHERE  CDATE IS NOT NULL
GROUP  BY TO_CHAR(Cdate, 'YYYY/MM')
ORDER BY 1;

---

4. 주문처리(EC_Order) 테이블에서 주문자ID, 주문상품코드, 구매횟수, 결제금액을 (주문자, 상품코드별), 주문자별 소계, 전체 합계를 계산하여 주문자ID순으로 출력하시오. (ROLLUP 연산, CASE() 함수와 GROUPING_ID() 함수 사용)

SELECT CASE GROUPING_ID(Order_ID, Product_Code) WHEN 1 THEN NULL
                                                       ELSE Order_ID
            END "주문자_ID",
            CASE GROUPING_ID(Order_ID, Product_Code) WHEN 1 THEN '     소계'
                                                    WHEN 3 THEN '전체합계'
                                                        ELSE Product_Code
            END "주문상품",
            COUNT(*) "구매횟수", TO_CHAR(SUM(Cmoney),'L999,999,999')"결제금액"
FROM       EC_Order
WHERE      CDate IS NOT NULL
GROUP      BY ROLLUP(Order_ID, Product_Code)
ORDER      BY ORder_ID;

---

5. 주문처리(EC_Order) 테이블에서 주문자ID, 상품코드, 결제금액을 주문자별, 상품코드별로 계산하여 주문자, 상품코드순으로 출력하시오. (GROUPING SETS 연산)

SELECT Order_ID, Product_Code, TO_CHAR(SUM(Cmoney),'L999,999,999') "결제금액"
FROM   EC_Order
WHERE CDAte IS NOT NULL
GROUP BY GROUPING SETS(Order_ID, Product_Code)
ORDER BY 1,2;

---

6. 주문처리(EC_Order) 테이블에서 결제자의 결제금액을 이용하여 높은 금액부터 순위를 구하여 [주문자ID, 결제일자, 결제방법, 결제금액, 순위]를 출력하시오.

SELECT Order_ID, CDATE "결제일자", Csel "결제방법", TO_CHAR(CMoney,'L999,999,999') "결제금액",
       DENSE_RANK() OVER(ORDER BY CMoney DESC) "순위"
FROM   EC_Order
WHERE  CDate IS NOT NULL
ORDER BY 4 DESC;

---

7. 수강임시(T_SG_Scores) 테이블에서 성적 취득한 학번별 과목수, 합계와 평균을 계산하여 [학번, 과목수, 총점, 평균]을 총점 내림차순으로 출력하시오.

SELECT Student_ID, COUNT(1) "과목수", SUM(Score) "총점", AVG(Score) "평균"
FROM   T_SG_Scores
WHERE  Score IS NOT NULL
GROUP  BY STudent_ID
ORder  BY 3 DESC;

---

8. 수강(SG_Scores) 테이블의 학번별 총점과 평균을 구하고, 평균으로 석차를 구하여 [학번, 총점, 평균, 석차]를 석차순으로 출력하시오.

SELECT Student_ID, SUM(Score) "총점", TO_CHAR(AVG(Score), '99.9') "평균",
       RANK()  OVER(ORDER BY AVG(Score) DESC) "석차"
FROM   SG_Scores
GROUP  BY Student_ID
ORDER  BY 4;

---

9. 자유게시판(Fress_Board) 테이블의 게시물 행을 수를 출력하고, 게시물의 저장하기 위해 게시판 번호(B_ID)를 구하시오. 단, 게시물 번호는 널이면 1, 그렇지 않으면 게시물번호의 최댓값+1로 한다. (CASE문 사용)

SELECT COUNT(*) "총게시물수" FROM FRee_Board;
SELECT CASE WHEN MAX(B_ID) IS NULL THEN 1
                                    ELSE MAX(B_ID)+1
       END "게시물번호"
FROM   Free_BOARD;