SQLD 기출문제 예상문제 205번

다음 문제를 풀어보세요.

다음 중 스칼라 서브쿼리를 사용하여 고객별 최대 주문 금액을 조회하는 SQL로 가장 적절한 것을 고르시오.

다음은 고객(CUSTOMER) 테이블과 주문(ORDERS) 테이블의 구조입니다.
-- CUSTOMER 테이블
CREATE TABLE CUSTOMER (
    CUSTOMER_ID VARCHAR2(10) PRIMARY KEY,
    CUSTOMER_NAME VARCHAR2(50)
);

-- ORDERS 테이블
CREATE TABLE ORDERS (
    ORDER_ID NUMBER PRIMARY KEY,
    CUSTOMER_ID VARCHAR2(10) REFERENCES CUSTOMER(CUSTOMER_ID),
    ORDER_AMOUNT NUMBER
);

SELECT C.CUSTOMER_ID, (SELECT MAX(O.ORDER_AMOUNT) FROM ORDERS O) AS MAX_ORDER_AMOUNT FROM CUSTOMER C;

SELECT C.CUSTOMER_ID, MAX(O.ORDER_AMOUNT) AS MAX_ORDER_AMOUNT FROM CUSTOMER C, ORDERS O WHERE C.CUSTOMER_ID = O.CUSTOMER_ID GROUP BY C.CUSTOMER_ID;

SELECT C.CUSTOMER_ID, (SELECT MAX(O.ORDER_AMOUNT) FROM ORDERS O WHERE O.CUSTOMER_ID = C.CUSTOMER_ID) AS MAX_ORDER_AMOUNT FROM CUSTOMER C;

SELECT C.CUSTOMER_ID, MAX(O.ORDER_AMOUNT) OVER (PARTITION BY C.CUSTOMER_ID) AS MAX_ORDER_AMOUNT FROM CUSTOMER C JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID;