[sqld] 노랭이책(개정판) - 38번 문제(p.53)

1. 예제 테이블

col CUSTOMER_NAME for a30
col CONTENT_NAME for a30

select * from CUSTOMER;
select * from CONTENT;
select * from NOTPREFER_CONTENT;
select * from RECOMMENDED_CONTENT;

-- 고객 테이블
CUSTOMER_ID CUSTOMER_NAME                         AGE
----------- ------------------------------ ----------
          1 John Doe                               30
          2 Jane Smith                             25
          3 Alice Brown                            40

-- 컨텐츠 테이블
CONTENT_ID CONTENT_NAME
---------- ------------------------------
       101 Action Movie
       102 Romantic Comedy
       103 Documentary

-- 비선호컨텐츠 테이블
CUSTOMER_ID CONTENT_ID REGISTER_DATE
----------- ---------- --------------------
          1        102 2023.01.01
          2        101 2023-02-15

-- 추천컨텐츠 테이블
CUSTOMER_ID CONTENT_ID TARGET_DATE
----------- ---------- --------------------
          1        102 2023-08-21
          1        103 2023-08-21
          2        102 2023-08-21

예제쿼리.txt

0.00MB

 

2. 문제 보기

-- 1번
SELECT C.CONTENT_ID, C.CONTENT_NAME
FROM CUSTOMER A INNER JOIN RECOMMENDED_CONTENT B
ON (A.CUSTOMER_ID = B.CUSTOMER_ID) INNER JOIN CONTENT C
ON (B.CONTENT_ID = C.CONTENT_ID)
WHERE A.CUSTOMER_ID = #custId#
AND B.TARGET_DATE = TO_CHAR(SYSDATE, 'YYYY.MM.DD')
AND NOT EXISTS (SELECT X.CONTENT_ID
                FROM NOTPREFER_CONTENT X
                WHERE X.CUSTOMER_ID = B.CUSTOMER_ID);

-- 2번
SELECT C.CONTENT_ID, C.CONTENT_NAME
FROM CUSTOMER A INNER JOIN RECOMMENDED_CONTENT B
ON (A.CUSTOMER_ID = B.CUSTOMER_ID) INNER JOIN CONTENT C
ON (B.CONTENT_ID = C.CONTENT_ID) RIGHT OUTER JOIN NOTPREFER_CONTENT D
ON (B.CUSTOMER_ID = D.CUSTOMER_ID AND B.CONTENT_ID = D.CONTENT_ID)
WHERE B.TARGET_DATE = TO_CHAR(SYSDATE, 'YYYY.MM.DD')
AND B.CONTENT_ID IS NOT NULL;

-- 3번
SELECT C.CONTENT_ID, C.CONTENT_NAME
FROM CUSTOMER A INNER JOIN RECOMMENDED_CONTENT B
ON (A.CUSTOMER_ID = B.CUSTOMER_ID) INNER JOIN CONTENT C
ON (B.CONTENT_ID = C.CONTENT_ID) LEFT OUTER JOIN NOTPREFER_CONTENT D
ON (B.CUSTOMER_ID = D.CUSTOMER_ID AND B.CONTENT_ID = D.CONTENT_ID)
WHERE A.CUSTOMER_ID = #custId#
AND B.TARGET_DATE = TO_CHAR(SYSDATE, 'YYYY.MM.DD')
AND D.CONTENT_ID IS NOT NULL;

-- 4번
SELECT C.CONTENT_ID, C.CONTENT_NAME
FROM CUSTOMER A INNER JOIN RECOMMENDED_CONTENT B
ON (A.CUSTOMER_ID = B.CUSTOMER_ID) INNER JOIN CONTENT C
ON (B.CONTENT_ID = C.CONTENT_ID)
WHERE A.CUSTOMER_ID = #custId#
AND B.TARGET_DATE = TO_CHAR(SYSDATE, 'YYYY.MM.DD')
AND NOT EXISTS (SELECT X.CONTENT_ID
                FROM NOTPREFER_CONTENT X
                WHERE X.CUSTOMER_ID = B.CUSTOMER_ID
                AND X.CONTENT_ID = B.CONTENT_ID);

// 실제로 SQL 쿼리를 실행할 때는 #custId#를 특정한 고객 ID로 대체해야 합니다.
// 예를 들어, 고객 ID가 1이라면 #custId#를 1로 대체하여 쿼리를 실행합니다.

 

3. 고객1 실제예시

SYSDATE가 2023.08.21이라고 가정한다.

-- 1번
SELECT C.CONTENT_ID, C.CONTENT_NAME
FROM CUSTOMER A INNER JOIN RECOMMENDED_CONTENT B
ON (A.CUSTOMER_ID = B.CUSTOMER_ID) INNER JOIN CONTENT C
ON (B.CONTENT_ID = C.CONTENT_ID)
WHERE A.CUSTOMER_ID = 1
AND B.TARGET_DATE = '2023.08.21'
AND NOT EXISTS (SELECT X.CONTENT_ID
                FROM NOTPREFER_CONTENT X
                WHERE X.CUSTOMER_ID = B.CUSTOMER_ID);

no rows selected

-- 2번
SELECT C.CONTENT_ID, C.CONTENT_NAME
FROM CUSTOMER A INNER JOIN RECOMMENDED_CONTENT B
ON (A.CUSTOMER_ID = 1 AND A.CUSTOMER_ID = B.CUSTOMER_ID) INNER JOIN CONTENT C
ON (B.CONTENT_ID = C.CONTENT_ID) RIGHT OUTER JOIN NOTPREFER_CONTENT D
ON (B.CUSTOMER_ID = D.CUSTOMER_ID AND B.CONTENT_ID = D.CONTENT_ID)
WHERE B.TARGET_DATE = '2023.08.21'
AND B.CONTENT_ID IS NOT NULL;

CONTENT_ID CONTENT_NAME
---------- ------------------------------
       102 Romantic Comedy
       
-- 2번
SELECT C.CONTENT_ID, C.CONTENT_NAME
FROM CUSTOMER A INNER JOIN RECOMMENDED_CONTENT B
ON (A.CUSTOMER_ID = 1 AND A.CUSTOMER_ID = B.CUSTOMER_ID) INNER JOIN CONTENT C
ON (B.CONTENT_ID = C.CONTENT_ID) LEFT OUTER JOIN NOTPREFER_CONTENT D -- LEFT 조인변경
ON (B.CUSTOMER_ID = D.CUSTOMER_ID AND B.CONTENT_ID = D.CONTENT_ID)
WHERE B.TARGET_DATE = '2023.08.21'
AND D.CONTENT_ID IS NULL; -- D.CONTENT_ID IS NULL;

CONTENT_ID CONTENT_NAME
---------- ------------------------------
       103 Documentary

-- 3번
SELECT C.CONTENT_ID, C.CONTENT_NAME
FROM CUSTOMER A INNER JOIN RECOMMENDED_CONTENT B
ON (A.CUSTOMER_ID = B.CUSTOMER_ID) INNER JOIN CONTENT C
ON (B.CONTENT_ID = C.CONTENT_ID) LEFT OUTER JOIN NOTPREFER_CONTENT D
ON (B.CUSTOMER_ID = D.CUSTOMER_ID AND B.CONTENT_ID = D.CONTENT_ID)
WHERE A.CUSTOMER_ID = 1
AND B.TARGET_DATE = '2023.08.21'
AND D.CONTENT_ID IS NOT NULL;

CONTENT_ID CONTENT_NAME
---------- ------------------------------
       102 Romantic Comedy
       
 -- 3번
SELECT C.CONTENT_ID, C.CONTENT_NAME
FROM CUSTOMER A INNER JOIN RECOMMENDED_CONTENT B
ON (A.CUSTOMER_ID = B.CUSTOMER_ID) INNER JOIN CONTENT C
ON (B.CONTENT_ID = C.CONTENT_ID) LEFT OUTER JOIN NOTPREFER_CONTENT D
ON (B.CUSTOMER_ID = D.CUSTOMER_ID AND B.CONTENT_ID = D.CONTENT_ID)
WHERE A.CUSTOMER_ID = 1
AND B.TARGET_DATE = '2023.08.21'
AND D.CONTENT_ID IS NULL; -- NULL로 변경

CONTENT_ID CONTENT_NAME
---------- ------------------------------
       103 Documentary

-- 4번
SELECT C.CONTENT_ID, C.CONTENT_NAME
FROM CUSTOMER A INNER JOIN RECOMMENDED_CONTENT B
ON (A.CUSTOMER_ID = 1 AND A.CUSTOMER_ID = B.CUSTOMER_ID) INNER JOIN CONTENT C
ON (B.CONTENT_ID = C.CONTENT_ID)
WHERE B.TARGET_DATE = '2023.08.21'
AND NOT EXISTS (SELECT X.CONTENT_ID
                FROM NOTPREFER_CONTENT X
                WHERE X.CUSTOMER_ID = B.CUSTOMER_ID
                AND X.CONTENT_ID = B.CONTENT_ID);
				
CONTENT_ID CONTENT_NAME
---------- ------------------------------
       103 Documentary