SQLD 기출문제 예상문제 380번

다음 문제를 풀어보세요.

다음 중 'Electronics' 카테고리의 상품을 구매했지만 'Books' 카테고리의 상품은 구매하지 않은 고객의 이름을 가장 효율적으로 조회하는 SQL을 고르시오.

[테이블 구조]
고객의 주문 내역 및 구매한 상품의 카테고리 정보를 관리하는 테이블은 다음과 같습니다.
CREATE TABLE CUSTOMERS (
    CUSTOMER_ID VARCHAR(10) PRIMARY KEY,
    CUSTOMER_NAME VARCHAR(100),
    REGION VARCHAR(50)
);

CREATE TABLE ORDERS (
    ORDER_ID VARCHAR(10) PRIMARY KEY,
    CUSTOMER_ID VARCHAR(10),
    ORDER_DATE DATE,
    TOTAL_AMOUNT DECIMAL(10, 2),
    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID)
);

CREATE TABLE ORDER_ITEMS (
    ORDER_ITEM_ID VARCHAR(10) PRIMARY KEY,
    ORDER_ID VARCHAR(10),
    PRODUCT_ID VARCHAR(10),
    QUANTITY INT,
    UNIT_PRICE DECIMAL(10, 2),
    FOREIGN KEY (ORDER_ID) REFERENCES ORDERS(ORDER_ID),
    FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID)
);

CREATE TABLE PRODUCTS (
    PRODUCT_ID VARCHAR(10) PRIMARY KEY,
    PRODUCT_NAME VARCHAR(100),
    CATEGORY_ID VARCHAR(10),
    FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORIES(CATEGORY_ID)
);

CREATE TABLE CATEGORIES (
    CATEGORY_ID VARCHAR(10) PRIMARY KEY,
    CATEGORY_NAME VARCHAR(100)
);

SELECT C.CUSTOMER_NAME
FROM CUSTOMERS C
WHERE C.CUSTOMER_ID IN (
    SELECT O.CUSTOMER_ID
    FROM ORDERS O
    JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID
    JOIN PRODUCTS P ON OI.PRODUCT_ID = P.PRODUCT_ID
    JOIN CATEGORIES CAT ON P.CATEGORY_ID = CAT.CATEGORY_ID
    WHERE CAT.CATEGORY_NAME = 'Electronics'
)
AND C.CUSTOMER_ID NOT IN (
    SELECT O.CUSTOMER_ID
    FROM ORDERS O
    JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID
    JOIN PRODUCTS P ON OI.PRODUCT_ID = P.PRODUCT_ID
    JOIN CATEGORIES CAT ON P.CATEGORY_ID = CAT.CATEGORY_ID
    WHERE CAT.CATEGORY_NAME = 'Books'
);

SELECT C.CUSTOMER_NAME
FROM CUSTOMERS C
JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID
JOIN PRODUCTS P ON OI.PRODUCT_ID = P.PRODUCT_ID
JOIN CATEGORIES CAT_E ON P.CATEGORY_ID = CAT_E.CATEGORY_ID AND CAT_E.CATEGORY_NAME = 'Electronics'
LEFT JOIN (
    SELECT DISTINCT O2.CUSTOMER_ID
    FROM ORDERS O2
    JOIN ORDER_ITEMS OI2 ON O2.ORDER_ID = OI2.ORDER_ID
    JOIN PRODUCTS P2 ON OI2.PRODUCT_ID = P2.PRODUCT_ID
    JOIN CATEGORIES CAT_B ON P2.CATEGORY_ID = CAT_B.CATEGORY_ID
    WHERE CAT_B.CATEGORY_NAME = 'Books'
) AS BooksCustomers ON C.CUSTOMER_ID = BooksCustomers.CUSTOMER_ID
WHERE BooksCustomers.CUSTOMER_ID IS NULL
GROUP BY C.CUSTOMER_ID, C.CUSTOMER_NAME;

SELECT C.CUSTOMER_NAME
FROM CUSTOMERS C
WHERE EXISTS (
    SELECT 1
    FROM ORDERS O
    JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID
    JOIN PRODUCTS P ON OI.PRODUCT_ID = P.PRODUCT_ID
    JOIN CATEGORIES CAT ON P.CATEGORY_ID = CAT.CATEGORY_ID
    WHERE O.CUSTOMER_ID = C.CUSTOMER_ID
    AND CAT.CATEGORY_NAME = 'Electronics'
)
AND NOT EXISTS (
    SELECT 1
    FROM ORDERS O
    JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID
    JOIN PRODUCTS P ON OI.PRODUCT_ID = P.PRODUCT_ID
    JOIN CATEGORIES CAT ON P.CATEGORY_ID = CAT.CATEGORY_ID
    WHERE O.CUSTOMER_ID = C.CUSTOMER_ID
    AND CAT.CATEGORY_NAME = 'Books'
);

SELECT C.CUSTOMER_NAME
FROM CUSTOMERS C
LEFT JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
LEFT JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID
LEFT JOIN PRODUCTS P ON OI.PRODUCT_ID = P.PRODUCT_ID
LEFT JOIN CATEGORIES CAT ON P.CATEGORY_ID = CAT.CATEGORY_ID
GROUP BY C.CUSTOMER_ID, C.CUSTOMER_NAME
HAVING MAX(CASE WHEN CAT.CATEGORY_NAME = 'Electronics' THEN 1 ELSE 0 END) = 1
   AND MAX(CASE WHEN CAT.CATEGORY_NAME = 'Books' THEN 1 ELSE 0 END) = 0;