[프로그래머스] SQL 쿼리테스트 Lv3(2)

1. 대장균의 크기에 따라 분류하기(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
ORDER BY ID;

2. 없어진 기록 찾기

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

프로그래머스

SELECT
    OUTS.ANIMAL_ID,
    OUTS.NAME
FROM ANIMAL_OUTS AS OUTS
    LEFT JOIN ANIMAL_INS AS INS
    ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY OUTS.ANIMAL_ID;

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

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

프로그래머스

SELECT
     DISTINCT CAR_ID,
    IF(CAR_ID IN (
        SELECT CAR_ID
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE),'대여중', '대여 가능') AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
ORDER BY CAR_ID DESC;

• 문제 풀이 아이디어
  • 서브쿼리는 2022년 10월 16일에 대여 중인 CAR_ID 목록을 추출하고,
    메인 쿼리는 각 CAR_ID가 이 목록에 포함되어 있는지 IF문으로 판단하여
    "대여중" 또는 "대여 가능"을 결과로 출력한다.

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

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

프로그래머스

SELECT 
     CONCAT('/home/grep/src/',F.BOARD_ID,'/',F.FILE_ID,F.FILE_NAME,F.FILE_EXT) AS FILE_PATH
FROM USED_GOODS_BOARD AS B
	JOIN USED_GOODS_FILE  AS F
	ON B.BOARD_ID = F.BOARD_ID
WHERE B.VIEWS = (
    SELECT MAX(VIEWS)
    FROM USED_GOODS_BOARD)
ORDER BY F.FILE_ID DESC;

POINT!

FROM 절에서는 전체적인 조인 구조만 만들고 WHERE 절에서 핵심 조건을 필터링하자.

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

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

프로그래머스

1. 첫번째 문제 풀이

정답 처리가 되긴했는데 점수가 60점이 나왔습니다.

아래 쿼리는 평균 33 이상 필터 후 MAX 계산이 자연스럽게 이루어지는 구조로 이루어져있는데요

여기서 놓친게 있었습니다.

• 10cm 이하의 물고기는 10cm로 간주해서 평균 계산해야한다라는 조건에서 잡은 물고기의 길이가 10cm 이하일 경우에는 LENGTH가 NULL이다라는 것입니다.
•  IF(LENGTH <= 10, 10, LENGTH) 코드는  LENGTH가 NULL이면 <=10도NULL이 되기 때문에 조건이 아예 무시됩니다. 그래서 평균이 정확하지 않은 것이었죠! 

SELECT
    COUNT(*) AS FISH_COUNT,
    MAX(F_LENGTH) AS MAX_LENGTH, 
    FISH_TYPE 
FROM(
    SELECT 
    ID,
    FISH_TYPE,
    TIME,
    IF(LENGTH <= 10, 10, LENGTH) AS F_LENGTH
FROM FISH_INFO) AS INFO
GROUP BY FISH_TYPE
HAVING(AVG(F_LENGTH) >= 33)
ORDER BY FISH_TYPE

 

2. 수정된 풀이 (100점)

IFNULL(LENGTH, 10) 으로 수정함으로써 길이가 10cm이하인 경우 LENGTH가 NULL이라는 조건을 고려하게 됩니다.

COALESCE(LENGTH, 10) 을 사용해도 됩니다.

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

 

POINT!

IFNULL과 COALESCE는  NULL값을 다른 값으로 대체하는 함수입니다.

 

[IFNULL과 COALESCE의 차이 비교표]

항목	IFNULL(expr1, expr2)	COALESCE(expr1, expr2, ...)
지원 개수	2개만 사용 가능	여러 개 인자 지원
NULL 대체 방식	expr1이 NULL이면 expr2 반환	왼쪽부터 차례로 NULL이 아닌 첫 값 반환
표준 SQL 여부	MySQL 전용 (비표준)	ANSI 표준 SQL 함수