[SQL] 02. 프로그래머스 LV.2 문제 정답

💯 프로그래머스 SQL LV.2 정답

고양이와 개는 몇 마리 있을까?

SELECT ANIMAL_TYPE, COUNT(*) AS count
FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'Dog' OR ANIMAL_TYPE ='Cat'
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE = 'Cat' DESC;

동명 동물 수 찾기

SELECT NAME, COUNT(NAME)
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(NAME) >= 2
ORDER BY NAME;

루시와 엘라 찾기

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME = 'Lucy' OR
      NAME = 'Ella' OR
      NAME = 'Pickle' OR
      NAME = 'Rogan' OR
      NAME = 'Sabrina' OR
      NAME = 'Mitty'
ORDER BY ANIMAL_ID;

이름에 el이 들어가는 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME LIKE '%el%' AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME;

중성화 여부 파악하기

SELECT ANIMAL_ID, NAME,
       CASE WHEN SEX_UPON_INTAKE LIKE 'Neutered%' THEN 'O'
            WHEN SEX_UPON_INTAKE LIKE 'Spayed%' THEN 'O'
            ELSE 'X'
            END AS '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

입양 시각 구하기(1)

SELECT HOUR(DATETIME) AS HOUR, COUNT(HOUR(DATETIME)) AS COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) BETWEEN 9 AND 20
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME);

DATETIME에서 DATE로 형 변환

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS '날짜'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

가격이 제일 비싼 식품의 정보 출력하기

SELECT *
FROM FOOD_PRODUCT
ORDER BY PRICE DESC
LIMIT 1;

3월에 태어난 여성 회원 목록 출력하기

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE
    MONTH(DATE_OF_BIRTH) = 3 AND
    GENDER = 'W' AND
    TLNO IS NOT NULL
ORDER BY MEMBER_ID ASC;

카테고리 별 상품 개수 구하기

SELECT SUBSTR(PRODUCT_CODE, 1, 2) AS CATEGORY, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY CATEGORY;

가격대 별 상품 개수 구하기

SELECT
    CASE
        WHEN PRICE < 10000 THEN 0
        WHEN PRICE >= 10000 AND PRICE < 20000 THEN 10000
        WHEN PRICE >= 20000 AND PRICE < 30000 THEN 20000
        WHEN PRICE >= 30000 AND PRICE < 40000 THEN 30000
        WHEN PRICE >= 40000 AND PRICE < 50000 THEN 40000
        WHEN PRICE >= 50000 AND PRICE < 60000 THEN 50000
        WHEN PRICE >= 60000 AND PRICE < 70000 THEN 60000
        WHEN PRICE >= 70000 AND PRICE < 80000 THEN 70000
        WHEN PRICE >= 80000 AND PRICE < 90000 THEN 80000
        ELSE 90000
    END AS PRICE_GROUP, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

가격대 별 상품 개수 구하기

SELECT P.PRODUCT_CODE AS PRODUCT_CODE, SUM(P.PRICE * O.SALES_AMOUNT) AS SALES
FROM OFFLINE_SALE O JOIN PRODUCT P ON O.PRODUCT_ID = P.PRODUCT_ID
GROUP BY P.PRODUCT_ID
ORDER BY SALES DESC, PRODUCT_CODE ASC;

❌오답 : SELECT 절에서 총 매출액을 구할 때 OFFLINE_SALE 테이블에서 수량을 곱하지 않아서 오답처리

진료과별 총 예약 횟수 출력하기

SELECT MCDP_CD AS '진료과 코드', COUNT(*) AS '5월예약건수'
FROM APPOINTMENT
WHERE YEAR(APNT_YMD) = 2022 AND MONTH(APNT_YMD) = 5
GROUP BY MCDP_CD
ORDER BY `5월예약건수`, MCDP_CD;

성분으로 구분한 아이스크림 총 주문량

SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF H JOIN ICECREAM_INFO I ON H.FLAVOR = I.FLAVOR
GROUP BY INGREDIENT_TYPE
ORDER BY `TOTAL_ORDER` ASC;

조건에 맞는 도서와 저자 리스트 출력하기

SELECT BOOK_ID, AUTHOR_NAME, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK B JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE B.CATEGORY = '경제'
ORDER BY PUBLISHED_DATE;

자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

-- 방법1 LIKE '%AAA%' 사용하기
SELECT CAR_TYPE, COUNT(*) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%통풍시트%' OR
      OPTIONS LIKE '%열선시트%' OR
      OPTIONS LIKE '%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE;

-- 방법2 REGEXP 사용하기
SELECT CAR_TYPE, COUNT(*) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS REGEXP ('통풍시트|열선시트|가죽시트')
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE;

자동차 평균 대여 기간 구하기

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE)+1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

조건에 부합하는 중고거래 상태 조회하기

SELECT BOARD_ID,
       WRITER_ID,
       TITLE,
       PRICE,
       CASE WHEN STATUS = 'SALE' THEN '판매중'
            WHEN STATUS = 'RESERVED' THEN '예약중'
            ELSE '거래완료'
       END AS STATUS
FROM USED_GOODS_BOARD
WHERE DATE_FORMAT(CREATED_DATE, '%Y-%m-%d') = '2022-10-05'
ORDER BY BOARD_ID DESC;

조건에 맞는 아이템들의 가격의 총합 구하기

SELECT SUM(PRICE) AS TOTAL_PRICE
FROM ITEM_INFO
WHERE RARITY = 'LEGEND';

ROOT 아이템 구하기

SELECT I.ITEM_ID, I.ITEM_NAME
FROM ITEM_INFO I JOIN ITEM_TREE T ON I.ITEM_ID = T.ITEM_ID
WHERE T.PARENT_ITEM_ID IS NULL;

ROOT 아이템 구하기

SELECT I.ITEM_ID, I.ITEM_NAME, I.RARITY
FROM ITEM_INFO I JOIN ITEM_TREE T ON I.ITEM_ID = T.ITEM_ID
WHERE T.PARENT_ITEM_ID IN (SELECT ITEM_ID
                           FROM ITEM_INFO
                           WHERE RARITY = 'RARE')
ORDER BY I.ITEM_ID DESC;

조건에 맞는 사원 정보 출력하기

SELECT SUM(SCORE) AS SCORE, B.EMP_NO, B.EMP_NAME, B.POSITION, B.EMAIL
FROM HR_EMPLOYEES B LEFT JOIN HR_GRADE C ON B.EMP_NO = C.EMP_NO
GROUP BY B.EMP_NO
ORDER BY SCORE DESC
LIMIT 1;

연도 별 평균 미세먼지 농도 조회하기

SELECT YEAR(YM) AS YEAR, ROUND(AVG(PM_VAL1), 2) AS PM10, ROUND(AVG(PM_VAL2), 2) AS `PM2.5`
FROM AIR_POLLUTION
WHERE LOCATION2 = '수원'
GROUP BY YEAR(YM)
ORDER BY YEAR;

노선별 평균 역 사이 거리 조회하기

SELECT ROUTE,
       CONCAT(ROUND(SUM(D_BETWEEN_DIST), 1), 'km') AS TOTAL_DISTANCE,
       CONCAT(ROUND(AVG(D_BETWEEN_DIST), 2), 'km') AS AVERAGE_DISTANCE
FROM SUBWAY_DISTANCE
GROUP BY ROUTE
ORDER BY ROUND(SUM(D_BETWEEN_DIST), 1) DESC;

❌ 오답 : 정렬할 때 TOTAL_DISTANCE 그대로 사용하면 'km'때문에 오답처리 발생

물고기 종류 별 잡은 수 구하기

-- 첫 번째 방법 GROUP BY절에 FISH_NAME 추가하기
SELECT COUNT(*) AS FISH_COUNT, FISH_NAME
FROM FISH_INFO I LEFT JOIN FISH_NAME_INFO N ON I.FISH_TYPE = N.FISH_TYPE
GROUP BY N.FISH_TYPE, N.FISH_NAME
ORDER BY FISH_COUNT DESC;

-- 두 번째 방법 SELECT 절에 집계함수 MAX 추가해주기
SELECT COUNT(*) AS FISH_COUNT, MAX(FISH_NAME) AS FISH_NAME
FROM FISH_INFO I LEFT JOIN FISH_NAME_INFO N ON I.FISH_TYPE = N.FISH_TYPE
GROUP BY N.FISH_TYPE
ORDER BY FISH_COUNT DESC;

❌ 오답 : ONLY_FULL_GROUP_BY 모드에서는GROUP BY 할 때 선택된 모든 컬럼이 반드시 GROUP BY에 포함되거나 집계함수를 사용해야 함

월별 잡은 물고기 수 구하기

SELECT COUNT(*) AS FISH_COUNT, MONTH(TIME) AS MONTH
FROM FISH_INFO
GROUP BY MONTH
ORDER BY MONTH;

특정 물고기를 잡은 총 수 구하기

SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO I LEFT JOIN FISH_NAME_INFO N ON I.FISH_TYPE = N.FISH_TYPE
WHERE N.FISH_NAME = 'BASS' OR N.FISH_NAME = 'SNAPPER';

분기별 특화된 대장균의 개체 수 구하기

-- 첫 번째 방법 CASE문 사용하기
SELECT CASE WHEN MONTH(DIFFERENTIATION_DATE) < 4 THEN '1Q'
            WHEN MONTH(DIFFERENTIATION_DATE) < 7 THEN '2Q'
            WHEN MONTH(DIFFERENTIATION_DATE) < 10 THEN '3Q'
            ELSE '4Q'
        END AS QUARTER, COUNT(*) AS ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY QUARTER
ORDER BY QUARTER;

-- 두 번째 방법(프로그래머스 정답 공유 가져옴)
SELECT
CONCAT(FLOOR((MONTH(DIFFERENTIATION_DATE)-1)/3)+1,'Q') as QUARTER,
COUNT(*) AS ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY QUARTER
ORDER BY QUARTER;

두 번째 방법에서 분기를 구하는 방식이 신기해서 가져옴

연도별 대장균 크기의 편차 구하기

SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR,
       (MAX_SIZE - SIZE_OF_COLONY) AS YEAR_DEV,
       ID
FROM ECOLI_DATA JOIN (
                      SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR,
                             MAX(SIZE_OF_COLONY) AS MAX_SIZE
                      FROM ECOLI_DATA
                      GROUP BY YEAR(DIFFERENTIATION_DATE))
                      AS MAX_SIZES ON YEAR(ECOLI_DATA.DIFFERENTIATION_DATE) = MAX_SIZES.YEAR
ORDER BY YEAR, YEAR_DEV

❌ 오답 : 서브쿼리 사용 방법 더 익숙해져야 함. 서브쿼리에서 연도별 최대값을 만들고 원래 테이블의 year와 join해서 연도 별 최대 대장균 크기를 각 행마다 붙여줬다.