서브쿼리

-- 단일행: 평균은 값 하나 → > 로 비교 (103, 202, 203, 302)
SELECT AUCTION_ID, START_PRICE
  FROM AUCTION
 WHERE START_PRICE >
       (SELECT AVG(START_PRICE) FROM AUCTION);

-- 오류: C1 물품 3건이 나오는데 = 로 비교 → ORA-01427
SELECT AUCTION_ID
  FROM AUCTION
 WHERE START_PRICE =
       (SELECT START_PRICE FROM AUCTION
         WHERE CATEGORY_CODE = 'C1');

-- 정상: 여러 행은 IN으로 (101, 102, 103)
SELECT AUCTION_ID
  FROM AUCTION
 WHERE START_PRICE IN
       (SELECT START_PRICE FROM AUCTION
         WHERE CATEGORY_CODE = 'C1');

-- > ANY: C1 가격 중 하나라도보다 크면 통과 (6건)
SELECT AUCTION_ID
  FROM AUCTION
 WHERE START_PRICE > ANY
       (SELECT START_PRICE FROM AUCTION
         WHERE CATEGORY_CODE = 'C1');

-- > ALL: C1 가격 모두보다 커야 통과 (202, 203, 302)
SELECT AUCTION_ID
  FROM AUCTION
 WHERE START_PRICE > ALL
       (SELECT START_PRICE FROM AUCTION
         WHERE CATEGORY_CODE = 'C1');

-- 다중컬럼: (C1, 80) 또는 (C2, 260)과 쌍이 일치 (101, 202)
SELECT AUCTION_ID
  FROM AUCTION
 WHERE (CATEGORY_CODE, START_PRICE) IN
       (SELECT CATEGORY_CODE, START_PRICE
          FROM AUCTION
         WHERE AUCTION_ID IN (101, 202));

-- 연관: 자기 분류의 평균과 비교 (103, 203, 302)
SELECT A.AUCTION_ID, A.START_PRICE
  FROM AUCTION A
 WHERE A.START_PRICE >
       (SELECT AVG(X.START_PRICE)
          FROM AUCTION X
         WHERE X.CATEGORY_CODE = A.CATEGORY_CODE);

-- EXISTS: 입찰이 한 건 이상 있는 물품 (101, 103, 202, 203, 302)
SELECT A.AUCTION_ID
  FROM AUCTION A
 WHERE EXISTS (SELECT 1 FROM BID B
                WHERE B.AUCTION_ID = A.AUCTION_ID);

-- 함정: 바깥 열을 참조하지 않는 EXISTS
-- 400 초과 입찰이 하나라도 있으므로 8건 모두 반환
SELECT AUCTION_ID
  FROM AUCTION
 WHERE EXISTS (SELECT 1 FROM BID
                WHERE BID_PRICE > 400);

-- 찜 목록의 NULL 때문에 0건
SELECT A.AUCTION_ID
  FROM AUCTION A
 WHERE A.AUCTION_ID NOT IN
       (SELECT W.AUCTION_ID FROM WATCHLIST W);

-- 해결 1: NOT EXISTS (6건)
SELECT A.AUCTION_ID
  FROM AUCTION A
 WHERE NOT EXISTS (SELECT 1 FROM WATCHLIST W
                    WHERE W.AUCTION_ID = A.AUCTION_ID);

-- 해결 2: 목록에서 NULL 제외 후 NOT IN (6건)
SELECT A.AUCTION_ID
  FROM AUCTION A
 WHERE A.AUCTION_ID NOT IN
       (SELECT W.AUCTION_ID FROM WATCHLIST W
         WHERE W.AUCTION_ID IS NOT NULL);

-- 스칼라: 물품마다 자기 분류 평균을 한 값으로 붙임
SELECT A.AUCTION_ID, A.START_PRICE,
       (SELECT ROUND(AVG(X.START_PRICE), 2)
          FROM AUCTION X
         WHERE X.CATEGORY_CODE = A.CATEGORY_CODE)
         AS CATEGORY_AVG
  FROM AUCTION A
 WHERE A.AUCTION_ID IN (103, 202, 203);

-- 0건 스칼라 서브쿼리 → NULL
SELECT (SELECT TITLE FROM AUCTION
         WHERE AUCTION_ID = 999) AS NO_ITEM
  FROM DUAL;

-- 인라인 뷰: 평균 시작가 180 이상인 분류 (C2, C3)
SELECT V.CATEGORY_CODE, V.ITEM_CNT, V.AVG_PRICE
  FROM (SELECT CATEGORY_CODE,
               COUNT(*) AS ITEM_CNT,
               ROUND(AVG(START_PRICE), 2) AS AVG_PRICE
          FROM AUCTION
         GROUP BY CATEGORY_CODE) V
 WHERE V.AVG_PRICE >= 180;

-- 오류: 안쪽 별칭 X는 괄호 밖에서 보이지 않음
SELECT X.CATEGORY_CODE
  FROM (SELECT X.CATEGORY_CODE FROM AUCTION X) V;

-- 정상: 인라인 뷰 별칭 V로 참조
SELECT V.CATEGORY_CODE
  FROM (SELECT X.CATEGORY_CODE FROM AUCTION X) V;

-- 분류별 통계를 뷰로 저장
CREATE OR REPLACE VIEW VW_CATEGORY_PRICE AS
SELECT CATEGORY_CODE,
       COUNT(*) AS ITEM_CNT,
       ROUND(AVG(START_PRICE), 2) AS AVG_PRICE
  FROM AUCTION
 GROUP BY CATEGORY_CODE;

-- 조회는 테이블처럼 (C1 3/140, C2 3/266.67, C3 2/180)
SELECT * FROM VW_CATEGORY_PRICE;

-- 오류: GROUP BY·집계가 들어간 뷰에는 INSERT 불가
INSERT INTO VW_CATEGORY_PRICE (CATEGORY_CODE, ITEM_CNT)
VALUES ('C4', 1);