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

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

 

★연습문제1

1. 주문처리(EC_Order) 테이블과 상품관리(EC_Product) 테이블을 이용하여, 'jupark' 회원이 결제한 [주문자 ID, 상품코드, 상품명, 주문 수량, 주문 금액]을 주문자 ID순으로 출력하시오. (등가조인과 내부조인 방법)

①등가조인

SELECT ORDER_ID,P.PRODUCT_CODE, PRODUCT_NAME,ORDER_QTY,CMONEY "주문금액"
FROM   EC_Order P, EC_Product S
WHERE  P.PRODUCT_CODE=S.PRODUCT_CODE
  AND  ORDER_ID='jupark'
ORDER  BY 1;

---

②내부조인(INNER JOIN~USING 또는 INNER JOIN~ON 키워드)

SELECT ORDER_ID,PRODUCT_CODE, PRODUCT_NAME,ORDER_QTY,CMONEY "주문금액"
FROM   EC_Order INNER JOIN EC_Product USING(PRODUCT_CODE)
WHERE  ORDER_ID='jupark'
ORDER  BY 1;

---

2. 주문처리(EC_Order) 테이블과 회원관리(EC_Member) 테이블, 상품관리(EC_Product) 테이블을 이용하여 결제한 [회원명, 전화번호, 주소, 배달상품, 배달수량]을 회원명순으로 출력하시오. (등가조인과 내부조인 방법)

①등가조인

COLUMN 배달상품 FORMAT A14
SELECT NAME, TELEPHONE, ADDRESS, PRODUCT_NAME "배달상품", ORDER_QTY "수량"
FROM   EC_Order P, EC_Member S, EC_Product Y
WHERE  P.ORDER_ID=S.USERID
  AND  P.PRODUCT_CODE=Y.PRODUCT_CODE
  AND  GUBUN ='결제'
ORDER  BY 1;

---

②내부조인(INNER JOIN~USING 또는 INNER JOIN~ON 키워드)

 

SELECT NAME, TELEPHONE, ADDRESS, PRODUCT_NAME "배달상품", ORDER_QTY "수량"
FROM   EC_Order P INNER JOIN EC_Member S
                    ON(P.ORDER_ID=S.USERID)
                  INNER JOIN EC_Product Y
                    ON(P.PRODUCT_CODE=Y.PRODUCT_CODE)
WHERE  GUBUN ='결제'
ORDER  BY 1;

---

3. 상품관리(EC_Product) 테이블과 주문처리(EC_Order) 테이블을 이용하여, 모든 상품을 출력하되 주문 상품의 주문금액(주문 수량 x 단가)을 계산하여 [상품명, 주문 수량, 단가, 주문자 ID, 주문금액]을 주문자ID순으로 2가지 방법에 의하여 출력하시오.

① WHERE절을 이용한 좌 외부조인

SELECT PRODUCT_NAME "주문상품",ORDER_QTY "수량",
       TO_CHAR(UNIT_PRICE,'L99,999,999') "단가",
       ORDER_ID, 
       TO_CHAR(ORDER_QTY*UNIT_PRICE,'L99,999,999') "주문금액"
FROM   EC_PRODUCT P, EC_ORDER C
WHERE  P.Product_CODE=C.PRODUCT_CODE(+)
ORDER  BY 4;

---

② LEFT OUTER JOIN 키워드를 이용한 방법

 

SELECT PRODUCT_NAME "주문상품",ORDER_QTY "수량",
       TO_CHAR(UNIT_PRICE,'L99,999,999') "단가",
       ORDER_ID, 
       TO_CHAR(ORDER_QTY*UNIT_PRICE,'L99,999,999') "주문금액"
FROM   EC_Product LEFT OUTER JOIN EC_Order USING(Product_CODE)
ORDER  BY 4;

---

4. 상품관리(EC_Product) 테이블과 주문처리(EC_Order) 테이블을 이용하여 한 번도 판매되지 않은 상품의 목록 [상품코드, 상품명, 단가, 재고수량]을 상품코드순으로 출력하시오. (외부조인 응용)

SELECT PRODUCT_CODE,PRODUCT_NAME,UNIT_PRICE,LEFT_QTY
FROM   EC_Product
WHERE  PRODUCT_CODE NOT IN (SELECT PRODUCT_CODE
                            FROM   EC_Order)
ORDER BY 1;

---

5. 회원관리(EC_Member) 테이블과 주문처리(EC_Order) 테이블을 참고하여 한 번 도 거래하지 않은 회원의 [회원ID, 회원명, 가입일자]을 출력하시오.

SELECT USERID, NAME, TIMESTAMP
FROM EC_Member
WHERE  USERID NOT IN (SELECT ORDER_ID
                            FROM   EC_Order)
ORDER BY 1;

---

★연습문제2

1. 회원(EC_Member) 테이블과 주문처리(EC_Order) 테이블을 이용하여 한번 이상 거래한 회원의 [회원명, 주민등록번호, 상품명, 주문수량, 주문금액]을 회원명순으로 출력하시오. (내부조인)

SELECT Name, Regist_No, O.Product_Code, Order_Qty, Cmoney
FROM EC_Member M INNER JOIN EC_Order O ON (M.UserID = O.Order_ID) 
ORDER BY 1;

---

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

SELECT Name, Regist_No, Telephone
FROM EC_Member M LEFT OUTER JOIN EC_Order O ON (M.UserID = O.Order_ID) 
WHERE O.Order_ID is NULL
ORDER BY 1;

---

3. 주문처리(EC_Order) 테이블, 회원관리(EC_Member) 테이블, 상품관리(EC_Product) 테이블을 이용하여 거주지가 '서울'인 회원 중에서 한번 이상 주문한 회원의 [회원명, 주민번호, 상품명, 주문수량, 단가, 거주지]를 회원명순으로 출력하시오. (내부 조인)

SELECT Name, Regist_No, Product_Name, Order_Qty, Unit_Price, SUBSTR(Address, 1,2) "거주지"
FROM EC_Member M INNER JOIN EC_Order O ON (M.UserID=O.Order_ID)
                 INNER JOIN EC_Product P ON (O.Product_Code =
P.Product_Code)
WHERE Address LIKE '서울%'
ORDER BY 1;

---

4. 수강임시(T_SG_Scores) 테이블, 과목임시(T_Course) 테이블, 학생(Student) 테이블을 이용하여 'C1801' 학번의 수강신청 과목을 출력하시오. (등가조인)

SELECT Dept_ID, Year, TS.Student_ID, Name, TS. Course_ID, Title, C_Number
FROM   T_SG_Scores TS, Student S, T_Course TC
WHERE  TS.Student_ID = S.Student_ID
  AND  TS.Course_ID = TC. Course_ID
  AND  TS. Student_ID = 'C1801'
ORDER  BY 5;

---

5. 수강임시(T_SG_Scores) 테이블, 과목임시(T_Course) 테이블, 학생(Student) 테이블을 이용하여 'C1801' 학번의 성적을 내부조인(inner join) 방식으로 출력하시오.

SELECT Dept_ID, Year, Student_ID, Name, Course_ID, Title, C_Number, Grade
FROM T_SG_Scores INNER JOIN Student USING (Student_ID)
                 INNER JOIN T_Course USING (Course_ID)
WHERE Student_ID = 'C1801'
ORDER BY 5;

---

6. 수강임시(T_SG_Scores) 테이블과 과목임시(T_Course) 테이블을 이용하여 '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이며, 과목별 평점은 "등급에 대한 평점 x 학점수"로 계산한다.

SELECT Student_ID, Course_ID, Title, C_Number "학점", 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 "등급평점",
        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 * C_Number "과목평점"
FROM    T_SG_Scores INNER JOIN Student USING (Student_ID)
                    INNER JOIN T_Course USING (Course_ID)
WHERE   Student_ID = 'C1802'
ORDER   BY Course_ID;

---

7. 수강임시(T_SG_Scores) 테이블에서 학번별 취득 과목수, 취득 점수의 합계와 평균을 계산하여, [학과코드, 학년, 학번, 성명, 과목수, 총점, 평균]을 총점 내림차순으로 출력하시오. 단, 평균은 소숫점 반올림하여 둘째자리까지 출력한다.

SELECT Dept_ID, Year, Student_ID, Name, Count (1) "과목수", SUM(Score) "총점",
       TO_CHAR(ROUND (AVG(Score), 2), '999.99') "평균"
FROM   T_SG_Scores SG INNER JOIN Student S USING (Student_ID)
WHERE  Score IS NOT NULL
GROUP  BY Dept_ID, Year, Student_ID, Name
Order  BY 3 DESC;

---

8. 학과(Department) 테이블과 교수(Professor) 테이블을 이용하여 직책명(Duty)에 대한 계층적 구조("총장->학과장->교수"순)의 학과별 직위 명단을 출력하시오.

SELECT Dept_Name "소속", decode (Duty,'총장',''
                            ,'학과장','   L______'
                            , NULL, '           L______') || Duty ||
     '  '|| Name || '  ' || Position "직책 및 성명"
FROM Professor P INNER JOIN Department D ON (P.Dept_ID = D.Dept_ID)
START WITH mgr is NULL
CONNECT BY prior Professor_id = Mgr ;

---

9. 수강(SG_Scores) 테이블과 과목(Course) 테이블을 이용하여 한 명이상 수강한 과목의 [과목코드, 과목명, 학점수]를 과목코드순으로 출력하시오. (INTERSECT)

SELECT Course_ID, Title, C_Number
FROM   Course
INTERSECT
SELECT Course_ID, Title, C_Number
FROM   SG_Scores INNER JOIN Course USING (Course_ID)
ORDER BY 1;

---

10. 수강(SG_Scores) 테이블에서 2018학년도 수강 신청한 학생(학번이 'C18'로 시작)의 과목과 2017학년도 수강 신청한 학생(학번이 'C17'로 시작)의 과목중에서 2016학년도 학생(학번이 'C16'이 시작)이 수강하지 않은 과목을 출력하시오.

SELECT Course_ID, Title, C_Number
FROM SG_Scores INNER JOIN Course USING (Course_ID)
WHERE Student_ID LIKE 'C18%'
UNION
SELECT Course_ID, Title, C_Number
FROM SG_Scores INNER JOIN Course USING (Course_ID)
WHERE Student_ID LIKE 'C17%'
MINUS
SELECT Course_ID, Title, C_Number
FROM SG_Scores INNER JOIN Course USING (Course_ID)
WHERE Student_ID LIKE 'C16%'
ORDER BY 1;