[실무]프로그래머스 MySQL 기출문제 모음 Lv.4.2

■ 출처

- 코딩테스트 : MySQL / Oracle / Lv.4

https://school.programmers.co.kr/learn/challenges?order=recent&languages=mysql%2Coracle&page=1&levels=4

코딩테스트 연습 | 프로그래머스 스쿨

 

 

 

■ 연습문제

10. 오프라인/온라인 판매 데이터 통합하기

UNION 구문 : 여러 쿼리를 하나로 만드는 함수(즉, 컬럼 수와 데이터 타입이 모두 동일한 테이블 간 연산 / 중복된 데이터 제거 O)

UNION ALL 구문 : UNION 구문과 동일(단, 중복된 데이터 제거 X)

#설명: 22.03 온/오프라인 상품 판매 데이터의 판매 날자, 상품ID, 유저ID, 판매량을 조회
#참고: 'NULL AS'을 USER_ID 변수에 추가한 이유는 UNION을 사용하려면 컬럼 수가 같아야하기 때문에 톤앤매너 개념으로 사용

SELECT 
    DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, 
    PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM 
    ONLINE_SALE 
WHERE 
    SALES_DATE LIKE '2022-03%'

UNION ALL
SELECT 
    DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE, 
    PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM 
    OFFLINE_SALE 
WHERE 
    SALES_DATE LIKE '2022-03%'
ORDER BY 
    SALES_DATE ASC, PRODUCT_ID ASC, USER_ID ASC

 

11. 년, 월, 성별 별 상품 구매 회원 수 구하기

#설명: 년, 월, 성별 별로 상품을 구매한 회원 수를 조회
#참고: 회원 수를 확인하는 것이므로 중복된 회원을 고려해야 함을 확인

SELECT
    YEAR(B_Table.SALES_DATE) AS YEAR,
    MONTH(B_Table.SALES_DATE) AS MONTH,
    A_Table.GENDER,
    COUNT(DISTINCT B_Table.USER_ID) AS USERS  
FROM
    USER_INFO AS A_Table
    INNER JOIN ONLINE_SALE AS B_Table 
    ON A_Table.USER_ID = B_Table.USER_ID
WHERE
    A_Table.GENDER IS NOT NULL
GROUP BY
    YEAR,
    MONTH,
    A_Table.GENDER
ORDER BY
    YEAR ASC,
    MONTH ASC,
    A_Table.GENDER ASC

 

 

12. 그룹별 조건에 맞는 식당 목록 출력하기

#설명: 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회

SELECT
    A_Table.MEMBER_NAME, 
    B_Table.REVIEW_TEXT, 
    DATE_FORMAT(B_Table.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM
    MEMBER_PROFILE AS A_Table
    RIGHT JOIN REST_REVIEW AS B_Table 
    ON A_Table.MEMBER_ID = B_Table.MEMBER_ID
WHERE
    #리뷰를 가장 많이 작성한 회원 1명을 조회
    A_Table.MEMBER_ID = (SELECT MEMBER_ID 
                          FROM REST_REVIEW 
                          GROUP BY MEMBER_ID 
                          ORDER BY COUNT(*) DESC 
                          LIMIT 1)
ORDER BY
    REVIEW_DATE ASC, REVIEW_TEXT ASC

 

 

13. 서울에 위치한 식당 목록 출력하기

#설명: 서울에 위치한 식당들의 ID, 이름, 음식종류, 즐겨찾기 수, 주소, 리뷰 평균평점 조회

SELECT
    A_Table.REST_ID,
    A_Table.REST_NAME,
    A_Table.FOOD_TYPE,
    A_Table.FAVORITES,
    A_Table.ADDRESS, 
    ROUND(AVG(B_Table.REVIEW_SCORE), 2) AS SCORE
FROM
    REST_INFO AS A_Table
    INNER JOIN REST_REVIEW  AS B_Table
    ON A_Table.REST_ID = B_Table.REST_ID
GROUP BY
    A_Table.REST_ID
HAVING
    A_Table.ADDRESS LIKE '서울%'
ORDER BY
    SCORE DESC, A_Table.FAVORITES DESC

※ 두 테이블을 결합하여 쿼리를 작성할 경우, 변수별로 소속들을 꼭 함께 작성 必

 

 

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

#설명: 생산일자가 22.05인 식품들의 식품ID, 식품 이름, 총매출액을 조회

SELECT
    A_Table.PRODUCT_ID,
    A_Table.PRODUCT_NAME,
    SUM(A_Table.PRICE * B_Table.AMOUNT) AS TOTAL_SALES
FROM
    FOOD_PRODUCT AS A_Table
    RIGHT JOIN (SELECT * FROM FOOD_ORDER WHERE PRODUCE_DATE LIKE '2022-05%') AS B_Table
    ON A_Table.PRODUCT_ID = B_Table.PRODUCT_ID
GROUP BY
    A_Table.PRODUCT_ID
ORDER BY
    TOTAL_SALES DESC, A_Table.PRODUCT_ID ASC

 

 

15. 식품분류별 가장 비싼 식품의 정보 조회하기

#설명: 식품분류별(과자, 국, 김치, 식용유) 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회

SELECT
    CATEGORY, 
    PRICE AS MAX_PRICE, 
    PRODUCT_NAME
FROM
    FOOD_PRODUCT 
WHERE
    CATEGORY IN ('과자', '국', '김치', '식용유') 
    AND PRICE IN (SELECT MAX(PRICE) AS MAX_PRICE 
                  FROM FOOD_PRODUCT  
                  GROUP BY CATEGORY)
GROUP BY
    CATEGORY
ORDER BY
    MAX_PRICE DESC

 

 

16. 우유와 요거트가 담긴 장바구니

WITH 문 : 쿼리를 따로 작성하여 임시 테이블처럼 활용
- 장점 : 쿼리 가독성 향상(직관적 코드 작성 및 해석 가능)

              SQL 성능 개선(서브쿼리와 기능은 유사하지만, 동작 방식이 달라 성능 개선효과 有)

#설명: 우유와 요거트를 동시에 구입한 장바구니의 아이디 조회

SELECT
    DISTINCT A_Table.CART_ID
FROM
    CART_PRODUCTS AS A_Table
    INNER JOIN CART_PRODUCTS AS B_Table
    ON A_Table.CART_ID = B_Table.CART_ID
WHERE
    (A_Table.NAME = 'Milk' AND B_Table.NAME = 'Yogurt')
    OR (A_Table.NAME = 'Yogurt' AND B_Table.NAME = 'Milk')

/*
WITH Milk_Table AS (SELECT DISTINCT CART_ID, NAME FROM CART_PRODUCTS WHERE NAME = 'Milk'), 
Yogurt_Table AS (SELECT DISTINCT CART_ID, NAME FROM CART_PRODUCTS WHERE NAME = 'Yogurt')

SELECT 
    Yogurt_Table.CART_ID
FROM 
    Yogurt_Table 
    INNER JOIN Milk_Table 
    ON Yogurt_Table.CART_ID = Milk_Table.CART_ID
ORDER BY 
    Yogurt_Table.CART_ID
*/

 

 

17. 입양 시각 구하기(2)

#설명: 보호소에는 몇 시에 입양이 가장 확발하게 일어나는지 확인하고자, 0시부터 23시까지 시간대별 입양 건수 조회
#참고: 주어진 테이블에는 모든 시간별 데이터가 존재하지 않기 때문에 SET함수를 통해 새로운 칼럼을 만들어줘야 함을 확인

#시간대별로 동물이 입양된 횟수를 계산
SET @HOUR := -1;  

SELECT 
    #@hour을 1씩 증가시키면서 그 값을 HOUR 별칭으로 정의하고, 시간대별로 증가하는 숫자가 HOUR변수를 생성
    #(즉, HOUR 변수가 -1일 때 +1을 대입하여 0으로 만드는 원리처럼 0은 1이 되는 형태)
    @HOUR := @HOUR + 1 AS HOUR,
    
    #서브쿼리를 사용하여, 현재 시간대에 해당하는 테이블의 행 수를 조회
    (SELECT COUNT(HOUR(DATETIME))
     FROM ANIMAL_OUTS
     WHERE HOUR(DATETIME) = @HOUR) AS COUNT
FROM 
    ANIMAL_OUTS

WHERE 
    #테이블에 @hour 값이 23보다 작은 경우에 대한 행만 선택
    @HOUR < 23

 

 

18. 보호소에서 중성화한 동물

#설명: 보호소에 들어올 당시 중성화가 되지 않았지만, 보호소를 나갈 당시에는 중성화가된 동물의 아이디와 생물 종, 이름을 조회

SELECT
    A_Table.ANIMAL_ID,
    A_Table.ANIMAL_TYPE,
    A_Table.NAME
FROM
    ANIMAL_INS AS A_Table
    LEFT JOIN ANIMAL_OUTS AS B_Table
    ON A_Table.ANIMAL_ID = B_Table.ANIMAL_ID
WHERE
    B_Table.ANIMAL_ID IS NOT NULL
    AND A_Table.SEX_UPON_INTAKE LIKE 'Intact%'
    AND ((B_Table.SEX_UPON_OUTCOME LIKE 'Spayed%') OR (B_Table.SEX_UPON_OUTCOME LIKE 'Neutered%'))
ORDER BY
    A_Table.ANIMAL_ID ASC