[프로그래머스] MySQL Lv.3 문제풀이 정답 모음 (feat. 코딩테스트)

 

문제 풀이 기간 : 24년 12월 24일 ~ 26일

 

최신순으로 정렬한 문제 순서입니다

 

없어진 기록 찾기

SELECT
    O.ANIMAL_ID
    , O.NAME
FROM ANIMAL_OUTS AS O
    LEFT JOIN ANIMAL_INS AS I
    ON O.ANIMAL_ID = I.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY ANIMAL_ID

 

있었는데요 없었습니다

SELECT
    I.ANIMAL_ID
    , I.NAME
FROM ANIMAL_INS AS I
    JOIN ANIMAL_OUTS AS O
    ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.DATETIME > O.DATETIME -- 보호시작일 > 입양일 (보호 시작일보다 입양일이 더 빠름)
ORDER BY I.DATETIME

날짜 비교 연산 (WHERE I.DATETIME > O.DATETIME)

• 큰 값일수록 더 나중의 시간임!!

 

오랜 기간 보호한 동물(1)

SELECT
    I.NAME
    , I.DATETIME
FROM ANIMAL_INS AS I
    LEFT JOIN ANIMAL_OUTS AS O
    ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.ANIMAL_ID IS NULL
ORDER BY DATETIME
LIMIT 3

 

오랜 기간 보호한 동물(2)

SELECT
    I.ANIMAL_ID
    , I.NAME
FROM ANIMAL_INS AS I
    JOIN ANIMAL_OUTS AS O
    ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.ANIMAL_ID IS NOT NULL
ORDER BY (O.DATETIME - I.DATETIME) DESC
LIMIT 2

 

헤비 유저가 소유한 장소

SELECT
    ID
    , NAME
    , HOST_ID
FROM PLACES
WHERE HOST_ID IN (
                SELECT HOST_ID
                FROM PLACES
                GROUP BY HOST_ID
                HAVING COUNT(*) >= 2)
ORDER BY ID

 

조건별로 분류하여 주문상태 출력하기

SELECT
    ORDER_ID
    , PRODUCT_ID
    , DATE_FORMAT(OUT_DATE, '%Y-%m-%d') AS OUT_DATE
    , CASE 
        WHEN OUT_DATE <= '2022-05-01' THEN '출고완료'
        WHEN OUT_DATE IS NULL THEN '출고미정'
        ELSE '출고대기'
FROM FOOD_ORDER 
ORDER BY ORDER_ID

 

즐겨찾기가 가장 많은 식당 정보 출력하기

풀이1. 서브쿼리 활용 
SELECT
    FOOD_TYPE
    , REST_ID
    , REST_NAME
    , FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (
    SELECT FOOD_TYPE, MAX(FAVORITES)
    FROM REST_INFO
    GROUP BY FOOD_TYPE
    )
ORDER BY FOOD_TYPE DESC
---

풀이 2. 윈도우 함수 활용
SELECT
    FOOD_TYPE
    , REST_ID
    , REST_NAME
    , FAVORITES
FROM (
    SELECT *
        , MAX(FAVORITES) OVER (PARTITION BY FOOD_TYPE) AS MAX_FAV
    FROM REST_INFO
    ) AS M
WHERE FAVORITES = MAX_FAV
ORDER BY FOOD_TYPE DESC

 

 

카테고리 별 도서 판매량 집계하기

SELECT
    CATEGORY
    , SUM(SALES) AS TOTAL_SALES
FROM BOOK AS B
    JOIN BOOK_SALES AS S
    ON B.BOOK_ID = S.BOOK_ID	
WHERE SALES_DATE LIKE '2022-01%'
GROUP BY CATEGORY
ORDER BY CATEGORY

 

대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

SELECT
    MONTH(START_DATE) AS MONTH 
    , CAR_ID
    , COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
                SELECT CAR_ID
                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
                GROUP BY CAR_ID
                HAVING COUNT(*) >= 5)
    AND START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY MONTH, CAR_ID
ORDER BY MONTH, CAR_ID DESC

 

자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

SELECT
    CAR_ID 
    , CASE
        WHEN MAX('2022-10-16' BETWEEN START_DATE AND END_DATE) = 1 THEN '대여중'
        ELSE '대여 가능'
        END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

 

group by를 수행하면 car_id 끼리 묶이게 되는데 이때 when의 max 절 안에 조건을 통해 아래처럼 출력
- 참이면 1 
- 거짓이면 0

따라서 max 함수에 의해 대여중이 한번이라도 있었으면 1을 출력하게 되며
그것이 =1 에 조건에 맞아 then '대여중'으로 나오게 되는 것

 

대여 기록이 존재하는 자동차 리스트 구하기

SELECT DISTINCT(C.CAR_ID) AS CAR_ID
FROM CAR_RENTAL_COMPANY_CAR AS C
    JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
    ON C.CAR_ID = H.CAR_ID
WHERE C.CAR_TYPE = '세단'
    AND H.START_DATE LIKE '2022-10%'
ORDER BY CAR_ID DESC

 

 

조건에 맞는 사용자와 총 거래금액 조회하기

SELECT
    U.USER_ID
    , U.NICKNAME
    , SUM(B.PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD AS B
    JOIN USED_GOODS_USER AS U
    ON B.WRITER_ID = U.USER_ID
WHERE B.STATUS = 'DONE'
GROUP BY USER_ID
HAVING TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES

 

조건에 맞는 사용자 정보 조회하기

SELECT
    USER_ID
    , NICKNAME
    , CONCAT_WS(' ', CITY, STREET_ADDRESS1, STREET_ADDRESS2) AS 전체주소
    , CONCAT_WS('-', SUBSTR(TLNO, 1, 3), SUBSTR(TLNO, 4, 4), SUBSTR(TLNO, 8, 4)) AS 전화번호
FROM USED_GOODS_USER
WHERE USER_ID IN ( 
                SELECT WRITER_ID
                FROM USED_GOODS_BOARD
                GROUP BY WRITER_ID
                HAVING COUNT(*) >= 3)
ORDER BY USER_ID DESC

CONCAT과 CONCAT_WS의 차이
: 문자열을 결합하는 함수 

• CONCAT
  • 여러 문자열을 단순히 붙여서 사용
  • 위의 ex) CONCAT(CITY, '-', STREET_ADDRESS1, '-', STREET_ADDRESS2)
• CONCAT_WS
  
  • 구분자를 지정하여 여러 문자열을 결합
  • 위의 ex) CONCAT_WS(' ', CITY, STREET_ADDRESS1, STREET_ADDRESS2)

위의 예시처럼 3개 이상의 문자열을 결합할 때는 concat_ws가 더 간편하다. 

SUBSTR 범위
: 문자열을 자를 때 사용되는 함수

• 사용법 : SUBSTR(문자열, 시작위치, 길이)

* 마지막이 '끝위치'가 아니라 '길이'임을 주의하기 *

 

조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

SELECT 
    CONCAT('/home/grep/src/', BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE 
WHERE BOARD_ID = (SELECT BOARD_ID
                FROM USED_GOODS_BOARD 
                ORDER BY VIEWS DESC
                LIMIT 1)
ORDER BY FILE_ID DESC

 

업그레이드 할 수 없는 아이템 구하기

SELECT
    I.ITEM_ID
    , ITEM_NAME
    , RARITY
FROM ITEM_INFO AS I
    LEFT JOIN ITEM_TREE AS T
    ON I.ITEM_ID = T.PARENT_ITEM_ID
WHERE T.ITEM_ID IS NULL
ORDER BY ITEM_ID DESC

 

부서별 평균 연봉 조회하기

SELECT 
    D.DEPT_ID
    , D.DEPT_NAME_EN
    , ROUND(AVG(E.SAL), 0) AS AVG_SAL
FROM HR_DEPARTMENT AS D
    JOIN HR_EMPLOYEES AS E
    ON D.DEPT_ID = E.DEPT_ID
GROUP BY D.DEPT_ID
ORDER BY AVG_SAL DESC

 

물고기 종류 별 대어 찾기

SELECT
    I.ID
    , N.FISH_NAME
    , I.LENGTH
FROM (SELECT 
        *
        , MAX(LENGTH) OVER (PARTITION BY FISH_TYPE) AS MAX_LENGTH
      FROM FISH_INFO
      ) AS I
      JOIN FISH_NAME_INFO AS N
      ON I.FISH_TYPE = N.FISH_TYPE
WHERE I.LENGTH = I.MAX_LENGTH
ORDER BY ID

 

특정 조건을 만족하는 물고기별 수와 최대 길이 구하기

SELECT 
    COUNT(*) AS FISH_COUNT
    , MAX(LENGTH) AS MAX_LENGTH
    , FISH_TYPE
FROM FISH_INFO
GROUP BY FISH_TYPE
HAVING AVG(IFNULL(LENGTH, 10)) >= 33
ORDER BY FISH_TYPE

 

대장균들의 자식의 수 구하기

SELECT 
    P.ID
    , COUNT(C.ID) AS CHILD_COUNT 
FROM ECOLI_DATA AS P
    LEFT JOIN ECOLI_DATA AS C
    ON P.ID = C.PARENT_ID
GROUP BY ID
ORDER BY ID

헷갈렸던 부분
SELECT 절에 COUNT(C.ID) vs COUNT(P.ID)

• COUNT(P.ID) : 항상 부모의 행 수를 반환 (부모 자체를 나타내기 때문에 NULL이 있을 수 없음 - NULL을 고려X) 
• COUNT(C.ID) : 자식이 있는 부모에 대해서만, 자식의 수를 반환 (NULL을 고려함)

* 헷갈리는 문제니까 주의하기 

 

대장균의 크기에 따라 분류하기 1

SELECT 
    ID
    , (CASE 
        WHEN SIZE_OF_COLONY <= 100 THEN 'LOW'
        WHEN SIZE_OF_COLONY > 1000 THEN 'HIGH'
        ELSE 'MEDIUM'
        END) AS SIZE
FROM ECOLI_DATA
ORDER BY ID

 

대장균의 크기에 따라 분류하기 2

SELECT 
    ID
    , (CASE 
        WHEN PER_RANK <= 0.25 THEN 'CRITICAL'
        WHEN PER_RANK <= 0.5 THEN 'HIGH'
        WHEN PER_RANK <= 0.75 THEN 'MEDIUM'
        ELSE 'LOW'
      END) AS COLONY_NAME
FROM (SELECT 
        ID, 
        PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS PER_RANK
     FROM ECOLI_DATA
     ) AS D
ORDER BY ID

 

 

🚀 보완해야 할 점
- SELF JOIN 더 연습하기
- PARTITION BY 더 적극적으로 활용해보기 

 

 

---

 

다른 Lv. 로 바로가기

[프로그래머스] SQL Lv.4 문제풀이 정답 모음 (feat. 코딩테스트)