Sql 연습 문제③

< Book Table >

bookid	bookname	publisher	price
1	축구의 역사	굿스포츠	7000
2	축구아는 여자	나무수	13000
3	축구의 이해	대한미디어	22000
4	골프 바이블	대한미디어	35000
5	피겨 교본	굿스포츠	8000
6	역도 단계별기술	굿스포츠	6000
7	야구의 추억	이상미디어	20000
8	야구를 부탁해	삼성당	7500
10	Olympic Champions	Pearson	13000

< Cumtomer Table >

custid	name	address	phone
1	박지성	영국 맨체스타	000-5000-0001
2	김연아	대한민국 서울	000-6000-0001
3	장미란	대한민국 강원도	000-7000-0001
4	추신수	미국 클리블랜드	000-8000-0001
5	박세리	대한민국 대전	NULL

< Orders Table >

orderid	custid	bookid	saleprice	orderdate
1	1	1	6000	2020-07-01
2	1	3	21000	2020-07-03
3	2	5	8000	2020-07-03
4	3	6	6000	2020-07-04
5	4	7	20000	2020-07-05
6	1	2	12000	2020-07-07
7	4	8	13000	2020-07-07
8	3	10	12000	2020-07-08
9	2	10	7000	2020-07-09
10	3	8	13000	2020-07-10

※ 해당 교재에 해설 및 풀이가 없으므로, 제가 푼 풀이가 틀릴 수도 있습니다.

 

1. 대한민국에 거주하는 고객의 고객명,주소,연락처를 보이는 프로시저를 작성하고 동작됨을 보이시오.

--프로시저 생성
CREATE OR REPLACE PROCEDURE GET_KR_USER_PRC(rc OUT SYS_REFCURSOR) 
AS 
BEGIN 
OPEN rc FOR
   SELECT NAME,
         ADDRESS,
         PHONE
     FROM CUSTOMER c  WHERE ADDRESS LIKE '%대한민국%';
END GET_KR_USER_PRC;

--동작 확인
DECLARE
   rc  SYS_REFCURSOR;
   NAME CUSTOMER.NAME%TYPE;
   ADDRESS CUSTOMER.ADDRESS%TYPE;
   PHONE CUSTOMER.PHONE%TYPE;
BEGIN
   GET_KR_USER_PRC(rc);
   LOOP
    FETCH rc INTO NAME, ADDRESS, PHONE;
    EXIT
     WHEN rc%notfound;
    dbms_output.put_line(NAME || ',' || ADDRESS || ',' || PHONE);
  END LOOP;
END;

 

2. 도서 테이블에 동일도서(출판사,책이름이 동일)가 있으면 도서의 가격만 새롭게 변경하고 그렇지 않으면 새로운 행을 삽입하는 프로시저를 작성하고 동작됨을 보이시오.

--프로시저 생성
CREATE OR REPLACE PROCEDURE BOOK_PRICE_PRC(in_publisher IN BOOK.PUBLISHER%TYPE,
in_bookname IN BOOK.BOOKNAME%TYPE,
in_price BOOK.PRICE%TYPE)
AS d_book_id BOOK.BOOKID%TYPE;
d_max_book_id BOOK.BOOKID%TYPE;
   BEGIN
   BEGIN
   SELECT bookid INTO d_book_id
     FROM BOOK b  
    WHERE b.BOOKNAME = in_bookname
      AND b.PUBLISHER = in_publisher;
   EXCEPTION
    WHEN NO_DATA_FOUND THEN
       d_book_id := 0;
    END;
   BEGIN
    SELECT MAX(bookid)+1 INTO d_max_book_id
     FROM BOOK b;
   EXCEPTION
    WHEN NO_DATA_FOUND THEN
       d_max_book_id := 1;
   END;
   IF(d_book_id = 0)THEN
      INSERT INTO BOOK (BOOKID, BOOKNAME, PUBLISHER, PRICE)
      VALUES(d_max_book_id, in_bookname, in_publisher, in_price);
   ELSE
      UPDATE BOOK
      SET  PRICE=in_price
      WHERE BOOKID=d_book_id;
   END IF;
END BOOK_PRICE_PRC;

--동작 확인
SELECT * FROM BOOK;
call BOOK_PRICE_PRC('test3','test2',4000);
call BOOK_PRICE_PRC('대한미디어','축구의 이해',1000);
SELECT * FROM BOOK;

 

3. 특정 고객의 주문 총액을 계산하여 3만원 이상이면 ‘우수’, 3만원 미만이면 ‘일반’을 반환하는 함수 grade()를 작성하고 동작됨을 보이시오.

CREATE OR REPLACE FUNCTION grade(v_custid NUMBER)
    RETURN VARCHAR
IS
    v_grade varchar(100);
BEGIN
   SELECT CASE WHEN SUM(o.SALEPRICE) >= 30000
         THEN '우수'
         ELSE '일반'
         END INTO v_grade
      FROM ORDERS o 
    WHERE o.CUSTID=v_custid;

  RETURN v_grade;
END;

SELECT grade(2) AS grade
FROM dual;

 

4. 3번문에에서 작성한 사용자정의 함수 grade()를 호출하여 고객의 이름과 등급을 보이는 SQL문을 작성하고 실행결과를 보이시오.

SELECT c.NAME,
      grade(c.CUSTID) AS grade
  FROM CUSTOMER c;

 

 5. 고객별로 도서를 몇 권 구입 했는지와 총구매액을 보이는 프로시저를 작성하고 동작됨을 보이시오.

 --프로시저 생성
CREATE OR REPLACE PROCEDURE GET_BOOK_ORDER_BYCUST_PRC(rc OUT SYS_REFCURSOR) 
AS 
BEGIN 
OPEN rc FOR
   SELECT o.CUSTID,
        COUNT(1) AS BOOK_COUNT,
        SUM(o.SALEPRICE) AS SALEPRICE 
   FROM ORDERS o 
  GROUP BY o.CUSTID ;
END GET_BOOK_ORDER_BYCUST_PRC;

--동작 확인
DECLARE
   rc  SYS_REFCURSOR;
   BOOK_COUNT number;
   CUSTID number;
   SALEPRICE number;
BEGIN
   GET_BOOK_ORDER_BYCUST_PRC(rc);
   LOOP
    FETCH rc INTO CUSTID, BOOK_COUNT, SALEPRICE;
    EXIT
     WHEN rc%notfound;
    dbms_output.put_line(CUSTID || ',' || BOOK_COUNT || ',' || SALEPRICE);
  END LOOP;
END;

 

6. 고객이 주문한 내용의 변경이 발생할 경우 로그테이블에 변경전 변경후 데이터를 기록하는 트리거를 작성하고 동작됨을 보이시오.

CREATE TABLE ORDERS_LOG
   (   
    "NO" NUMBER(10,0) PRIMARY KEY,
    "ORDERID" NUMBER(2,0), 
   "BEFORE_CUSTID" NUMBER(2,0), 
   "AFTER_CUSTID" NUMBER(2,0), 
   "BEFORE_BOOKID" NUMBER(2,0), 
   "AFTER_BOOKID" NUMBER(2,0), 
   "BEFORE_SALEPRICE" NUMBER(8,0), 
   "AFTER_SALEPRICE" NUMBER(8,0), 
   "BEFORE_ORDERDATE" DATE,
   "AFTER_ORDERDATE" DATE,
   "UPDATE" DATE
   ) ;
  
  --시퀀스 생성 
CREATE SEQUENCE ORDERS_LOG_NO_SEQ INCREMENT BY 1 MINVALUE 0 NOCYCLE NOCACHE NOORDER;

--트리거 생성
CREATE OR REPLACE TRIGGER c##sql3.ORDERS_UPDATE
AFTER UPDATE 
ON ORDERS
FOR EACH ROW
BEGIN
   INSERT INTO ORDERS_LOG
   (
   "NO", 
   ORDERID, 
   BEFORE_CUSTID, 
   AFTER_CUSTID, 
   BEFORE_BOOKID, 
   AFTER_BOOKID, 
   BEFORE_SALEPRICE, 
   AFTER_SALEPRICE, 
   BEFORE_ORDERDATE, 
   AFTER_ORDERDATE, 
   "UPDATE")
   VALUES(
   ORDERS_LOG_NO_SEQ.nextval, 
   :OLD.ORDERID, 
   :OLD.CUSTID, 
   :NEW.CUSTID, 
   :OLD.BOOKID, 
   :NEW.BOOKID, 
   :OLD.SALEPRICE, 
   :NEW.SALEPRICE, 
   :OLD.ORDERDATE, 
   :NEW.ORDERDATE, 
   SYSDATE);
END;

UPDATE ORDERS
SET CUSTID=5, BOOKID=6, SALEPRICE=13000, ORDERDATE=TIMESTAMP '2022-07-03 00:00:00.000000'
WHERE ORDERID=11;

SELECT * FROM ORDERS_LOG;

 

7. 그룹함수를 이용하여 출판사별 고객별 판매액을 소계와 총계가 보이도록 DML문을 작성하고 실행해 보이시오. 

SELECT b.PUBLISHER, o.CUSTID, SUM(o.SALEPRICE) AS PRICE
  FROM ORDERS o 
  LEFT JOIN BOOK b ON o.BOOKID = b.BOOKID 
 GROUP BY ROLLUP(b.PUBLISHER, o.CUSTID) ;

 

 8. 윈도우 함수를 이용하여 출판사별 총판매금액, 판매순위를 산출하는 DML문 작성하고 실행해 보이시오.

SELECT b.PUBLISHER, SUM(o.SALEPRICE) AS PRICE,
RANK() OVER (ORDER BY SUM(o.SALEPRICE) DESC) AS RANKING
  FROM ORDERS o 
  LEFT JOIN BOOK b ON o.BOOKID = b.BOOKID 
 GROUP BY b.PUBLISHER ;

Reference : 오라클로 배우는 데이터베이스 개론과 실습

https://www.hanbit.co.kr/store/books/look.php?p_code=B2082711299

IT CookBook, 오라클로 배우는 데이터베이스 개론과 실습(2판)