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

★연습문제1

1. 상품관리(EC_Product) 테이블에서 단가(Unit_price)가 평균 단가보다 높은 상품에 대하여 [상품코드, 상품명, 단가]를 출력하시오.

SELECT PRODUCT_CODE, PRODUCT_NAME, UNIT_PRICE
FROM   EC_Product
WHERE  UNIT_PRICE > (SELECT AVG(UNIT_PRICE)
                     FROM   EC_Product);

---

2. 주문처리(EC_Order) 테이블로부터 입금액이 제일 많은 [주문자ID, 상품코드, 주문수량, 결제방법, 입금액]을 출력하시오.

SELECT ORDER_ID, PRODUCT_CODE, ORDER_QTY,CSEL,CMONEY
  FROM EC_Order
 WHERE CMONEY = (SELECT MAX(CMONEY)
                   FROM EC_Order);

---

3. 주문처리(EC_Order) 테이블에서 결제종류별 입금한 금액이 제일 많은 행에 대하여 [결제종류, 주문자ID, 입금액, 입금일자]를 입금액순으로 출력하시오.

SELECT CSEL "결제종류",ORDER_ID, CMONEY "입급액", CDATE "입급일자"
FROM   EC_Order
WHERE  CMONEY IN (SELECT MAX(CMONEY)
                   FROM EC_Order
                   GROUP BY CSEL);

---

4. 주문처리(EC_Order)테이블에서 한 번 이상 거래한 회원의 [회원 ID, 회원명, 주민등록번호, 전화번호]를 회원명순으로 출력하시오. 

SELECT USERID, NAME,REGIST_NO,TELEPHONE
FROM   EC_Member M
WHERE  EXISTS (SELECT *
                   FROM   EC_Order O
                   WHERE  M.USERID= O.ORDER_ID)
ORDER BY 2;

---

5. 회원관리(EC_Member) 테이블과 주문처리(EC_Order) 테이블을 이용하여 한 번도 거래하지 않은 회원의 [회원ID, 회원명, 주민등록번호, 전화번호]를 회원명순으로 출력하시오.

SELECT USERID, NAME,REGIST_NO,TELEPHONE
FROM   EC_Member M
WHERE  NOT EXISTS (SELECT *
                   FROM   EC_Order O
                   WHERE  M.USERID= O.ORDER_ID)
ORDER BY 2;

---

6. 장바구니(EC_Basket) 테이블의 [주민번호, 주문자 ID, 상품코드, 주문수량, 주문일자]를 출력하여 확인한 모든 행을 주문처리(EC_Order) 테이블에 저장하고, 장바구니(EC_Basket) 테이블의 모든 행을 영구히 삭제하시오.

SELECT ORDER_NO, ORDER_ID, PRODUCT_CODE, ORDER_QTY, ORDER_DATE
FROM  EC_BASKET;

INSERT INTO EC_Order(ORDER_NO,ORDER_ID,PRODUCT_CODE,ORDER_QTY,CDATE)
SELECT*
FROM  EC_BASKET;

SELECT ORDER_NO, ORDER_ID, PRODUCT_CODE,ORDER_QTY, CMONEY
FROM   EC_Order
WHERE  CMONEY IS NULL;

DELETE FROM EC_Basket;

commit;

---

7. 주문처리(EC_Order) 테이블의 결제금액(cmoney)이 널인 행에 대하여 주문 수량(Order_Qty)과 상품관리(EC_Product) 테이블의 단가(Unit_Price)를 이용하여 주문처리(EC_Order) 테이블의 주문금액(주문수량 x 단가)을 계산하여 저장하고, 구분(Gubun) 칼럼에 '미결'로 수정하시오. (서브 쿼리를 사용한 UPDATE문)

UPDATE EC_Order O
SET    CMONEY =ORDER_QTY*(SELECT UNIT_PRICE
                          FROM   EC_Product P
                          WHERE  O.PRODUCT_CODE=P.PRODUCT_CODE)
       ,Gubun='미결'
WHERE  CMONEY IS NULL;

select ORDER_NO, PRODUCT_CODE, ORDER_QTY, CMONEY, GUBUN
from   EC_Order
WHERE  Gubun='미결';

commit;

---

8. 상품관리 테이블(EC_Product)에서 단가가 높은 상위 상품 TOP-5에 대하여 [순위, 상품코드, 상품명, 단가, 생산처]를 출력하시오. (인라인 뷰)

SELECT ROWNUM "순위", a.*
FROM (SELECT Product_CODE, Product_Name, UNIT_PRICE,COMPANY
      FROM   EC_Product
      ORDER  BY 3 DESC)a
WHERE ROWNUM<=5;

---

9. 상품관리 테이블(EC_Product)에서 단가가 높은 상위 상품 11위부터 15위까지 [num, 상품코드, 상품명, 단가, 생산처]를 출력하시오. (인라인 뷰)

SELECT*
FROM (SELECT ROWNUM num, a.*
      FROM (SELECT Product_CODE, Product_Name, UNIT_PRICE,COMPANY
            FROM   EC_Product
            ORDER  BY 3 DESC)a
            )
WHERE num BETWEEN 11 AND 15;

---

10. 주문처리(EC_Order) 테이블과 동일한 Empty_Order 빈 테이블을 생성하고, Empty_Order 테이블의 구조를 확인하시오.

CREATE TABLE Empty_Order
AS
       SELECT *
       FROM   EC_Order
       WHERE  1=2;
       
DESC Empty_Order

---

★연습문제2

1. 주문처리(EC_Order) 테이블로부터 'jupark'가 주문한 상품과 동일한 상품의 구매지수를 출력하시오. (스칼라 서브 쿼리 이용)

SELECT Order_ID, Product_Code, (SELECT COUNT(*)
                                FROM EC_Order A
                                WHERE A.Product_code=B.Product_code
                                ) "동일상품_구매자수"
FROM   EC_Order B                                
WHERE  Order_ID = 'jupark';

---

2. 주문처리(EC_Order) 테이블에서 구분(Gubun) 칼럼이 '배달' 또는 '결제' 행에 대하여 2018년 1월 1일부터 2018년 7월 30일까지 주문자ID별 결젲 합계 금액을 회원관리(EC_Member) 테이블의 구매실적(BuyCash)칼럼에 합하여 저장하시오.

UPDATE EC_Member M
SET    BuyCash = BuyCash +
                 (SELECT SUM(CMoney)
                  FROM EC_Order O
                  WHERE O.Order_ID= M.UserID
                  AND CDate BETWEEN '2018/01/01' AND '2018/07/30'
                  AND Gubun IN ('배달', '결제'));

SELECT UserID, Name, TO_CHAR(BuyCash, 'L99,999,999')
FROM   EC_Member
WHERE  Buycash IS NOT NULL;

---

3. 주문처리(EC_Order) 테이블과 상품관리(EC_Product) 테이블, 회원관리(EC_Member) 테이블을 이용하여 결제합계금액(Cmoney)이 가장 많은 금액중 상위 5명에 대한 (회원명, 결제합계금액)을 출력하시오. (인라인 뷰 이용)

SELECT ROWNUM "순위", a.*
FROM  (SELECT Name "회원명", SUM(CMoney) "결제합계금액"
       FROM EC_Order O INNER JOIN EC_Member M ON O.Order_ID= M.UserID
       GROUP BY Name
       ORDER BY 2 DESC ) a
WHERE  ROWNUM <= 5;

---

4. 주문처리(EC_Order) 테이블과 상품관리(EC_Product) 테이블을 이용하여 최근에 주문 결제한 상위자 5명에 대하여 (순위, 상품코드, 상품명, 주문수량, 결제방법, 결제금액, 결제일자)을 출력하시오. (인라인 뷰 이용)

SELECT ROWNUM "순위", a.*
FROM  (SELECT Product_Code, Product_Name, Order_QTY, Csel, CMoney, CDate
       FROM EC_Order INNER JOIN EC_Product USING (Product_Code)
       WHERE CMoney IS NOT NULL AND CDate IS NOT NULL
       ORDER BY CDate DESC ) a
WHERE  ROWNUM <= 5;

---

5. 과목임시(T_Course) 테이블에서 추가수강료(Course_fees)의 평균값을 계산하여 출력하고, 추가 수강료가 평균 수강료보다 높은 과목을 출력하시오.

SELECT AVG(Course_Fees) FROM T_Course;

SELECT *
FROM Course
WHERE Course_Fees >= (SELECT AVG(Course_Fees) FROM Course)
ORDER BY 1;

---

6. 과목임시(T_Course) 테이블에서 과목코드가 'L1'로 시작하는 과목중에서 추가 수강료가 가장 낮은 금액보다 많은 과목을 추가수강료 역순으로 출력하시오.

SELECT *
FROM  T_Course
WHERE Course_ID LIKE 'L1%'
AND   Course_fees > ANY (SELECT Course_fees
                       FROM T_Course)
ORDER BY Course_Fees Desc;

---

7. 수강임시(T_SG_Scores) 테이블에서 성적취득일자가 2018년 6월에 과목코드 별 최고점을 받은 과목의 [과목코드, 학번, 성적, 성적취득일자]를 과목코드 순으로 출력하시오.

SELECT Course_ID, Student_ID, Score, Score_Assigned
FROM   T_SG_Scores
WHERE (Course_ID, Score) IN (SELECT Course_ID, MAX(Score)
                             FROM T_SG_Scores
                             WHERE TO_CHAR(Score_Assigned, 'YY/MM') = '18/06'
                             GROUP BY Course_ID)
ORDER BY Course_ID;

---

8. 과목임시(T_Course) 테이블과 수강임시(T_SG_Scores) 테이블을 이용하여 한 명 시아 수강한 과목을 [과목코드, 과목명, 학점수, 담당교수번호, 추가수강료]를 과목코드순으로 출력하시오.

SELECT *
FROM T_Course C
WHERE EXISTS (SELECT *
              FROM T_SG_Scores SG
              WHERE SG.Course_ID = C. Course_ID);

---

9. 과목임시(T_Course) 테이블과 수강임시(T_SG_Scores) 테이블을 이용하여 한번도 수강하지 않은 과목을 [과목코드, 과목명, 학점수, 담당교수번호, 추가수강료]를 과목코드순으로 출력하시오.

SELECT *
FROM   T_Course C
WHERE NOT EXISTS(SELECT *
                 FROM T_SG_Scores SG
                 WHERE SG.Course_ID = C.Course_ID)
ORDER BY 1;

---

10. 수강(SG_Scores) 테이블을 이용하여 2018학년도에 성적을 취득한 행들을 저장하는 2018학년도 성적(SG_Score_2018) 테이블을 동일한 구조로 생성하고, 저장된 행들을 학번, 과목코드순으로 출력하시오.

CREATE TABLE SG_Score_2018
AS
   SELECT *
   FROM   SG_Scores
   WHERE  TO_CHAR(Score_Assigned, 'YY') = '18'
     AND  Score IS NOT NULL;

SELECT Student_ID, Course_ID, Score, Grade, Score_Assigned
FROM   SG_Score_2018
ORDER  BY 1, 2;

---

11. 과목(Course) 테이블에서 추가 수강료가 널인 행을 수강임시(T_Course) 테이블로 복사하시오.

INSERT INTO T_Course
SELECT *
FROM Course
WHERE Course_Fees IS NULL;

---

12. 수강(SG_Scores) 테이블과 학생(Student) 테이블을 이용하여 '컴공' 학과의 평균 성적 상위자 3명을 출력하되, 취득과목수가 최소한 3과목 이상인 학생의 (순위, 학과코드 ,학번, 서ㅕㅇ명, 취득과목수, 평균점수)를 출력하시오. 단 평균은 소숫점 2자리까지 반올림하여 출력함.

SELECT ROWNUM "순위", a.*
FROM (SELECT Dept_ID, student_ID, Name, COUNT(Course_ID), ROUND(AVG(Score),2) "평균"
      FROM SG_Scores INNER JOIN Student USING (Student_ID)
      GROUP BY dept_ID, Student_ID, Name
      HAVING COUNT(Course_ID) > 3
      ORDER BY 5 DESC )a
WHERE ROWNUM <= 3;

---

13. 수강(SG_Scores) 테이블로부터 과목별 등급 인원수를 피벗 테이블을 이용하여 출력하시오. 등급은 'A','A ','B+','B ','C+','C ',,'D+','D ','F '로 구분함

SELECT *
FROM (SELECT Title, Grade
      FROM SG_Scores INNER JOIN Course USING (Course_ID) )
PIVOT (
       COUNT(*)
       FOR grade IN ('A+', 'A', 'B+', 'B', 'C+', 'C', 'D+', 'D', 'F ')
       )
ORDER BY 1;