[SQLD 자격검정 실전문제] Part 2-1. 88번 적절한 배치작업 구현 SQL 찾기

정답 : 4번

특정 고객(CUSTID)에게 추천한 컨텐츠를 조회하는 쿼리임.

단, 고객이 비선호로 표시한 컨텐츠는 제외함.

비선호 컨텐츠를 제외하기 위해서는 NOT EXISTS를 사용하며, 이 때 특정 고객이 비선호하는 것만 조회해야 함. 

 

[테이블 관계 설명]

- 고객은 추천 컨텐츠를 받을 수 있으며, 추천 컨텐츠는 추천 대상일자에 맞춰 관리됨

- 비선호 컨텐츠는 고객이 선호하지 않는 컨텐츠를 정의하여 특정 조건에서 제외해야 함

- 고객에게 추천 컨텐츠를 제공할 때, 비선호 컨텐츠로 등록된 컨텐츠는 제외해야 함 

# 테이블 삭제
DROP TABLE 비선호컨텐츠;
DROP TABLE 추천컨텐츠;
DROP TABLE 컨텐츠;
DROP TABLE 고객;

# 테이블 생성
CREATE TABLE 고객 (
    고객ID VARCHAR2(20) PRIMARY KEY,
    고객명 VARCHAR2(100),
    나이 NUMBER
);

CREATE TABLE 컨텐츠 (
    컨텐츠ID VARCHAR2(20) PRIMARY KEY,
    컨텐츠명 VARCHAR2(100)
);

CREATE TABLE 추천컨텐츠 (
    고객ID VARCHAR2(20),
    컨텐츠ID VARCHAR2(20),
    추천대상일자 VARCHAR2(10),
    CONSTRAINT pk_추천컨텐츠 PRIMARY KEY (고객ID, 컨텐츠ID),
    CONSTRAINT fk_추천컨텐츠_고객 FOREIGN KEY (고객ID) REFERENCES 고객(고객ID),
    CONSTRAINT fk_추천컨텐츠_컨텐츠 FOREIGN KEY (컨텐츠ID) REFERENCES 컨텐츠(컨텐츠ID)
);

CREATE TABLE 비선호컨텐츠 (
    고객ID VARCHAR2(20),
    컨텐츠ID VARCHAR2(20),
    등록일시 VARCHAR2(10),
    CONSTRAINT pk_비선호컨텐츠 PRIMARY KEY (고객ID, 컨텐츠ID),
    CONSTRAINT fk_비선호컨텐츠_고객 FOREIGN KEY (고객ID) REFERENCES 고객(고객ID),
    CONSTRAINT fk_비선호컨텐츠_컨텐츠 FOREIGN KEY (컨텐츠ID) REFERENCES 컨텐츠(컨텐츠ID)
);

# 테스트 데이터
INSERT INTO 고객 VALUES ('CUST01', '김고객', 30);
INSERT INTO 컨텐츠 VALUES ('CONT01', '추천컨텐츠1');
INSERT INTO 컨텐츠 VALUES ('CONT02', '추천컨텐츠2');

INSERT INTO 추천컨텐츠 VALUES ('CUST01', 'CONT01', '2024.01.25');
INSERT INTO 추천컨텐츠 VALUES ('CUST01', 'CONT02', '2024.01.25');
INSERT INTO 비선호컨텐츠 VALUES ('CUST01', 'CONT02', '2024.01.25');

# 쿼리 실행
SELECT C.컨텐츠ID, C.컨텐츠명
FROM 고객 A
INNER JOIN 추천컨텐츠 B ON (A.고객ID = 'CUST01' AND A.고객ID = B.고객ID)
INNER JOIN 컨텐츠 C ON (B.컨텐츠ID = C.컨텐츠ID)
WHERE B.추천대상일자 = '2024.01.25'
AND NOT EXISTS (
    SELECT X.컨텐츠ID
    FROM 비선호컨텐츠 X
    WHERE X.고객ID = B.고객ID
    AND X.컨텐츠ID = B.컨텐츠ID
);