[Select 1~5] SQL 코딩테스트 고득점 Kit

안녕하세요! 다겸입니다.
최근에 쿼리 코딩테스트를 준비하면서 Programmers SQL 고득점 Kit 학습을 시작했는데, 벨로그에 기록하며 학습하면 좋을 것 같아서 이렇게 시작해볼까 합니다 :)

Programmers SQL 코딩테스트 고득점 Kit
SELECT 문제 1번부터 5번까지 정답 쿼리입니다.

1. 조건에 부합하는 중고거래 댓글 조회하기 (LV1)
2. 서울에 위치한 식당 목록 출력하기 (LV4)
3. 과일로 만든 아이스크림 고르기 (LV1)
4. 흉부외과 또는 일반외과 의사 목록 출력하기 (LV1)
5. 12세 이하인 여자 환자 목록 출력하기 (LV1)

1. 조건에 부합하는 중고거래 댓글 조회하기 (LV1)

정답:

SELECT b.TITLE
    ,  b.BOARD_ID
    ,  r.REPLY_ID
    ,  r.WRITER_ID
    ,  r.CONTENTS
    ,  DATE_FORMAT(r.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD AS b
JOIN USED_GOODS_REPLY AS r ON b.BOARD_ID = r.BOARD_ID
WHERE b.CREATED_DATE BETWEEN '2022-10-01 00:00:00' AND '2022-10-31 23:59:59'
ORDER BY r.CREATED_DATE ASC, b.TITLE ASC

2. 서울에 위치한 식당 목록 출력하기 (LV4)

정답:

SELECT DISTINCT i.REST_ID
     , i.REST_NAME
     , i.FOOD_TYPE
     , i.FAVORITES
     , i.ADDRESS
     , ROUND(AVG(r.REVIEW_SCORE), 2) AS SCORE
FROM REST_INFO AS i
JOIN REST_REVIEW AS r ON i.REST_ID = r.REST_ID
WHERE i.ADDRESS LIKE '서울%'
GROUP BY i.REST_ID
ORDER BY SCORE DESC, i.FAVORITES DESC

처음에는 아래처럼 WITH문을 사용해서 풀려고 했는데 계속 오답이 떠서 당황.. 🧐

오답 쿼리:

WITH AVG_REVIEW AS (
    SELECT i.REST_ID
        ,  AVG(REVIEW_SCORE) AS AVG_REVIEW_SCORE
    FROM REST_INFO AS i
    JOIN REST_REVIEW AS r ON i.REST_ID = r.REST_ID
    GROUP BY i.REST_ID
)

SELECT i.REST_ID
     , i.REST_NAME
     , i.FOOD_TYPE
     , i.FAVORITES
     , i.ADDRESS
     , ROUND(AVG_REVIEW_SCORE, 2) AS SCORE
FROM REST_INFO AS i
LEFT JOIN AVG_REVIEW AS a ON i.REST_ID = a.REST_ID
WHERE i.ADDRESS LIKE '%서울%'
ORDER BY SCORE DESC, i.FAVORITES DESC

나의 생각 & 문제점

• SELECT에 포함된 모든 칼럼이 GROUP BY에 없으면 오류가 발생할 수 있다고 알고있었음
• 하지만 WITH문 사용해 기존 테이블과 JOIN 후 결과값을 출력하려고 하니 식당이 중복으로 뜨는 문제가 발생..

개선점

• WITH문 필요X
• '%서울%' > '서울%'로 사용해야 함

의문점

• ONLY_FULL_GROUP_BY 모드에서는 SELECT에 포함된 모든 칼럼이 GROUP BY에 없으면 오류가 발생할 수 있음
• '%서울%'과 '서울%'이 다른 건 알지만 해당 문제에서 결과값은 동일하게 나오는데 '%서울%'로 작성하면 오답으로 채점됨!

3. 과일로 만든 아이스크림 고르기 (LV1)

정답:

SELECT f.FLAVOR
FROM FIRST_HALF AS f
JOIN ICECREAM_INFO AS i ON f.FLAVOR = i.FLAVOR
WHERE f.TOTAL_ORDER > 3000
  AND i.INGREDIENT_TYPE = 'fruit_based'
ORDER BY f.TOTAL_ORDER DESC

4. 흉부외과 또는 일반외과 의사 목록 출력하기 (LV1)

정답:

SELECT DR_NAME
    ,  DR_ID
    ,  MCDP_CD
    ,  DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') AS HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD = 'CS' OR MCDP_CD = 'GS'
ORDER BY HIRE_YMD DESC, DR_NAME ASC

5. 12세 이하인 여자 환자 목록 출력하기 (LV1)

정답:

SELECT PT_NAME
    ,  PT_NO
    ,  GEND_CD
    ,  AGE
    ,  (CASE WHEN TLNO IS NULL THEN 'NONE' ELSE TLNO END) AS TLNO
FROM PATIENT
WHERE GEND_CD = 'W' AND AGE <= 12
ORDER BY AGE DESC, PT_NAME ASC