[과제] select 연습문제 풀이

1. 연습문제

 

2. 테이블 정보

BOOK 테이블

CUSTOMER 테이블

IMPORTED_BOOK 테이블

ORDERS 테이블

 

3. 결과 

/*1-5*/
--박지성이 구매한 도서의 출판사 수
SELECT COUNT(PUBLISHER)
FROM BOOK 
WHERE BOOKID IN(
SELECT BOOKID
FROM ORDERS O
WHERE O.CUSTID = (SELECT CUSTID 
         			FROM CUSTOMER 
        		   WHERE NAME = '박지성'
      			 ) -- 박지성의 CUSTID를 단일쿼리로 추출한다.
               )

/*1-6*/
--박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이
--박지성 정보는 CUSTOMER/ 도서이름,가격은 BOOK/ 판매가격은 ORDERS에서 가져올 수 있다.
--그래서 위 3개 테이블을 조인했다.
--데이터가 뻥튀기 되지않도록 ON조건을 줬다.
SELECT B.BOOKNAME
     , B.PRICE
     , (B.PRICE - O.SALEPRICE) AS DIFFER_PRICE
  FROM BOOK B
  JOIN ORDERS O  
    ON B.BOOKID = O.BOOKID
  JOIN CUSTOMER C 
    ON O.CUSTID = C.CUSTID 
   AND C.NAME = '박지성';
   
/*1-7*/
--박지성이 구매하지 않은 도서의 이름
--전체 BOOKNAME에서 박지성이 구매하지 않은 BOOKNAME을 MINUS해줬다.
SELECT BOOKNAME
  FROM BOOK
 MINUS 
 SELECT B.BOOKNAME
  FROM BOOK B
  JOIN ORDERS O 
    ON B.BOOKID = O.BOOKID
  JOIN CUSTOMER C 
    ON O.CUSTID = C.CUSTID 
   AND C.NAME = '박지성'
   
/*2-8*/
--주문하지 않은 고객의 이름(서브쿼리 사용할 것)
--DISTINCT로 주문고객이름의 중복을 제거하여 조회했다.
--NOT IN조건을 사용하여 주문하지 않은 고객이름을 추출했다.
SELECT NAME
FROM CUSTOMER c
WHERE c.CUSTID NOT IN (SELECT DISTINCT CUSTID FROM ORDERS);

/*2-9*/
--주문 금액의 총액과 주문의 평균 금액
SELECT SUM(SALEPRICE) AS SUM_PRICE
       ,AVG(SALEPRICE) AS AVG_PRICE
  FROM ORDERS
  
/*2-10*/
--고객의 이름과 고객별 구매액
--고객의 이름은 CUSTOMER/ 고객의 구매액은 ORDERS에서 가져올 수 있다.
--따라서, 2개 테이블을 JOIN했다.
SELECT C.NAME
     , SUM(O.SALEPRICE) AS CUSTOMER_PRICE
  FROM CUSTOMER C 
      ,ORDERS O
 WHERE C.CUSTID = O.CUSTID
 GROUP BY C.NAME
 
/*2-11*/
--고객 이름과 고객이 구매한 도서 목록
--서브쿼리를 사용하여 고객이 구매한 도서목록을 조회 했다.
--고객 이름은 CUSTOMER/ 고객이 구매는 ORDERS / 도서목록은 BOOK에서 조회가능하다.
--따라서, 3개 테이블을 JOIN했다.
--고객이 구매한 BOOKID를 IN조건으로 썼다.
SELECT CU.NAME
       ,B.BOOKNAME 
  FROM BOOK B
, CUSTOMER CU 
, ORDERS OD
 WHERE B.BOOKID IN(
                  SELECT BOOKID
                    FROM ORDERS O
                   WHERE O.CUSTID = (SELECT CUSTID 
                                    FROM CUSTOMER C
                                   WHERE C.CUSTID = O.CUSTID
                                 )
                  )
  AND B.BOOKID = OD.BOOKID 
  AND CU.CUSTID = OD.CUSTID 
ORDER BY CU.NAME

/*2-12*/
--도서의 가격과 판매가격의 차이가 가장 많은 주문 (MAX)
--BOOK테이블의 PRICE와 ORDERS테이블의 SALEPRICE의 차이에 MAX함수를 써서 MAX값을 뽑았다.
--무엇이 잘못된건지 MAX값만 추출되는게 아니라서, ORDER BY로 MAX값을 최상단으로 두고
--ROWNUM(오라클내장컬럼)값이 1인 것을 추출했다.
--괄호안에 두개 컬럼을 넣고 IN 조건에 두개 컬럼이 조회되도록 하면 IN 조건을 다중으로 쓸 수 있다.
SELECT * 
  FROM ORDERS OD
 WHERE (OD.BOOKID, OD.CUSTID) IN (
                                SELECT BOOKID, CUSTID
                                  FROM (
                                        SELECT B.BOOKID
                                             , O.CUSTID
                                             , MAX(B.PRICE - O.SALEPRICE) AS MAXDIFFER_PRICE
                                          FROM BOOK B
                                          JOIN ORDERS O ON B.BOOKID = O.BOOKID
                                          JOIN CUSTOMER C ON O.CUSTID = C.CUSTID
                                         GROUP BY B.BOOKID, O.CUSTID
                                         ORDER BY MAXDIFFER_PRICE DESC
                                        )
                                WHERE ROWNUM = 1 
                                 );

/*2-13*/
--도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름
--도서의 판매액 평균을 단일값이 나오도록 쿼리를 짰고 그 값에 상관서브쿼리를 비교해주었다.
SELECT NAME
  FROM CUSTOMER c
 WHERE CUSTID IN (
                SELECT CUSTID
                FROM ORDERS OD
                WHERE (SELECT AVG(SALEPRICE)
                       FROM ORDERS 
                      ) < (SELECT AVG(SALEPRICE)
                             FROM ORDERS SO
                            WHERE OD.CUSTID = SO.CUSTID
                          ) 
                  )

 

※내가 푼 것이 정답이 아닐 수 있다.

SQL 쿼리 작성을 할 때는 성능을 고려해야한다.

아직 최적화 실력이 부족하여 성능보단 결과가 나오는 것에 집중했다.

 

4. 채점 및 개선

chatGPT를 사용해서 채점 및 개선쿼리를 확인했습니다.

 

1. 쿼리 1-5: 출판사의 수

SELECT COUNT(DISTINCT B.PUBLISHER) 
FROM BOOK B 
JOIN ORDERS O ON B.BOOKID = O.BOOKID 
JOIN CUSTOMER C ON O.CUSTID = C.CUSTID 
WHERE C.NAME = '박지성';

설명

• 개선된 쿼리는 JOIN을 사용하여 중첩된 서브쿼리를 제거합니다. 성능과 가독성이 향상됩니다.

2. 쿼리 1-6: 가격 차이

원래 쿼리와 개선된 쿼리는 동일합니다. 이 쿼리는 이미 효율적입니다.

 

3. 쿼리 1-7: 구매하지 않은 책

개선된 쿼리 없음 - 이 쿼리는 이미 최적화되어 있습니다. MINUS 연산은 특정 상황에서 필요하며, 이 경우에 적절합니다.

 

4. 쿼리 2-8: 테이블 조회

원래 쿼리와 개선된 쿼리는 동일합니다. 이 쿼리들은 단순한 테이블 조회를 위한 것이므로, 이미 최적화되어 있습니다.

 

5. 쿼리 2-9: 주문의 합계와 평균

원래 쿼리와 개선된 쿼리는 동일합니다. 이 쿼리는 이미 효율적입니다.

 

6. 쿼리 2-10: 고객별 구매액

원래 쿼리와 개선된 쿼리는 동일합니다. 이 쿼리는 이미 효율적입니다.

 

7. 쿼리 2-11: 고객이 구매한 도서 목록

개선된 쿼리

SELECT C.NAME, B.BOOKNAME 
FROM CUSTOMER C 
JOIN ORDERS O ON C.CUSTID = O.CUSTID 
JOIN BOOK B ON O.BOOKID = B.BOOKID 
ORDER BY C.NAME;

 

설명

• 개선된 쿼리는 명시적인 JOIN을 사용하여 가독성을 향상시키고 중첩된 서브쿼리를 제거합니다.

8. 쿼리 2-12: 최대 가격 차이 주문

원래 쿼리와 개선된 쿼리는 동일합니다. 이 쿼리는 이미 복잡한 로직을 효율적으로 처리하고 있습니다.

 

9. 쿼리 2-13: 평균 구매액이 높은 고객

원래 쿼리

SELECT NAME
  FROM CUSTOMER c
 WHERE CUSTID IN (
                SELECT CUSTID
                FROM ORDERS OD
                WHERE (SELECT AVG(SALEPRICE)
                       FROM ORDERS 
                      ) < (SELECT AVG(SALEPRICE)
                             FROM ORDERS SO
                            WHERE OD.CUSTID = SO.CUSTID
                          ) 
                  )

 

개선된 쿼리

SELECT C.NAME 
FROM CUSTOMER C 
JOIN 
( SELECT O.CUSTID FROM ORDERS O GROUP BY O.CUSTID HAVING AVG(O.SALEPRICE) > 
( SELECT AVG(SALEPRICE) FROM ORDERS ) ) AS HC
ON C.CUSTID = HC.CUSTID;​

설명

• 개선된 쿼리는 JOIN과 HAVING 절을 사용하여 중첩된 서브쿼리를 제거합니다. 가독성이 향상되고 실행 계획이 더 효율적일 수 있습니다.

 

총정리

개선된 쿼리들은 가독성을 높이고, 필요한 경우 중첩된 서브쿼리를 줄여 성능을 향상시킬 수 있습니다.

SQL 쿼리 작성시 명시적인 JOIN 구문 사용, 불필요한 서브쿼리 제거, 그룹화 및 집계 함수의 적절한 사용이 중요합니다.