오랜만에 SQL 문제풀이로 돌아왔다!
태블로를 조금 덜하는 대신,
요즘에는 MySQL 문제풀이와 데이터 분석 독서에 집중하고 있던 찰나에
프로그래머스에 새로운 문제들이 추가되어 다시 한번 풀어보게 되었다.
그래서 이번에도 꾸준히 문제를 레벨 별로 풀어보고
기록에 남길만한 문제들을 올려보려고 한다.
Lv.1과 Lv.2는 필요한 설명만 할 생각이다.
Let's Go!
🚘 1. 특정 옵션이 포함된 자동차 리스트 구하기
문제 설명
다음은 어느 자동차 대여 회사에서 대여중인 자동차들의 정보를 담은 CAR_RENTAL_COMPANY_CAR 테이블입니다.
CAR_RENTAL_COMPANY_CAR 테이블은 아래와 같은 구조로 되어있으며,
CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS 는 각각 자동차 ID, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트를 나타냅니다.
| Column name | Type | Nullable |
|---|---|---|
| CAR_ID | INTEGER | FALSE |
| CAR_TYPE | VARCHAR(255) | FALSE |
| DAILY_FEE | INTEGER | FALSE |
| OPTIONS | VARCHAR(255) | FALSE |
자동차 종류는 '세단', 'SUV', '승합차', '트럭', '리무진' 이 있습니다. 자동차 옵션 리스트는 콤마(',')로 구분된 키워드 리스트(옵션 리스트 값 예시: '열선시트', '스마트키', '주차감지센서')로 되어있으며, 키워드 종류는 '주차감지센서', '스마트키', '네비게이션', '통풍시트', '열선시트', '후방카메라', '가죽시트' 가 있습니다.
문제
CAR_RENTAL_COMPANY_CAR 테이블에서 '네비게이션' 옵션이 포함된 자동차 리스트를 출력하는 SQL문을 작성해주세요. 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.
예시
예를 들어 CAR_RENTAL_COMPANY_CAR 테이블이 다음과 같다면
| CAR_ID | CAR_TYPE | DAILY_FEE | OPTIONS |
|---|---|---|---|
| 1 | 세단 | 16000 | 가죽시트,열선시트,후방카메라 |
| 2 | SUV | 14000 | 스마트키,네비게이션,열선시트 |
| 3 | SUV | 22000 | 주차감지센서,후방카메라,네비게이션 |
'네비게이션' 옵션이 포함된 자동차는 자동차 ID가 2, 3인 자동차이고, 자동차 ID를 기준으로 내림차순 정렬하면 다음과 같은 결과가 나와야 합니다.
Answer
SELECT CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY CAR_ID DESC;간단하게 LIKE 구문과 정규표현식을 쓰면 되는 문제였다.
🚙 2. 자동차 대여 기록에서 장기/단기 대여 구분하기
문제 설명
다음은 어느 자동차 대여 회사의 자동차 대여 기록 정보를 담은
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블입니다.
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며,
HISTORY_ID, CAR_ID, START_DATE, END_DATE 는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.
| Column name | Type | Nullable |
|---|---|---|
| HISTORY_ID | INTEGER | FALSE |
| CAR_ID | INTEGER | FALSE |
| START_DATE | DATE | FALSE |
| END_DATE | DATE | FALSE |
문제
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문을 작성해주세요. 결과는 대여 기록 ID를 기준으로 내림차순 정렬해주세요.
예시
예를 들어 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블이 다음과 같다면
| HISTORY_ID | CAR_ID | START_DATE | END_DATE |
|---|---|---|---|
| 1 | 4 | 2022-09-27 | 2022-11-27 |
| 2 | 3 | 2022-10-03 | 2022-11-04 |
| 3 | 2 | 2022-09-05 | 2022-09-05 |
| 4 | 1 | 2022-09-01 | 2022-09-30 |
| 5 | 3 | 2022-09-16 | 2022-10-15 |
2022년 9월의 대여 기록 중 '장기 대여' 에 해당하는 기록은 대여 기록 ID가 1, 4인 기록이고, '단기 대여' 에 해당하는 기록은 대여 기록 ID가 3, 5 인 기록이므로 대여 기록 ID를 기준으로 내림차순 정렬하면 다음과 같이 나와야 합니다.
| HISTORY_ID | CAR_ID | START_DATE | END_DATE | RENT_TYPE |
|---|---|---|---|---|
| 5 | 3 | 2022-09-16 | 2022-10-13 | 단기 대여 |
| 4 | 1 | 2022-09-01 | 2022-09-30 | 장기 대여 |
| 3 | 2 | 2022-09-05 | 2022-09-05 | 단기 대여 |
| 1 | 4 | 2022-09-27 | 2022-10-26 | 장기 대여 |
주의사항
START_DATE와 END_DATE의 경우 예시의 데이트 포맷과 동일해야 정답처리 됩니다.
Answer
SELECT HISTORY_ID, CAR_ID,
DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE,
DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE,
CASE
WHEN DATEDIFF(END_DATE, START_DATE) >= 29 THEN '장기 대여'
ELSE '단기 대여'
END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE DATE_FORMAT(START_DATE, '%Y-%m') = '2022-09'
ORDER BY HISTORY_ID DESC;여기서 가장 문제는 START_DATE에 빌린 날짜가 포함되므로 30일이 아니라 29일을
'장기 대여'에 적용시켜야한다는 것이다.
또한 프로그래머스 문제의 특징으로 Date 타입의 값들이 전부 시-분-초까지 등장해
DATE_FORMAT 함수로 형태를 변형해주어야한다.
🍦 3. 과일로 만든 아이스크림 고르기
문제 설명
다음은 아이스크림 가게의 상반기 주문 정보를 담은 FIRST_HALF 테이블과 아이스크림 성분에 대한 정보를 담은 ICECREAM_INFO 테이블입니다. FIRST_HALF 테이블 구조는 다음과 같으며, SHIPMENT_ID, FLAVOR, TOTAL_ORDER 는 각각 아이스크림 공장에서 아이스크림 가게까지의 출하 번호, 아이스크림 맛, 상반기 아이스크림 총주문량을 나타냅니다. FIRST_HALF 테이블의 기본 키는 FLAVOR입니다.
| NAME | TYPE | NULLABLE |
|---|---|---|
| SHIPMENT_ID | INT(N) | FALSE |
| FLAVOR | VARCHAR(N) | FALSE |
| TOTAL_ORDER | INT(N) | FALSE |
ICECREAM_INFO 테이블 구조는 다음과 같으며, FLAVOR, INGREDITENT_TYPE 은 각각 아이스크림 맛, 아이스크림의 성분 타입을 나타냅니다. INGREDIENT_TYPE에는 아이스크림의 주 성분이 설탕이면 sugar_based라고 입력되고, 아이스크림의 주 성분이 과일이면 fruit_based라고 입력됩니다. ICECREAM_INFO의 기본 키는 FLAVOR입니다. ICECREAM_INFO테이블의 FLAVOR는 FIRST_HALF 테이블의 FLAVOR의 외래 키입니다.
| NAME | TYPE | NULLABLE |
|---|---|---|
| FLAVOR | VARCHAR(N) | FALSE |
| INGREDIENT_TYPE | VARCHAR(N) | FALSE |
문제
상반기 아이스크림 총주문량이 3,000보다 높으면서 아이스크림의 주 성분이 과일인 아이스크림의 맛을 총주문량이 큰 순서대로 조회하는 SQL 문을 작성해주세요.
예시
예를 들어 FIRST_HALF 테이블이 다음과 같고
| SHIPMENT_ID | FLAVOR | TOTAL_ORDER |
|---|---|---|
| 101 | chocolate | 3200 |
| 102 | vanilla | 2800 |
| 103 | mint_chocolate | 1700 |
| 104 | caramel | 2600 |
| 105 | white_chocolate | 3100 |
| 106 | peach | 2450 |
| 107 | watermelon | 2150 |
| 108 | mango | 2900 |
| 109 | strawberry | 3100 |
| 110 | melon | 3150 |
| 111 | orange | 2900 |
| 112 | pineapple | 2900 |
ICECREAM_INFO 테이블이 다음과 같다면
| FLAVOR | INGREDIENT_TYPE |
|---|---|
| chocolate | sugar_based |
| vanilla | sugar_based |
| mint_chocolate | sugar_based |
| caramel | sugar_based |
| white_chocolate | sugar_based |
| peach | fruit_based |
| watermelon | fruit_based |
| mango | fruit_based |
| strawberry | fruit_based |
| melon | fruit_based |
| orange | fruit_based |
| pineapple | fruit_based |
상반기 아이스크림 총주문량이 3,000보다 높은 아이스크림 맛은 chocolate, strawberry, melon, white_chocolate입니다. 이 중에 아이스크림의 주 성분이 과일인 아이스크림 맛은 strawberry와 melon이고 총주문량이 큰 순서대로 아이스크림 맛을 조회하면 melon, strawberry 순으로 조회되어야 합니다. 따라서 SQL 문을 실행하면 다음과 같이 나와야 합니다.
| FLAVOR |
|---|
| melon |
| --- |
| strawberry |
Answer
SELECT f.FLAVOR FROM FIRST_HALF f
LEFT JOIN ICECREAM_INFO i
ON f.FLAVOR = i.FLAVOR
WHERE f.TOTAL_ORDER > 3000
AND i.INGREDIENT_TYPE = 'fruit_based'
ORDER BY f.TOTAL_ORDER DESC;정확한 JOIN 구문과 조건을 적어주면 된다.
🏭 4. 경기도에 위치한 식품창고 목록 출력하기
문제 설명
다음은 식품창고의 정보를 담은 FOOD_WAREHOUSE 테이블입니다. FOOD_WAREHOUSE 테이블은 다음과 같으며 WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, TLNO, FREEZER_YN는 창고 ID, 창고 이름, 창고 주소, 전화번호, 냉동시설 여부를 의미합니다.
| Column name | Type | Nullable |
|---|---|---|
| WAREHOUSE_ID | VARCHAR(10) | FALSE |
| WAREHOUSE_NAME | VARCHAR(20) | FALSE |
| ADDRESS | VARCHAR(100) | TRUE |
| TLNO | VARCHAR(20) | TRUE |
| FREEZER_YN | VARCHAR(1) | TRUE |
문제
FOOD_WAREHOUSE 테이블에서 경기도에 위치한 창고의 ID, 이름, 주소, 냉동시설 여부를 조회하는 SQL문을 작성해주세요. 이때 냉동시설 여부가 NULL인 경우, 'N'으로 출력시켜 주시고 결과는 창고 ID를 기준으로 오름차순 정렬해주세요.
예시
FOOD_WAREHOUSE 테이블이 다음과 같을 때
| WAREHOUSE_ID | WAREHOUSE_NAME | ADDRESS | TLNO | FREEZER_YN |
|---|---|---|---|---|
| WH0001 | 창고_경기1 | 경기도 안산시 상록구 용담로 141 | 031-152-1332 | Y |
| WH0002 | 창고_충북1 | 충청북도 진천군 진천읍 씨제이로 110 | 043-623-9900 | Y |
| WH0003 | 창고_경기2 | 경기도 이천시 마장면 덕평로 811 | 031-221-7241 | NULL |
| WH0004 | 창고_경기3 | 경기도 김포시 대곶면 율생중앙로205번길 | 031-671-1900 | N |
| WH0005 | 창고_충남1 | 충청남도 천안시 동남구 광덕면 신덕리1길 9 | 041-876-5421 | Y |
SQL을 실행하면 다음과 같이 출력되어야 합니다.
| WAREHOUSE_ID | WAREHOUSE_NAME | ADDRESS | FREEZER_YN |
|---|---|---|---|
| WH0001 | 창고_경기1 | 경기도 안산시 상록구 용담로 141 | Y |
| WH0003 | 창고_경기2 | 경기도 이천시 마장면 덕평로 811 | N |
| WH0004 | 창고_경기3 | 경기도 김포시 대곶면 율생중앙로205번길 | N |
Answer
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS,
CASE WHEN FREEZER_YN IS NULL THEN 'N'
ELSE FREEZER_YN
END AS FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기%'
ORDER BY WAREHOUSE_ID ASC;
-- 내 답안
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IFNULL(FREEZER_YN, 'N')
FROM FOOD_WAREHOUSE
WHERE WAREHOUSE_NAME LIKE '%경기%'
ORDER BY WAREHOUSE_ID이번에는 두 개의 답안을 작성해보았다.
IFNULL로 간단하게 NULL 값을 'N'으로 나타나는 경우와
CASE WHEN을 사용해 좀 더 구체적으로 나타내는 경우이다.
🤝🏻 5. 조건에 부합하는 중고거래 댓글 조회하기
문제 설명
다음은 중고거래 게시판 정보를 담은 USED_GOODS_BOARD 테이블과 중고거래 게시판 첨부파일 정보를 담은 USED_GOODS_REPLY 테이블입니다. USED_GOODS_BOARD 테이블은 다음과 같으며 BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS은 게시글 ID, 작성자 ID, 게시글 제목, 게시글 내용, 가격, 작성일, 거래상태, 조회수를 의미합니다.
| Column name | Type | Nullable |
|---|---|---|
| BOARD_ID | VARCHAR(5) | FALSE |
| WRITER_ID | VARCHAR(50) | FALSE |
| TITLE | VARCHAR(100) | FALSE |
| CONTENTS | VARCHAR(1000) | FALSE |
| PRICE | NUMBER | FALSE |
| CREATED_DATE | DATE | FALSE |
| STATUS | VARCHAR(10) | FALSE |
| VIEWS | NUMBER | FALSE |
USED_GOODS_REPLY 테이블은 다음과 같으며 REPLY_ID, BOARD_ID, WRITER_ID, CONTENTS, CREATED_DATE는 각각 댓글 ID, 게시글 ID, 작성자 ID, 댓글 내용, 작성일을 의미합니다.
| Column name | Type | Nullable |
|---|---|---|
| REPLY_ID | VARCHAR(10) | FALSE |
| BOARD_ID | VARCHAR(5) | FALSE |
| WRITER_ID | VARCHAR(50) | FALSE |
| CONTENTS | VARCHAR(1000) | TRUE |
| CREATED_DATE | DATE | FALSE |
문제
USED_GOODS_BOARD와 USED_GOODS_REPLY 테이블에서 2022년 10월에 작성된 게시글 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일을 조회하는 SQL문을 작성해주세요. 결과는 댓글 작성일을 기준으로 오름차순 정렬해주시고, 댓글 작성일이 같다면 게시글 제목을 기준으로 오름차순 정렬해주세요.
예시
USED_GOODS_BOARD 테이블이 다음과 같고
| BOARD_ID | WRITER_ID | TITLE | CONTENTS | PRICE | CREATED_DATE | STATUS | VIEWS |
|---|---|---|---|---|---|---|---|
| B0001 | kwag98 | 반려견 배변패드 팝니다 | 정말 저렴히 판매합니다. 전부 미개봉 새상품입니다. | 12000 | 2022-10-01 | DONE | 250 |
| B0002 | lee871201 | 국내산 볶음참깨 | 직접 농사지은 참깨입니다. | 3000 | 2022-10-02 | DONE | 121 |
| B0003 | goung12 | 배드민턴 라켓 | 사놓고 방치만 해서 팝니다. | 9000 | 2022-10-02 | SALE | 212 |
| B0004 | keel1990 | 디올 귀걸이 | 신세계강남점에서 구입. 정품 아닐시 백퍼센트 환불 | 130000 | 2022-10-02 | SALE | 199 |
| B0005 | haphli01 | 스팸클래식 팔아요 | 유통기한 2025년까지에요 | 10000 | 2022-10-02 | SALE | 121 |
USED_GOODS_REPLY 테이블이 다음과 같을 때
| REPLY_ID | BOARD_ID | WRITER_ID | CONTENTS | CREATED_DATE |
|---|---|---|---|---|
| R000000001 | B0001 | s2s2123 | 구매하겠습니다. 쪽지 드립니다. | 2022-10-02 |
| R000000002 | B0002 | hoho1112 | 쪽지 주세요. | 2022-10-03 |
| R000000003 | B0006 | hwahwa2 | 삽니다. 연락주세요. | 2022-10-03 |
| R000000004 | B0007 | hong02 | 예약중 | 2022-10-06 |
| R000000005 | B0009 | hanju23 | 구매완료 | 2022-10-07 |
SQL을 실행하면 다음과 같이 출력되어야 합니다.
| TITLE | BOARD_ID | REPLY_ID | WRITER_ID | CONTENTS | CREATED_DATE |
|---|---|---|---|---|---|
| 반려견 배변패드 팝니다 | B0001 | R000000001 | s2s2123 | 구매하겠습니다. 쪽지 드립니다. | 2022-10-02 |
| 국내산 볶음참깨 | B0002 | R000000002 | hoho1112 | 쪽지 주세요. | 2022-10-03 |
주의사항
CREATED_DATE의 포맷이 예시의 포맷과 일치해야 정답처리 됩니다.
Answer
SELECT b.TITLE, r.BOARD_ID, r.REPLY_ID, r.WRITER_ID, r.CONTENTS,
DATE_FORMAT(r.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD b
JOIN USED_GOODS_REPLY r
ON b.BOARD_ID = r.BOARD_ID
WHERE b.CREATED_DATE LIKE '2022-10%'
ORDER BY 6 ASC, 1 ASC;이 문제는 Lv.1 이라고 하기에는 좀 애매한 문제였다.
일단 작성된 게시글과 답글이 달린 게시글이 다르기때문에 그것을 신경써주어 쿼리를 짜주어야 하고
두번째는 LEFT JOIN을 사용했을 때는 정답처리가 되지 않아
혹시나 하는 마음에 확실한 INNER JOIN을 사용하니 정답처리가 되었다.
이 문제는 좀 더 연구해봐야할 것 같다.
다음에는 Lv.2로 돌아오겠다!!
