프로그래머스 SQL 고득점 Kit - SELECT
문제리스트
- 평균 일일 대여 요금 구하기
- 과일로 만든 아이스크림 만들기
- 3월에 태어난 여성 회원 목록 출력하기
- 서울에 위치한 식당 목록 출력하기
문제1. 평균 일일 대여 요금 구하기
문제 설명
CAR_RENTAL_COMPANY_CAR 테이블에서 자동차 종류가 'SUV'인 자동차들의 평균 일일 대여 요금을 출력하는 SQL문을 작성해주세요. 이때 평균 일일 대여 요금은 소수 첫 번째 자리에서 반올림하고, 컬럼명은 AVERAGE_FEE 로 지정해주세요.
예시
예를 들어 CAR_RENTAL_COMPANY_CAR 테이블이 다음과 같다면
CAR_ID CAR_TYPE DAILY_FEE OPTIONS
| 1 | 세단 | 16000 | 가죽시트,열선시트,후방카메라 |
| 2 | SUV | 14000 | 스마트키,네비게이션,열선시트 |
| 3 | SUV | 22000 | 주차감지센서,후방카메라,가죽시트 |
'SUV' 에 해당하는 자동차들의 평균 일일 대여 요금은 18,000 원 이므로, 다음과 같은 결과가 나와야 합니다.
AVERAGE_FEE
18000
문제 해결 방법1.
SELECT
ROUND(AVG(DAILY_FEE), 0) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV';
이렇게 까진 쉽지만,
새로운 칼럼을 결과까지 실험해보자.
윈도우 함수를 이용해야 한다.
코드는 다음과 같이 이용해야 한다.
-- 코드를 입력하세요
SELECT
*,
AVG(DAILY_FEE) OVER (PARTITION BY CAR_TYPE ORDER BY CAR_ID
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
;코드가 길지만, 익숙해지기 위해서 끊임없이 연습해야 한다.
스터디 회의에서 윈도우 함수에 대해 구체적으로 다루어야겠다.
문제2. 과일로 만든 아이스크림 만들기
문제 설명
다음은 아이스크림 가게의 상반기 주문 정보를 담은 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 문을 작성해주세요.
이 문제를 처음 풀 땐, inner join을 활용해서 풀어야한다고 생각했다.
그리고 where 절로 조건에 맞는 값들을 추출해야한다고 생각했었다.
풀이는 다음과 같다.
-- 처음 풀이
-- INNER JOIN 을 활용한 풀이
SELECT
F.FLAVOR
FROM
FRUIT_HALF F INNER JOIN ICECREAM_INFO I
ON F.FLAVOR = I.FLAVOR
WHERE F.TOTAL_ORDER > 3000
AND I.INGREDIENT_TYPE = 'fruit_based'
;물론 이 풀이도 맞지만,
더 효율적인 풀이를 위해 실험을 한다.
굳이 JOIN을 활용하여야 할까?
JOIN을 하지 않고도, 추출할 수 있지 않을까?
가능하다. 두 개의 DB에서 JOIN을 하지 않고도, 각각의 DB에 맞는 조건을 추출하여, 값을 불러올 수 있다.
풀이는 다음과 같다.
SELECT
F.FLAVOR
FROM FIRST_HALF F, ICECREAM_INFO I
WHERE
F.FLAVOR = I.FLAVOR
AND F.TOTAL_ORDER >= 3000
AND I.INGREDIENT_TYPE = 'fruit_based'
;새롭게 알게된 것
- DB를 불러올 때, 동시에 2개를 불러올 수 있다.
- 2개의 DB를 불러와서, 동시에 조건을 걸어줄 수도 있다.
문제3. 3월에 태어난 여성 회원 목록 출력하기
문제 설명
다음은 식당 리뷰 사이트의 회원 정보를 담은 MEMBER_PROFILE 테이블입니다. MEMBER_PROFILE 테이블은 다음과 같으며 MEMBER_ID, MEMBER_NAME, TLNO, GENDER, DATE_OF_BIRTH는 회원 ID, 회원 이름, 회원 연락처, 성별, 생년월일을 의미합니다.
Column name Type Nullable
| MEMBER_ID | VARCHAR(100) | FALSE |
| MEMBER_NAME | VARCHAR(50) | FALSE |
| TLNO | VARCHAR(50) | TRUE |
| GENDER | VARCHAR(1) | TRUE |
| DATE_OF_BIRTH | DATE | TRUE |
문제
MEMBER_PROFILE 테이블에서 생일이 3월인 여성 회원의 ID, 이름, 성별, 생년월일을 조회하는 SQL문을 작성해주세요. 이때 전화번호가 NULL인 경우는 출력대상에서 제외시켜 주시고, 결과는 회원ID를 기준으로 오름차순 정렬해주세요.
예시
MEMBER_PROFILE 테이블이 다음과 같을 때
MEMBER_ID MEMBER_NAME TLNO GENDER DATE_OF_BIRTH
| jiho92@naver.com | 이지호 | 01076432111 | W | 1992-02-12 |
| jiyoon22@hotmail.com | 김지윤 | 01032324117 | W | 1992-02-22 |
| jihoon93@hanmail.net | 김지훈 | 01023258688 | M | 1993-02-23 |
| seoyeons@naver.com | 박서연 | 01076482209 | W | 1993-03-16 |
| yoonsy94@gmail.com | 윤서연 | NULL | W | 1994-03-19 |
SQL을 실행하면 다음과 같이 출력되어야 합니다.
MEMBER_ID MEMBER_NAME GENDER DATE_OF_BIRTH
| seoyeons@naver.com | 박서연 | W | 1993-03-16 |
주의사항
DATE_OF_BIRTH의 데이트 포맷이 예시와 동일해야 정답처리 됩니다.
이 문제는 DB에서 조건에 맞는 데이터를 추출하는 단순한 문제이다.
가장 중요한 요건은 DATE_OF_BIRTH 칼럼에서 정답의 포맷을 맞춰줘야 한다.
간단하게 날짜 형식에 대해서 짚고 넘어가자.
DATE_FORMAT함수 이용방법
DATE_FORMAT(날짜, 형식) : 날짜를 지정한 형식으로 출력
예시) 1999년 3월 18일 00시 00분 00초
이것을 활용해서 우리는 정답의 포맷 '1993-03-16'으로 맞춰보자.
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 ;
새롭게 알게된 점
- 날짜 type에서 월 또는 일을 추출할 땐, Month() 함수를 사용해야한다.
- → 괜히 string으로 추출하겠다고, SUBSTRING_INDEX(column_name, 구분할 문자, 가지고 올 위치)를 사용하면 안된다.
- 날짜 type을 맞춰주는 함수는 DATE_FORMAT(column_name, “%Y-%m-%d”)를 활용한다.
- → 여기서 Y,m,d가 대문자인지 소문자인지에 따라서도 추출되는 방법이 다르다.
문제4. 서울에 위치한 식당 목록 출력하기
문제 설명
다음은 식당의 정보를 담은 REST_INFO 테이블과 식당의 리뷰 정보를 담은 REST_REVIEW 테이블입니다. REST_INFO 테이블은 다음과 같으며 REST_ID, REST_NAME, FOOD_TYPE, VIEWS, FAVORITES, PARKING_LOT, ADDRESS, TEL은 식당 ID, 식당 이름, 음식 종류, 조회수, 즐겨찾기수, 주차장 유무, 주소, 전화번호를 의미합니다.
Column name Type Nullable
| REST_ID | VARCHAR(5) | FALSE |
| REST_NAME | VARCHAR(50) | FALSE |
| FOOD_TYPE | VARCHAR(20) | TRUE |
| VIEWS | NUMBER | TRUE |
| FAVORITES | NUMBER | TRUE |
| PARKING_LOT | VARCHAR(1) | TRUE |
| ADDRESS | VARCHAR(100) | TRUE |
| TEL | VARCHAR(100) | TRUE |
REST_REVIEW 테이블은 다음과 같으며 REVIEW_ID, REST_ID, MEMBER_ID, REVIEW_SCORE, REVIEW_TEXT,REVIEW_DATE는 각각 리뷰 ID, 식당 ID, 회원 ID, 점수, 리뷰 텍스트, 리뷰 작성일을 의미합니다.
Column name Type Nullable
| REVIEW_ID | VARCHAR(10) | FALSE |
| REST_ID | VARCHAR(10) | TRUE |
| MEMBER_ID | VARCHAR(100) | TRUE |
| REVIEW_SCORE | NUMBER | TRUE |
| REVIEW_TEXT | VARCHAR(1000) | TRUE |
| REVIEW_DATE | DATE | TRUE |
문제
REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.
예시
REST_INFO 테이블이 다음과 같고
REST_ID REST_NAME FOOD_TYPE VIEWS FAVORITES PARKING_LOT ADDRESS TEL
| 00028 | 대우부대찌개 | 한식 | 52310 | 10 | N | 경기도 용인시 처인구 남사읍 처인성로 309 | 031-235-1235 |
| 00039 | 광주식당 | 한식 | 23001 | 20 | N | 경기도 부천시 산업로8번길 60 | 031-235-6423 |
| 00035 | 삼촌식당 | 일식 | 532123 | 80 | N | 서울특별시 강서구 가로공원로76가길 | 02-135-1266 |
REST_REVIEW 테이블이 다음과 같을 때
REVIEW_ID REST_ID MEMBER_ID REVIEW_SCORE REVIEW_TEXT REVIEW_DATE
| R000000065 | 00028 | soobin97@naver.com | 5 | 부찌 국물에서 샤브샤브 맛이나고 깔끔 | 2022-04-12 |
| R000000066 | 00039 | yelin1130@gmail.com | 5 | 김치찌개 최곱니다. | 2022-02-12 |
| R000000067 | 00028 | yelin1130@gmail.com | 5 | 햄이 많아서 좋아요 | 2022-02-22 |
| R000000068 | 00035 | ksyi0316@gmail.com | 5 | 숙성회가 끝내줍니다. | 2022-02-15 |
| R000000069 | 00035 | yoonsy95@naver.com | 4 | 비린내가 전혀없어요. | 2022-04-16 |
SQL을 실행하면 다음과 같이 출력되어야 합니다.
이 문제의 해결방법은 다음과 같다.
- REST_ID를 기준으로 NULL값이 없는 것들로 INNER JOIN
- '서울'을 포함하는 것들을 추출
- REST_ID를 기준으로 평점 평균처리 및 반올림
- 내림차순
풀이는 다음과 같다.
SELECT
I.REST_ID,
I.REST_NAME,
I.FOOD_TYPE,
I.FAVORITES,
I.ADDRESS,
ROUND(AVG(R.REVIEW_SCORE), 2) AS SCORE
FROM REST_INFO I JOIN REST_REVIEW R
ON I.REST_ID = R.REST_ID
WHERE I.ADDRESS LIKE '서울%'
GROUP BY I.REST_ID
ORDER BY SCORE DESC, I.FAVORITES DESC
;여기서 주의해야 할 점은
'%서울%'이 아니라 '서울%'로 LIKE 절을 수행해야 한다.
처음엔 이것 때문에 에러가 많이 나서 당황했었다.
'Query' 카테고리의 다른 글
| [프로그래머스]조건에 맞는 개발자 찾기, 대장균들의 자식의 수 구하기 (1) | 2024.12.11 |
|---|---|
| [프로그래머스]상위 n개 레코드, 업그레이드 된 아이템 구하기 (1) | 2024.12.10 |
| [프로그래머스]재구매가 일어난 상품과 회원 리스트 구하기, 오프라인/온라인 판매 데이터 통합하기 (2) | 2024.12.09 |
| [프로그래머스] 12세 이하인 여자 환자 목록 출력하기, 조건에 부합하는 중고거래 댓글 조회하기 (5) | 2024.12.08 |
| [OT]Make Query Club for my own (2) | 2024.12.06 |
