[SQL] 프로그래머스 코딩테스트 : Lv3. (2)

 

Lv3. 부서별 평균 연봉 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/284529

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

❗ROUND(), AVG() 사용해서 포맷 맞추기
❗JOIN 사용해서 테이블결합
❗ORDER BY 절에 AVG() 조건 달기

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/299307

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

❗CASE WHEN 사용해서 조건 달기

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/157340

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

❗MAX() 절 안에 CASE WHEN 사용해서 '대여중', '대여 가능' 구분하고 최대값 추출하기
- 테이블에 존재하는 데이터가 '2022-10-16' 이전 데이터가 다수 존재
- '2022-10-16' 이상인 데이터가 1개씩 존재하기 때문에 MAX()를 사용해도 정답 처리 됨
❗참고 : https://school.programmers.co.kr/questions/79796

 

-- 실패
SELECT CAR_ID
    , CASE
        WHEN END_DATE >= '2022-10-16' THEN '대여중'
        ELSE '대여 가능'
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
-- ORDER BY CAR_ID DESC

❗END_DATE >= '2022-10-16' THEN '대여중' : START_DATE를 고려하지 않은 코드

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/164671

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

❗CONCAT() 으로 출력 조건 맞추기 (포맷 맞추기 위해 중간에 '/' 넣어주기)
❗WHERE 절에 서브 쿼리 사용해서 가장 많은 뷰를 보이는 게시물 ID 1개 추출하기 (LIMIT)
❗ORDER BY 절에 조건 맞추기

 

Lv3. 헤비 유저가 소유한 장소

https://school.programmers.co.kr/learn/courses/30/lessons/77487

SELECT * 
FROM PLACES
WHERE HOST_ID IN (SELECT HOST_ID
                    FROM PLACES
                    GROUP BY HOST_ID
                    HAVING COUNT(*) >1)
ORDER BY ID

❗WHERE 절 IN 과 서브쿼리 사용하기
❗WHERE 절 서브쿼리에 GROUP BY, HAVING 사용해서 조건 제한하기

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/151139

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(START_DATE), CAR_ID
ORDER BY MONTH(START_DATE) ASC, CAR_ID DESC

❗WHERE 절에 IN 조건, 서브쿼리 사용해서 조건에 맞는 CAR_ID만 추출
❗GROUP BY 절에 MONTH와 CAR_ID로 그룹화
❗ORDER BY 절에 정렬 조건 맞추기
❗참고 : https://noaahhh.tistory.com/87

+) WHERE 절 서브쿼리

❗WHERE 절에 날짜 조건 맞추기
❗HAVING 절에 개수 조건 맞추기

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/298519

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

❗COUNT(), MAX() 사용해서 출력 조건 맞추기
❗HAVING 절에 AVG() 사용해서 조건 맞추기

+) WITH 절 T1

❗IFNULL() 사용해서 NULL인 곳을 10으로 채우기

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/299305

WITH T1 AS (
    SELECT PARENT_ID, COUNT(*) AS CHILD_COUNT
    FROM ECOLI_DATA
    GROUP BY PARENT_ID
)
SELECT ED.ID
    , IFNULL(T1.CHILD_COUNT,0) AS CHILD_COUNT
FROM ECOLI_DATA AS ED
    LEFT JOIN T1 ON ED.ID = T1.PARENT_ID
ORDER BY ED.ID

❗IFNULL() 사용해서 CHILD_COUNT 가 없는 곳에 0 넣기
❗LEFT JOIN 사용해서 기존 테이블의 ID와 생성 테이블의 PARENT_ID 기준 결합하기

+) WITH 절 T1

❗GROUP BY, COUNT() 집계함수 사용해서 CHILD_COUNT 수 구하기

+) LEFT JOIN 절 결과

❗ 기존 테이블의 ID와 생성 테이블의 PARENT_ID 가 같다

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/273712

SELECT II.ITEM_ID, II.ITEM_NAME, II.RARITY
FROM ITEM_TREE AS IT
    RIGHT JOIN ITEM_INFO AS II ON IT.PARENT_ITEM_ID = II.ITEM_ID
WHERE IT.PARENT_ITEM_ID IS NULL
ORDER BY II.ITEM_ID DESC

❗RIGHT JOIN 사용해서 테이블 결합하기
❗WHERE 절에서 결합된 테이블에 조건 걸기

 

Lv3. 물고기 종류 별 대어 찾기

https://school.programmers.co.kr/learn/courses/30/lessons/293261

WITH T1 AS (
    SELECT *
        , ROW_NUMBER() OVER (PARTITION BY FISH_TYPE ORDER BY LENGTH DESC) AS ROW_N
    FROM FISH_INFO
)

SELECT T1.ID, FNI.FISH_NAME, T1.LENGTH
FROM T1 
    LEFT JOIN FISH_NAME_INFO AS FNI ON T1.FISH_TYPE = FNI.FISH_TYPE
        WHERE T1.ROW_N = 1
ORDER BY T1.ID ASC

❗LEFT JOIN 절에 WHERE 조건 달아서 조건에 충족하는 데이터만 테이블 결합하기
❗ORDER BY 절에 조건 충족하기

+) WITH 절 T1

❗FISH_TYPE 기준 LENGTH 가 큰 순서로 번호 매김

+) LEFT JOIN 절

❗LEFT JOIN 절에 WHERE 조건 달아서 조건에 충족하는 데이터만 테이블 결합하기
❗WHERE 절 조건 : ROW_N = 1

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/301649

WITH T1 AS (
    SELECT *
        , NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) AS TILE
    FROM ECOLI_DATA
)

SELECT ID
    , CASE
        WHEN TILE = 1 THEN 'CRITICAL'
        WHEN TILE = 2 THEN 'HIGH'
        WHEN TILE = 3 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS     COLONY_NAME
FROM T1
ORDER BY ID

❗CASE WHEN 사용해서 TILE 칼럼 조건에 맞춰서 수정하기

+) WITH 절 T1

❗NTILE() 함수 사용해서 SIZE_OF_COLONY 기준 사분위수 생성
- 문제의 조건이 사분위수와 같아서 NTILE 사용
❗참고 : https://jie0025.tistory.com/85