SQLD 기출문제 예상문제 390번

다음 문제를 풀어보세요.

다음 중 아래 SQL에 대한 설명으로 가장 적절한 것을 고르시오.

[테이블 구조]

CREATE TABLE PRODUCTS (
    PRODUCT_ID INT PRIMARY KEY,
    PRODUCT_NAME VARCHAR(100),
    CATEGORY_ID INT,
    PRICE DECIMAL(10, 2),
    LAST_UPDATED_DATE DATE
);

CREATE TABLE CATEGORIES (
    CATEGORY_ID INT PRIMARY KEY,
    CATEGORY_NAME VARCHAR(100),
    DISCOUNT_RATE DECIMAL(5, 2)
);

[초기 데이터]

INSERT INTO CATEGORIES (CATEGORY_ID, CATEGORY_NAME, DISCOUNT_RATE) VALUES (1, 'Electronics', 0.10);
INSERT INTO CATEGORIES (CATEGORY_ID, CATEGORY_NAME, DISCOUNT_RATE) VALUES (2, 'Books', 0.05);
INSERT INTO CATEGORIES (CATEGORY_ID, CATEGORY_NAME, DISCOUNT_RATE) VALUES (3, 'Apparel', 0.15);
INSERT INTO CATEGORIES (CATEGORY_ID, CATEGORY_NAME, DISCOUNT_RATE) VALUES (4, 'Food', 0.00);

INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID, PRICE, LAST_UPDATED_DATE) VALUES (101, 'Laptop', 1, 1200.00, '2023-01-01');
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID, PRICE, LAST_UPDATED_DATE) VALUES (102, 'SQL Book', 2, 50.00, '2023-01-01');
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID, PRICE, LAST_UPDATED_DATE) VALUES (103, 'T-Shirt', 3, 30.00, '2023-01-01');
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID, PRICE, LAST_UPDATED_DATE) VALUES (104, 'Monitor', 1, 300.00, '2023-01-01');
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID, PRICE, LAST_UPDATED_DATE) VALUES (105, 'Java Book', 2, 45.00, '2023-01-01');
INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID, PRICE, LAST_UPDATED_DATE) VALUES (106, 'Snacks', 4, 10.00, '2023-01-01');

[SQL 쿼리]

UPDATE PRODUCTS P
SET P.PRICE = P.PRICE * (1 - (SELECT C.DISCOUNT_RATE FROM CATEGORIES C WHERE C.CATEGORY_ID = P.CATEGORY_ID)),
    P.LAST_UPDATED_DATE = CURRENT_DATE
WHERE P.CATEGORY_ID IN (SELECT CATEGORY_ID FROM CATEGORIES WHERE DISCOUNT_RATE > 0);