Programmers : MySQL SELECT

김소정·2023년 10월 26일
TILsql
0

Problem Solving (SQL)

목록 보기
5/6
post-thumbnail

#1 12세 이하인 여자 환자 목록 출력하기

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

풀이

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;

IFNULL(컬럼명, 'NULL일 시 대체할 값')

  • 전화번호가 없을 시, NULL 값 대신 반환할 텍스트를 지정해주었다.
  • ISNULL과 헷갈리지 않게 유의해야 한다.
    ISNULL은 MSSQL에서 사용하는 문법이고, MySQL은 IFNULL이다.

#2 인기있는 아이스크림

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

풀이

SELECT FLAVOR FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID;

정렬의 우선순위가 높을수록 앞에 적어주어야 한다. ORDER BY의 정렬 default 값은 오름차순(ASC)이므로, 내림차순 정렬할 때는 꼭 컬럼명 뒤에 DESC를 붙여주자.


#3 재구매가 일어난 상품과 회원 리스트 구하기

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

풀이

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

재구매 여부를 확인해야 하므로, 회원 ID와 상품 ID의 조합이 2번 이상 존재하는지 확인해야 한다. 따라서 (1) GROUP BY를 통해 회원 ID와 상품 ID를 묶어주고 (2) HAVING 절로 그룹화에 조건을 부여함으로써 레코드가 2개 이상인 것만 출력하도록 지정해주었다.


#4 조건에 맞는 회원수 구하기

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

풀이

SELECT COUNT(*) AS USERS FROM USER_INFO
WHERE YEAR(JOINED) = 2021
AND AGE BETWEEN 20 AND 29;

데이터 타입이 DATE인 경우, YEAR, MONTH, DAY 함수를 이용해 년, 월, 일을 출력할 수 있다. 또한 범위를 지정할 때는 BETWEEN A AND B를 사용해주면 된다.

cf. MySQL에서는 기본적인 부등호를 통한 범위 지정도 가능하다.

#5 평균 일일 대여 요금 구하기

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

풀이

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

ROUND(컬럼명, 반올림할 자릿수)

  • 반올림할 자릿수를 따로 지정하지 않을 시엔 첫 번째 자리에서 반올림된다.
  • ex. ROUND(AVG(DAILY_FEE), 3) 소수점 세 번째 자리에서 반올림 해준다. 즉 소수점 두 번째 자리까지 출력된다.

#6 오프라인/온라인 판매 데이터 통합하기

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

풀이

(
    SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') SALES_DATE
        , PRODUCT_ID
        , USER_ID
        , SALES_AMOUNT
    FROM ONLINE_SALE
    WHERE YEAR(SALES_DATE) = 2022 AND MONTH(SALES_DATE) = 03
)
UNION ALL
(
    SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') SALES_DATE
        , PRODUCT_ID
        , NULL AS USER_ID
        , SALES_AMOUNT
    FROM OFFLINE_SALE
    WHERE YEAR(SALES_DATE) = 2022 AND MONTH(SALES_DATE) = 03
)
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;

처음에는 JOIN을 사용해서 푸는 문제인 줄 알았는데, 알고보니 서브쿼리를 만들어서 UNION으로 합치는 문제였다.

  • ONLINE_SALE 테이블의 3월 판매 데이터와 OFFLINE_SALE 테이블의 3월 판매 데이터를 추출해서 UNION ALL로 합친다. 중복 데이터가 존재하지 않기 때문에 UNION ALL과 UNION 모두 사용해도 된다.
  • 컬럼 수가 동일하지 않으면 에러가 발생하기 때문에 문제에서 이야기한대로 OFFLINE_SALE 테이블의 서브쿼리를 만들 때는 USER_ID를 NULL 값으로 설정해준다.

#7 조건에 맞는 도서 리스트 출력하기

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

풀이

SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK
WHERE YEAR(PUBLISHED_DATE) = 2021 
    AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE;

출력할 때 DATE_FORMAT을 이용해서 2021-10-24 형태로 출력해주는 것 잊지 않기. 만약 DATE의 데이터 타입을 가지고 있는 PUBLISHED_DATE 컬럼을 그대로 출력해주면 2021-10-24 00:00:00의 형태로 출력된다.


#8 3월에 태어난 여성 회원 목록 출력하기

MEMBER_PROFILE 테이블에서 생일이 3월인 여성 회원의 ID, 이름, 성별, 생년월일을 조회하는 SQL문을 작성해주세요. 이때 전화번호가 NULL인 경우는 출력대상에서 제외시켜 주시고, 결과는 회원ID를 기준으로 오름차순 정렬해주세요.

풀이

SELECT MEMBER_ID
    , MEMBER_NAME
    , GENDER
    , DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE GENDER = 'W'
    AND MONTH(DATE_OF_BIRTH) = 3
    AND TLNO IS NOT NULL
ORDER BY MEMBER_ID;

NULL인 경우를 제외하기 위해서는 IS NOT NULL을 통해 NULL이 아닌 경우만 출력해주면 된다. NULL에 관련된 주요 문법 세 가지(IFNULL, IS NULL, IS NOT NULL)를 잊지 말자.


#9 과일로 만든 아이스크림 고르기

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

풀이

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

테이블을 조인할 때는 보통 별칭을 지정해주는 게 편리하다. 조인하는 테이블이 많아지는 경우에는 A, B, C와 같이 간단하게 보다는 실제 테이블명과 유사하거나 직관적으로 알 수 있는 별칭을 사용해주는 게 좋다.


#10 조건에 부합하는 중고거래 댓글 조회하기

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

풀이

SELECT A.TITLE
    , A.BOARD_ID
    , B.REPLY_ID
    , B.WRITER_ID
    , B.CONTENTS
    , DATE_FORMAT(B.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD A JOIN USED_GOODS_REPLY B
ON A.BOARD_ID = B.BOARD_ID AND A.CREATED_DATE <= '2022-10-31'
WHERE DATE_FORMAT(A.CREATED_DATE, '%Y-%m') = '2022-10'
ORDER BY B.CREATED_DATE ASC, A.TITLE ASC;

날짜 범위를 지정할 때 부등호를 사용했다.

  • A.CREATED_DATE <= '2022-10-31'은 2022년 10월 31일을 포함해, 그 이전에 작성된 데이터만 불러오는 구문이다.

#11 흉부외과 또는 일반외과 의사 목록 출력하기

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

풀이

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

진료과를 확인하는 조건절에서 등호를 이용해도 되지만, 진료과의 범위가 넓어지는 경우에는 하나씩 등호를 사용해 OR로 연결하면 조건절이 너무 길어지고 코드의 가독성이 떨어질 수 있다. 이럴 때는 IN 연산자를 사용하면 직관적이고 편리하다.


#12 강원도에 위치한 생산공장 목록 출력하기

FOOD_FACTORY 테이블에서 강원도에 위치한 식품공장의 공장 ID, 공장 이름, 주소를 조회하는 SQL문을 작성해주세요. 이때 결과는 공장 ID를 기준으로 오름차순 정렬해주세요.

풀이

/*
[LIKE , NOT LIKE 사용해 특정 문자 포함 데이터 확인 및 제외하고 출력 실시]
1. LIKE : 특정 문자를 포함하는 데이터를 출력합니다
2. NOT LIKE : 특정 문자를 포함하지 않는 데이터를 출력합니다
3. 컬럼 LIKE '문자%' : 특정 문자로 시작하는 데이터 확인
4. 컬럼 LIKE '%문자' : 특정 문자로 종료하는 데이터 확인
5. 컬럼 LIKE '%문자%' : 문자 시작 ~ 종료까지 특정 문자 포함 여부 확인
*/

SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE '%강원도%'
ORDER BY FACTORY_ID ASC;

문자열 포함 여부를 확인할 때는 다음의 두 가지 방법을 활용할 수 있다.

  1. LIKE

    • LIKE '%문자': 특정 문자로 시작하는 데이터 확인
    • LIKE '문자%': 특정 문자로 끝나는 데이터 확인
    • LIKE '%문자%': 특정 문자가 포함되어 있는 데이터 확인

    cf. NOT LIKE를 사용해서 특정 문자열을 포함하지 않는 데이터를 출력할 수도 있다.

  2. REGEXP

    • 보다 복잡한 패턴을 검색할 때 사용한다. 복수개의 특정 문자열 포함 여부를 확인할 수 있다.
    • REGEXP '특정 문자열 1|특정 문자열 2'와 같이 사용해주면 된다.

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

REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.

풀이

SELECT A.REST_ID
    , A.REST_NAME
    , A.FOOD_TYPE
    , A.FAVORITES
    , A.ADDRESS
    , ROUND(AVG(B.REVIEW_SCORE), 2) AS SCORE
FROM REST_INFO A RIGHT JOIN REST_REVIEW B
ON A.REST_ID = B.REST_ID
WHERE A.ADDRESS LIKE '서울%'
GROUP BY B.REST_ID
ORDER BY SCORE DESC, FAVORITES DESC;

GROUP BY로 그룹을 묶어주고, 집계 함수(SUM, AVG, MAX, COUNT 등)를 사용해 열 데이터를 집계해준다.

이 문제에서도 리뷰 평균 점수를 조회하기 위해서 식당 ID(REST_ID)를 기준으로 그룹화를 해주고 평균을 구하는 AVG 집계 함수를 사용해주었다.


Reference

https://school.programmers.co.kr/learn/courses/30/parts/17042

profile
김소정
Yonsei University, Applied Statistics
이전 포스트

Programmers: MySQL Lv. 4

다음 포스트

Programmers : MySQL MAX & IS NULL

0개의 댓글