[프로그래머스] SQL 문제 풀이 (MySQL)

Cha Hwa Young·2023년 5월 10일

mysql sql 프로그래머스

프로그래머스

목록 보기

1/1

고득점 Kit 모든 문제에 대한 풀이 ❌❌
틀린 문제 혹은 다양한 풀이가 있는 문제만 정리

NULL

📍NULL 처리하기

IFNULL() : 데이터가 NULL인 데이터를 다른 값으로 출력한다.

IFNULL(expression, alt_value)

expression : NULL인지 테스트할 대상

alt_value : expression이 NULL일 때 반환되는 값

SELECT ANIMAL_TYPE, IFNULL(NAME, "No name"), SEX_UPON_INTAKE FROM ANIMAL_INS;

이름이 없는 동물의 이름을 "No name"으로 표시하기 위해 IFNULL() 사용

📍나이 정보가 없는 회원 수 구하기

SELECT 컬럼명 (AS) 별명 FROM 테이블명;

SELECT COUNT(*) USERS FROM USER_INFO WHERE AGE IS NULL;

📍경기도에 위치한 식품창고 목록 출력하기

경기도에 위치

➡️ 창고 이름이 '창고_경기2'처럼 경기가 포함되어 있거나, 주소가 경기로 시작해야 함.

LIKE 연산자 와일드카드

WHERE NAME LIKE '_r%' : 두 번째 인덱스에 r이 있는 모든 값

WHERE NAME LIKE 'a%' : a로 시작하는 모든 값

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IFNULL(FREEZER_YN, "N") FROM FOOD_WAREHOUSE WHERE ADDRESS LIKE ("경기%") ORDER BY WAREHOUSE_ID;

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IFNULL(FREEZER_YN, "N") FROM FOOD_WAREHOUSE WHERE WAREHOUSE_NAME LIKE ("%경기%") ORDER BY WAREHOUSE_ID;

MAX

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

ORDER BY로 풀이

SELECT * FROM FOOD_PRODUCT ORDER BY PRICE DESC LIMIT 1;

MAX, 서브쿼리 풀이

SELECT * FROM FOOD_PRODUCT WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT);

JOIN

INNER JOIN을 JOIN이라고만 써도 INNER JOIN으로 인식한다.

LEFT JOIN : 두 테이블이 있을 경우, 첫 번째 테이블을 기준으로 두 번째 테이블을 조합하는 JOIN

-- 예) 1학년 학생의 이름과 지도교수명을 출력하라. 단, 지도교수가 지정되지 않은 학생도 출력되게 하라.

SELECT STUDENT.NAME, PROFESSOR.NAME 
FROM STUDENT LEFT OUTER JOIN PROFESSOR -- STUDENT를 기준으로 왼쪽 조인
ON STUDENT.PID = PROFESSOR.ID 
WHERE GRADE = 1

RIHGT JOIN : 두 테이블이 있을 경우, 두 번째 테이블을 기준으로 첫 번째 테이블을 조합하는 JOIN

-- 예) 1학년 학생의 이름과 지도교수명을 출력하라. 단, 지도교수가 지정되지 않은 학생도 출력되게 하라.

SELECT STUDENT.NAME, PROFESSOR.NAME 
FROM STUDENT RIGHT OUTER JOIN PROFESSOR -- PROFESSOR를 기준으로 오른쪽 조인
ON STUDENT.PID = PROFESSOR.ID 
WHERE GRADE = 1

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

🥲틀린 풀이

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

➡️ 곱집합으로 결과가 나옴. JOIN으로 조건을 추가해야 함.

⚠️DATE_FORMAT

2022-02-14와 같이 연도 표현을 4자리로 하고 싶다면, %Y
22-02-14와 같이 연도 표현을 2자리로 하고 싶다면, %y

🤗 바른 풀이

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;

📍상품 별 오프라인 매출 구하기

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

GROUP BY가 없다면 다음과 같이 하나의 결과만 나온다.

📍없어진 기록 찾기

입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름

➡️ANIMAL_OUTS 테이블에는 있지만, ANIMAL_INS 테이블에는 없는 동물 찾기

JOIN 풀이

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

NOT IN 풀이

SELECT ANIMAL_ID, NAME FROM ANIMAL_OUTS
WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_INS)
ORDER BY ANIMAL_ID;

📍있었는데요 없었습니다

JOIN 풀이

SELECT O.ANIMAL_ID, O.NAME FROM ANIMAL_OUTS O JOIN ANIMAL_INS I ON O.ANIMAL_ID = I.ANIMAL_ID
WHERE O.DATETIME < I.DATETIME 
ORDER BY I.DATETIME;

WHERE로만 풀이

SELECT O.ANIMAL_ID, O.NAME FROM ANIMAL_OUTS O, ANIMAL_INS I
WHERE O.ANIMAL_ID = I.ANIMAL_ID AND O.DATETIME < I.DATETIME 
ORDER BY I.DATETIME;

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

JOIN 풀이

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

아직 입양을 못 간 동물 ➡️ ANIMAL_OUTS 테이블에 ID가 없는 경우

NOT IN 풀이

SELECT I.NAME, I.DATETIME
FROM ANIMAL_INS I
WHERE I.ANIMAL_ID NOT IN (SELECT O.ANIMAL_ID FROM ANIMAL_OUTS O)
ORDER BY I.DATETIME LIMIT 3;

📍5월 식품들의 총매출 조회하기

🥲틀린 풀이

SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(P.PRICE * O.AMOUNT) TOTAL_SALES
FROM FOOD_PRODUCT P JOIN FOOD_ORDER O
ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE O.PRODUCE_DATE LIKE "2022-05-%"
ORDER BY TOTAL_SALES DESC, PRODUCT_ID ASC;

총매출을 식품 ID별로 나누지 않고 다 합한 결과를 보여줌.

🤗 바른 풀이 1)

SELECT P.PRODUCT_ID, P.PRODUCT_NAME, 
FROM FOOD_PRODUCT P JOIN FOOD_ORDER O
ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE O.PRODUCE_DATE LIKE "2022-05-%"
GROUP BY P.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, PRODUCT_ID ASC;

GROUP BY 를 통해 SUM(P.PRICE * O.AMOUNT) TOTAL_SALES를 식품 ID별로 나눈다.

🤗 바른 풀이 2)

SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(P.PRICE * O.AMOUNT) TOTAL_SALES
FROM FOOD_PRODUCT P JOIN FOOD_ORDER O
ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE YEAR(O.PRODUCE_DATE) = 2022 AND MONTH(O.PRODUCE_DATE) = 5
GROUP BY P.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, PRODUCT_ID ASC;

YEAR(), MONTH() 로 연도와 월 데이터를 추출하여 풀이

🤗 바른 풀이 3)

SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(P.PRICE * O.AMOUNT) TOTAL_SALES
FROM FOOD_PRODUCT P JOIN FOOD_ORDER O
ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE DATE_FORMAT(O.PRODUCE_DATE, "%Y-%m") = "2022-05"
GROUP BY P.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, PRODUCT_ID ASC;

DATE_FORMAT 함수 이용하여 2022년 5월인지 판단

DATE_FORMAT(날짜, "형식")

📍주문량이 많은 아이스크림들 조회하기

WHERE 풀이

SELECT H.FLAVOR
FROM FIRST_HALF H, JULY J
WHERE H.FLAVOR = J.FLAVOR
GROUP BY H.FLAVOR
ORDER BY SUM(H.TOTAL_ORDER + J.TOTAL_ORDER) DESC LIMIT 3;

UNION ALL 풀이

SELECT FLAVOR
FROM (
    SELECT FLAVOR, TOTAL_ORDER FROM FIRST_HALF
    UNION ALL
    SELECT FLAVOR, TOTAL_ORDER FROM JULY
    ) A
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC LIMIT 3;

UNION ALL(수평 결합) : 중복여부와 상관없이 모든 데이터 출력

⚠️Every derived table must have its own alias

➡️SELECT FROM ( ~~ ) A와 같이 서브쿼리에는 Alias를 줘야 한다.

📍상품을 구매한 회원 비율 구하기
여러 조건이 있는 문제
1) 가입 년도가 2021년
2) 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림
3) 전체 결과는 년, 월을 기준으로 정렬

SELECT YEAR(O.SALES_DATE) YEAR, MONTH(O.SALES_DATE) MONTH, 
COUNT(DISTINCT U.USER_ID) PUCHASED_USERS, -- 상품을 구매한 회원수
ROUND(COUNT(DISTINCT U.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = 2021), 1) PUCHASED_RATIO -- ROUND(상품 구매 회원 수 / 전체 회원 수, 1)
FROM ONLINE_SALE O JOIN USER_INFO U ON O.USER_ID = U.USER_ID
WHERE YEAR(U.JOINED) = 2021
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;

⚠️ GROUP BY를 하지 않으면 월 별로 출력하지 않는다.

ROUND(숫자, 반올림할 자릿수)

숫자를 반올림할 자릿수 +1 자릿수에서 반올림

ROUND(1234.56, 1) = 1234.6
ROUND(1234.56, -1) = 1230

GROUP BY

📍가격대 별 상품 개수 구하기

TRUNCATE

SELECT TRUNCATE(PRICE, -4) PRICE_GROUP, COUNT(*) PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

TRUNCATE(숫자, 버림할 자릿수)

버림할 자릿수가 음수일 땐 자연수 범위에 해당

TRUNCATE(12000, -4) = 10000
TRUNCATE(9000, -4) = 0

TRUNCATE로 절삭하여 0, 10000, 20000와 같이 가격대를 표시할 수 있음

사칙연산 이용

SELECT (PRICE-PRICE%10000) PRICE_GROUP, COUNT(*) PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

판매가에서 판매가를 10000으로 나눈 나머지를 뺀다.

9000 - 9000%10000 = 9000 - 9000 = 0
12000 - 12000%10000 = 10000

📍동명 동물 수 찾기

🥲 틀린 풀이

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

Unknown column 'COUNT' in 'where clause'

➡️ WHERE 절 다음에는 집계 함수를 사용할 수 없는데, COUNT(*)가 2 이상인 조건을 WHERE에 기재하였음

🤗 바른 풀이

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

⚠️ WHERE 절은 조회할 집계되지 않은 자료에 대한 조건이고, HAVING은 집계된 자료에 대한 조건이므로

GROUP BY 다음에 HAVING이 와야 한다.

📍년, 월, 성별 별 상품 구매 회원 수 구하기
😒 틀린 풀이

SELECT YEAR(O.SALES_DATE) YEAR, MONTH(O.SALES_DATE) MONTH, U.GENDER GENDER, COUNT(U.USER_ID) USERS
FROM USER_INFO U JOIN ONLINE_SALE O
ON U.USER_ID = O.USER_ID
WHERE U.GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER;

동일 날짜에, 동일 id의 사용자가 만약 서로 다른 여러개 상품을 샀다면 중복 집계되므로 오답

😊 바른 풀이

SELECT YEAR(O.SALES_DATE) YEAR, MONTH(O.SALES_DATE) MONTH, U.GENDER GENDER, COUNT(DISTINCT U.USER_ID) USERS
FROM USER_INFO U JOIN ONLINE_SALE O
ON U.USER_ID = O.USER_ID
WHERE U.GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER;

DISTINCT로 중복 제거

📍입양 시각 구하기(1)

SELECT HOUR(DATETIME) HOUR, COUNT(ANIMAL_ID) COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR >= 9 AND HOUR < 20
ORDER BY HOUR;

HAVING절을 HOUR BETWEEN 9 AND 20로 써도 됨
⚠️ 입양 시각이 09:00부터 19:59까지이므로 9보다는 크거나 같지만, 20보다는 작아야 한다는 코드를 포함해야 한다.

📍입양 시각 구하기(2)
😒 틀린 풀이

SELECT HOUR(DATETIME) HOUR, COUNT(ANIMAL_ID) COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR BETWEEN 0 AND 24
ORDER BY HOUR;

0시부터 23시까지의 입양 건 수를 출력해야 되는데, 0~6시, 20~23시는 출력되지 않음
➡️ 그 시간대의 입양이 일어나지 않았기 때문

0~23시를 커버하는 테이블이 필요하고, with와 recursive를 활용하여 임시 테이블을 생성할 수 있다.

WITH recursive rc
as( # 재귀쿼리 세팅
    select 0 as HOUR # 초기값 설정
    union all # 위 쿼리와 아래 쿼리의 값을 연산
    select HOUR+1
    from rc # 재귀
    where HOUR < 23 # 반복을 멈추는 용도
)

테이블 생성 후 LEFT JOIN

😊 바른 풀이

with recursive rc
as (
    select 0 as hour
    union all
    select hour+1
    from rc
    where hour<23
)

select rc.hour, count(hour(O.datetime)) as count
from rc left join animal_outs O
on rc.hour = hour(O.datetime) -- rc 테이블에서의 시간과 animal_outs에서의 시간이 같은 것만
group by rc.hour

참고1
참고2
썸네일 메이커 항상 감사합니다 ♥️

Cha Hwa Young

기회를 잡는 사람이 되도록!

[프로그래머스] SQL 문제 풀이 (MySQL)