SQLD 기출문제 예상문제 35번

다음 문제를 풀어보세요.

다음은 고객 테이블(CUSTOMER)과 주문 테이블(ORDERS)의 구조 및 데이터 일부이다. 다음 SQL 쿼리의 응답 시간을 개선하기 위한 가장 효과적인 방법은 무엇인가?

-- 고객 테이블 구조
CREATE TABLE CUSTOMER (
    CUSTOMER_ID INT PRIMARY KEY,
    NAME VARCHAR(50),
    CITY VARCHAR(50)
);

-- 주문 테이블 구조
CREATE TABLE ORDERS (
    ORDER_ID INT PRIMARY KEY,
    CUSTOMER_ID INT,
    ORDER_DATE DATE,
    AMOUNT DECIMAL(10, 2),
    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID)
);

-- SQL 쿼리
SELECT C.NAME, SUM(O.AMOUNT)
FROM CUSTOMER C
JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
WHERE C.CITY = 'Seoul'
AND O.ORDER_DATE BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY C.NAME
HAVING SUM(O.AMOUNT) > 100000;

-- CUSTOMER 테이블 데이터 일부
INSERT INTO CUSTOMER (CUSTOMER_ID, NAME, CITY) VALUES (1, '김철수', 'Seoul');
INSERT INTO CUSTOMER (CUSTOMER_ID, NAME, CITY) VALUES (2, '박영희', 'Busan');
INSERT INTO CUSTOMER (CUSTOMER_ID, NAME, CITY) VALUES (3, '이민호', 'Seoul');

-- ORDERS 테이블 데이터 일부
INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ORDER_DATE, AMOUNT) VALUES (1, 1, '2023-03-15', 50000);
INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ORDER_DATE, AMOUNT) VALUES (2, 1, '2023-07-20', 60000);
INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ORDER_DATE, AMOUNT) VALUES (3, 2, '2023-05-10', 70000);
INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ORDER_DATE, AMOUNT) VALUES (4, 3, '2023-11-01', 80000);
INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ORDER_DATE, AMOUNT) VALUES (5, 3, '2023-12-25', 30000);