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

★연습문제1

1. 회원관리(EC_Member) 테이블의 주민등록번호를 이용하여 성별을 추출하고, 성별이 '1' 또는 '3'일 때 '남자', '2' 또는 '4'일 때 '여자'로 변환하여 성별순으로 출력하시오.

SELECT NAME, REGIST_NO,DECODE(SUBSTR(REGIST_NO,8,1),'1','남','2','여',
                                                    '3','남','4','여')
                                                    AS 성별
FROM EC_MEMBER
ORDER BY 3;

---

2. 회원관리(EC_Member) 테이블을 이용하여 각 회원의 [회원명, 주민등록번호, 나이, 기준년월일]을 출력하시오. 단 , 기준녀월일은 SYSDATE로 한다.

SELECT NAME,REGIST_NO,EXTRACT(YEAR FROM SYSDATE)
       -(DECODE(SUBSTR(REGIST_NO,8,1),'1', '19','2','19','20') || 
       SUBSTR(REGIST_NO,1,2)) +1 AS 나이,
       TO_CHAR(SYSDATE,'YYYY/MM/DD') AS 기준년월일
FROM EC_Member
ORDER BY 3;

---

3. 주문처리(EC_Order) 테이블에서 결제 후 배달하지 않은 상품에 대하여 [주문자ID, 상품코드, 수량, 결제금액, 결제 후 지난 일자, 기준일]을 계산하여 출력하시오. 단, 기준일은 SYSDATE임.

SELECT ORDER_ID, PRODUCT_CODE AS 주문상품,ORDER_QTY AS 수량,
       TO_CHAR(CMONEY,'L99,999,999') AS 결제금액, 
       TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'),'YYYYMMDD') 
       - TO_DATE(TO_CHAR(CDATE,'YYYYMMDD'),'YYYYMMDD') "결제 후 일자",
       TO_CHAR(SYSDATE,'YYYY/MM/DD') AS 기준일
FROM EC_Order
WHERE GUBUN='결제' AND MDATE IS NULL;

---

4. 단가가 100만원 이상인 상품에 대하여 10% 할인하려고 한다. 상품관리(EC_Product) 테이블에서 해당 상품의 10%할인된 금액을 형식에 맞추어 출력하시오.

SELECT PRODUCT_CODE,PRODUCT_NAME,
       TO_CHAR(UNIT_PRICE,'L99,999,999') AS 단가,
       TO_CHAR(UNIT_PRICE*0.9,'L99,999,999') "10%할인된금액"
FROM EC_Product
WHERE UNIT_PRICE>=1000000;

---

5. 주문처리(EC_Order) 테이블의 구분(Gubun) 칼럼 값이 '배달'을 제외하고, '결제'이면 '배달준비', '미결'이면 '결제대기', '취소'이면 '반품', 그렇지 않으면 '대기'로 변경하여 [주문자ID, 상품코드, 결제금액, 결제일자, 구분]의 처리내용을 출력하시오.

SELECT ORDER_ID,PRODUCT_CODE,CMONEY AS 결제금액,TO_CHAR(CDATE,'YY/MM/DD') AS 결제일자,
       DECODE(SUBSTR(GUBUN,1,2),'결제','배달준비'
                                ,'미결','결제대기'
                                ,'취소','반품','대기')AS 처리내용
FROM EC_Order
WHERE NOT GUBUN='배달';

---

★연습문제2

1. 회원관리(EC_Member) 테이블에서 '서울' 거주자에 대하여 회원명의 성씨를 출출하여 [회원명, 성, 전화번호, 주소]를 출력하시오.

SELECT NAME, SUBSTR(NAME,1,1) AS 성,TELEPHONE,ADDRESS
FROM   EC_Member
WHERE Address LIKE '서울%'
ORDER BY 1;

---

2. 상품관리(EC_Product) 테이블에서 모든 컴퓨터 제품의 재고수량을 단위와 결합하여 [상품코드, 상품명, 단위, 재고수량, 생샌처]를 출력하시오. CONCAT()함수

SELECT Product_Code,Standard, CONCAT(Left_Qty,Unit) "재고수량",
Company
FROM Ec_product
WHERE Product_Name LIKE '%컴퓨터%';

---

3. 주문처리(EC_Order) 테이블에서 결제금액이 100만원 이상 결제한 주문자의 결제일자(CDATE를 년월('YYYY/MM') 형식으로 변환하여 출력하시오.

SELECT Order_ID, Product_Code, CMoney,CDate "결제일자",
       TO_CHAR(Cdate, 'YYYY/MM') "결제년월"
FROM   EC_Order
WHERE  CDATE IS NOT NULL AND CMONEY >=1000000
ORDER  BY CDate;

---

4. 회원관리(EC_Member) 테이블에서 현재일자(SYSDATE) 기준으로 회원가입기간을 'xx년 xx월'로 계산하여 1년 이상인 회원을 출력하시오.

SELECT Name, REGIST_NO, TimeStamp "가입일자",
       Trunc(MONTHS_Between(Current_DATE,Timestamp) /12) || '년' ||
       MOD(Trunc(MONTHS_Between(Current_Date,Timestamp)),12)||'월' AS 가입기간,
       CURRENT_DATE "기준일자"
FROM   EC_MEMBER
WHERE  Trunc(MONTHS_Between(Current_Date, Timestamp)/12) >=1
ORDER  BY 1;

---

5. 주문처리(EC_Order) 테이블에서 결제한 회원중 '신용카드'로 결제한 회원에 대하여 EXTRACT() 함수를 이용하여 결제년도와 결제 월을 추출해 보시오.

SELECT Order_ID, CMoney, Csel, Cdate,
       ExTRACT(YEAR FROM CDATE) "결제년도",
       EXTRACT(MONTH FROM CDATE) "결제 월"
FROM   EC_Order
WHERE  CDATE IS NOT NULL AND Csel='신용카드'
ORDER  BY 4;

---

6. 수강임시(T_SG_Scores) 테이블에서 CASE 함수를 이용하여 등급을 산출하고, [학번, 과목코드, 성적, 등급, 성적취득일자]를 출력하시오.

UPDATE T_SG_Scores
SET    GRADE = CASE WHEN Score BETWEEN 95 AND 100 THEN 'A+'
                    WHEN Score BETWEEN 90 AND 94  THEN 'A'
                    WHEN Score BETWEEN 85 AND 89  THEN 'B+'
                    WHEN Score BETWEEN 80 AND 84  THEN 'B'
                    WHEN Score BETWEEN 75 AND 79  THEN 'C+'
                    WHEN Score BETWEEN 70 AND 74  THEN 'C'
                    WHEN Score BETWEEN 65 AND 69  THEN 'D+'
                    WHEN Score BETWEEN 60 AND 64  THEN 'D'
                                                  ELSE 'F'
                    END
WHERE  GRADE IS NULL;

SELECT*FROM T_SG_SCores;
COMMIT;

---

7. 수강임시(T_SG_Scroes)테이블에서 'C1801'학번의 성적을 5로 나누어 몫과 나머지를 계산하여 [학번, 과목코드, 성적, 몫, 나머지]를 출력하시오.

SELECT Student_ID, Course_ID, Score, Floor(Score/5)"몫",MOD(Score,5)"나머지"
FROM   T_SG_Scores
WHERE  Student_ID='C1801'
ORDER  BY 3 DESC;

---

8. 수강임시(T_SG_Scores) 테이블에서 CASE 함수를 이용하여 'C1801' 학번의 등급이 'A+'이면 4.5, 'A'이면 4.0, 'B+'이면 3.5, 'B '이면 3.0, 'C+'이면 2.5, 'C'이면 2.0, 'D+'이면 1.5, 'D'이면 1.0, 'F'이면 0.0을 출력하시오.

SELECT Student_ID, Course_ID, Grade,
       CASE Grade When 'A+' THEN '4.5' WHEN 'A ' THEN '4.0'
                  When 'B+' THEN '3.5' WHEN 'B ' THEN '3.0'
                  When 'C+' THEN '2.5' WHEN 'C ' THEN '2.0'
                  When 'D+' THEN '1.5' WHEN 'D ' THEN '1.0'
                            ELSE '0.0'
       END "평점"
FROM   T_SG_Scores
WHERE  Student_ID='C1801';

---

9. 과목임시(T_Course) 테이블에서 추가수강료(Course_fees)가 널인 행에 대하여 널 값을 0으로 변환하여 출력하시오.

SELECT Course_ID, Title,C_Number,Professor_ID, NVL(Course_fees,0)
FROM   T_Course
WHERE  Course_fees IS NULL;

---

10. 기준일자(CURRENT_DATE)에 5일을 더하고, 기준시간에 4시간을 더하여 출력하시오.

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

SELECT CURRENT_DATE "기준일자와 기준시간", CURRENT_DATE+5 "5일 후",
       CURRENT_DATE+4/24 "4시간 후"      FROM DUAL;

---

11. 상품관리(EC_Product)테이블에서 상품명을 '개인용컴퓨터', '노트북컴퓨터', '프린터', 'TV', 기타 출력순서로 1순위, 단가 내림차순을 2순위로 출력하시오.

SELECT Product_Code, Product_Name, Unit_Price, Company
FROM   EC_Product
ORDER  BY CASE Product_Name WHEN '개인용컴퓨터' THEN 1
                            WHEN '노트북컴퓨터' THEN 2
                            WHEN '프린터'      THEN 3
                            WHEN 'TV'         THEN 4
                                              ELSE 5
          END, 3 DESC;