SQL 정리 - SELECT(2)

Rael·2023년 3월 16일
sql코딩테스트
0

SQL 정리

목록 보기
4/4

프로그래머스 SQL 고득점 KIT - SELECT 편

1. 과일로 만든 아이스크림 고르기(Lv.1)

[문제]
상반기 아이스크림 총주문량이 3,000보다 높으면서 아이스크림의 주 성분이 과일인 아이스크림의 맛을 총주문량이 큰 순서대로 조회하는 SQL 문을 작성해주세요.

[풀이]
JOIN을 활용하여 두 테이블을 연결해 원하는 데이터를 조회하는 문제이다.
이 문제에서는 문제에서 외래키로 명시한 FLAVOR 컬럼으로 테이블을 연결했다.

SELECT fh.FLAVOR
FROM FIRST_HALF fh
    LEFT JOIN ICECREAM_INFO ii
    ON fh.FLAVOR = ii.FLAVOR
WHERE TOTAL_ORDER > 3000 AND INGREDIENT_TYPE = 'fruit_based'
ORDER BY TOTAL_ORDER DESC;

2. 조건에 부합하는 중고거래 댓글 조회하기(Lv.1)

[문제]
USED_GOODS_BOARD와 USED_GOODS_REPLY 테이블에서 2022년 10월에 작성된 게시글 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일을 조회하는 SQL문을 작성해주세요.
결과는 댓글 작성일을 기준으로 오름차순 정렬해주시고, 댓글 작성일이 같다면 게시글 제목을 기준으로 오름차순 정렬해주세요.

[풀이]
JOIN을 사용하여 게시글과 댓글 테이블을 연결하고,
LIKE를 사용하여 원하는 날짜를 조회하는 문제이다.

해당 문제에서는 시행착오가 존재했으나, 먼저 정답은 다음과 같다. 

SELECT TITLE, ugb.BOARD_ID, REPLY_ID, ugr.WRITER_ID, ugr.CONTENTS, date_format(ugr.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD ugb
    RIGHT JOIN USED_GOODS_REPLY ugr
    ON ugb.BOARD_ID = ugr.BOARD_ID
WHERE ugb.CREATED_DATE LIKE '2022-10-%'
ORDER BY ugr.CREATED_DATE, TITLE ASC;

[시행착오]
처음 이 문제를 LEFT JOIN으로 접근하여 발생한 문제이다.

위와 같이 LEFT JOIN으로 문제를 풀었을 때, 게시글을 기준으로 JOIN 되기 때문에 댓글이 없는 게시글까지 조회하게 된다.
댓글이 존재하는 게시글을 찾기 위해서는 댓글을 기준으로 RIGHT JOIN을 해주어야 원하는 데이터를 조회할 수 있다.

3. 12세 이하인 여자 환자 목록 출력하기(Lv.1)

[문제]
PATIENT 테이블에서 12세 이하인 여자환자의 환자이름, 환자번호, 성별코드, 나이, 전화번호를 조회하는 SQL문을 작성해주세요.
이때 전화번호가 없는 경우, 'NONE'으로 출력시켜 주시고 결과는 나이를 기준으로 내림차순 정렬하고, 나이 같다면 환자이름을 기준으로 오름차순 정렬해주세요.

[풀이]
어렵지 않으나, IFNULL을 몰랐다면 풀기 곤란한 문제이다.

SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, "NONE") AS TLNO
FROM PATIENT
WHERE AGE<=12 AND GEND_CD='W'
ORDER BY AGE DESC, PT_NAME ASC;

⭐️ IFNULL

  • 만약 해당 컬럼에 NULL이 존재한다면 칸을 비워두는 대신 다른 문자열로 대체하는 기능을 한다.
  • IFNULL([컬럼 명], ["대체할 문자열"])

4. 흉부외과 또는 일반외과 의사 목록 출력하기(Lv.1)

[문제]
DOCTOR 테이블에서 진료과가 흉부외과(CS)이거나 일반외과(GS)인 의사의 이름, 의사ID, 진료과, 고용일자를 조회하는 SQL문을 작성해주세요.
이때 결과는 고용일자를 기준으로 내림차순 정렬하고, 고용일자가 같다면 이름을 기준으로 오름차순 정렬해주세요.

[풀이]
date_format을 활용하면 어려움 없이 풀 수 있는 문제이다.

SELECT DR_NAME, DR_ID, MCDP_CD, date_format(HIRE_YMD, '%Y-%m-%d') AS HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD IN ('CS', 'GS')
ORDER BY HIRE_YMD DESC, DR_NAME ASC;

여기서 조금의 tip이 있다면,
이 문제와 같이 OR로 고려해야 할 조건들이 2개 이상이라면
OR 대신 IN()을 사용하는 것이 훨씬 간편하다.

  • [컬럼 명] IN([조건 1], [조건 2])
  • 컬럼에 조건들이 해당한다면 TRUE

5. 평균 일일 대여 요금 구하기(Lv.1)

[문제]
CAR_RENTAL_COMPANY_CAR 테이블에서 자동차 종류가 'SUV'인 자동차들의 평균 일일 대여 요금을 출력하는 SQL문을 작성해주세요.
이때 평균 일일 대여 요금은 소수 첫 번째 자리에서 반올림하고, 컬럼명은 AVERAGE_FEE 로 지정해주세요.

[풀이]
ROUND가 반올림을 수행하는 것을 알면 쉽게 풀 수 있다.

SELECT ROUND(AVG(DAILY_FEE),0) AS AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV'

ROUND

  • ROUND([반올림할 값], [남길 소수 자리])
  • ROUND(50.56, 1) => 50.6

6. 재구매가 일어난 상품과 회원 리스트 구하기(Lv.2)

[문제]
ONLINE_SALE 테이블에서 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성해주세요.
결과는 회원 ID를 기준으로 오름차순 정렬해주시고 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬해주세요.

[풀이]
GROUP BY, HAVING 절을 사용하면 쉽게 풀 수 있는 문제이다.

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(USER_ID) >= 2
ORDER BY USER_ID ASC, PRODUCT_ID DESC;

GROUP BY

  • 원하는 컬럼(여러개도 가능) 내의 값들을 각 그룹으로 묶을 수 있다.
  • 사용 예)
    GROUP BY [컬럼 명]

HAVING

  • WHERE 절에서는 집계 함수를 사용할 수 없다.
  • 반면 HAVING 절에서는 집계 함수를 가지고 조건비교를 할 때 사용한다.
  • HAVING 절은 무조건 GROUP BY와 함께 사용된다.
  • 사용 예)
    HAVING COUNT([컬럼 명]) >= 2

7. 오프라인/온라인 판매 데이터 통합하기(Lv.4)

[문제]
ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요.
OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요.
결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 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

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, PRODUCT_ID, USER_ID

UNION

  • 서로 다른 두 쿼리의 결과를 합한다.
  • 중복된 row 제거

UNION ALL

  • UNION과 마찬가지로 두 쿼리의 결과를 합한다.
  • 중복된 row를 제거하지 않는다.
  • 중복을 제거하지 않으므로 UNION 보다 처리 속도가 빠르다.

8. 조건에 맞는 회원수 구하기(Lv.1)

[문제]
USER_INFO 테이블에서 2021년에 가입한 회원 중 나이가 20세 이상 29세 이하인 회원이 몇 명인지 출력하는 SQL문을 작성해주세요.

[풀이]
FLOOR만 활용하면 쉽게 풀 수 있는 문제였다.
FLOOR를 활용하지 않아도, BETWEEN을 알면 풀 수 있다.

SELECT COUNT(*) AS USERS
FROM USER_INFO
WHERE FLOOR(AGE/10) = 2 AND JOINED LIKE '2021-%'

FLOOR

  • 올림을 수행한다.
  • FLOOR([값])

또 다른 풀이 방법
나누기 + 올림 + 비교 방식으로 나이를 구하지 않고,
BETWEEN 20 AND 29를 사용하여 문제를 풀 수 있다.

9. 인기있는 아이스크림(Lv.1)

[문제]
상반기에 판매된 아이스크림의 맛을 총주문량을 기준으로 내림차순 정렬하고 총주문량이 같다면 출하 번호를 기준으로 오름차순 정렬하여 조회하는 SQL 문을 작성해주세요.

[풀이]
ORDER BY를 활용하면 쉽게 풀 수 있는 문제이다.

SELECT FLAVOR
FROM first_half
ORDER BY total_order desc, shipment_id asc;

10. 조건에 맞는 도서 리스트 출력하기(Lv.1)

[문제]
BOOK 테이블에서 2021년에 출판된 '인문' 카테고리에 속하는 도서 리스트를 찾아서 도서 ID(BOOK_ID), 출판일 (PUBLISHED_DATE)을 출력하는 SQL문을 작성해주세요.
결과는 출판일을 기준으로 오름차순 정렬해주세요.

[풀이]
LIKE를 활용하면 쉽게 풀 수 있는 문제이다.
또한, 날짜가 존재하는 문제에서는 거의 date_format를 사용하니 암기하는 것이 좋다.

SELECT BOOK_ID, date_format(PUBLISHED_DATE, '%Y-%m-%d')
FROM book
WHERE published_date LIKE '2021-%' and category = '인문'
ORDER BY published_date asc;
profile
Rael
이전 포스트

SQL 정리 - GROUP BY

0개의 댓글