SQLD 기출문제 예상문제 74번

다음 문제를 풀어보세요.

다음 중 성능 관점에서 가장 비효율적인 SQL 쿼리를 고르시오. (단, 모든 테이블에는 적절한 인덱스가 존재하며, 데이터 양은 매우 크다고 가정한다.)

-- 테이블 구조
CREATE TABLE ORDERS (
    ORDER_ID INT PRIMARY KEY,
    CUSTOMER_ID INT,
    ORDER_DATE DATE,
    TOTAL_AMOUNT DECIMAL(10, 2)
);

CREATE TABLE CUSTOMERS (
    CUSTOMER_ID INT PRIMARY KEY,
    CUSTOMER_NAME VARCHAR(255),
    CITY VARCHAR(255)
);

CREATE TABLE PRODUCTS (
    PRODUCT_ID INT PRIMARY KEY,
    PRODUCT_NAME VARCHAR(255),
    PRICE DECIMAL(10, 2)
);

CREATE TABLE ORDER_ITEMS (
    ORDER_ID INT,
    PRODUCT_ID INT,
    QUANTITY INT,
    PRIMARY KEY (ORDER_ID, PRODUCT_ID)
);

SELECT c.CUSTOMER_NAME, SUM(o.TOTAL_AMOUNT) AS TOTAL_SPENT
FROM CUSTOMERS c
JOIN ORDERS o ON c.CUSTOMER_ID = o.CUSTOMER_ID
GROUP BY c.CUSTOMER_NAME
HAVING SUM(o.TOTAL_AMOUNT) > (SELECT AVG(TOTAL_AMOUNT) FROM ORDERS);

SELECT c.CUSTOMER_NAME
FROM CUSTOMERS c
WHERE EXISTS (SELECT 1 FROM ORDERS o WHERE c.CUSTOMER_ID = o.CUSTOMER_ID AND o.ORDER_DATE > DATE('2023-01-01'));

SELECT p.PRODUCT_NAME
FROM PRODUCTS p
WHERE p.PRODUCT_ID IN (SELECT PRODUCT_ID FROM ORDER_ITEMS WHERE QUANTITY > 10);

SELECT c.CUSTOMER_NAME
FROM CUSTOMERS c
WHERE c.CUSTOMER_ID NOT IN (SELECT CUSTOMER_ID FROM ORDERS WHERE ORDER_DATE < DATE('2023-01-01'));