[SQL] SQL 고득점 Kit

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit

언어 : MySQL

SELECT

# 1. 인기있는 아이스크림
SELECT FLAVOR 
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC;

# 2. 모든 레코드 조회하기
SELECT * FROM ANIMAL_INS ORDER BY ANIMAL_ID;

# 3. 오프라인/온라인 판매 데이터 통합하기
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') SALES_DATE, 
        PRODUCT_ID,
        USER_ID, 
        SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE >= '2022-03-01' AND SALES_DATE < '2022-04-01'
UNION ALL
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') SALES_DATE, 
        PRODUCT_ID,
        NULL AS USER_ID,
        SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE >= '2022-03-01' AND SALES_DATE < '2022-04-01'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;

# 4. 아픈 동물 찾기
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION = 'Sick'
ORDER BY ANIMAL_ID;

# 5. 어린 동물 찾기
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'
ORDER BY ANIMAL_ID;

# 6. 과일로 만든 아이스크림 고르기
SELECT F.FLAVOR
FROM FIRST_HALF F
JOIN ICECREAM_INFO I ON F.FLAVOR = I.FLAVOR
WHERE TOTAL_ORDER > 3000 AND INGREDIENT_TYPE='fruit_based'
ORDER BY TOTAL_ORDER DESC;

# 7. 강원도에 위치한 생산공장 목록 출력하기
SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE '강원도%'
ORDER BY FACTORY_ID;

# 8. 서울에 위치한 식당 목록 출력하기
SELECT I.REST_ID, 
        REST_NAME, 
        FOOD_TYPE, 
        FAVORITES,
        ADDRESS,
        ROUND(AVG(REVIEW_SCORE),2) AS SCORE
FROM REST_INFO I
JOIN REST_REVIEW R ON I.REST_ID = R.REST_ID
WHERE ADDRESS LIKE '서울%'
GROUP BY I.REST_ID
ORDER BY SCORE DESC, FAVORITES DESC;

# 9. 조건에 부합하는 중고거래 댓글 조회하기
SELECT B.TITLE, B.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 >= '2022-10-01' AND B.CREATED_DATE < '2022-11-01'
ORDER BY R.CREATED_DATE, B.TITLE;

# 10. 조건에 맞는 도서 리스트 출력하기
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;

SUM, MAX, MIN

# 1. 가격이 제일 비싼 식품의 정보 출력하기
SELECT * FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT);

# 2. 최댓값 구하기
SELECT MAX(DATETIME) FROM ANIMAL_INS;

# 3. 최솟값 구하기
SELECT MIN(DATETIME) FROM ANIMAL_INS;

# 4. 동물 수 구하기
SELECT COUNT(ANIMAL_ID) FROM ANIMAL_INS;

# 5. 중복 제거하기
SELECT COUNT(DISTINCT NAME) FROM ANIMAL_INS;

# 6. 가장 비싼 상품 구하기
SELECT MAX(price) AS MAX_PRICE from PRODUCT;

GROUP BY

# 1. 저자 별 카테고리 별 매출액 집계하기
SELECT A.AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM((PRICE*SALES)) AS TOTAL_SALES
FROM BOOK_SALES S
JOIN BOOK B ON B.BOOK_ID = S.BOOK_ID
JOIN AUTHOR A ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE YEAR(SALES_DATE)=2022 AND MONTH(SALES_DATE)=1
GROUP BY A.AUTHOR_ID, CATEGORY
ORDER BY A.AUTHOR_ID, CATEGORY DESC;

# 2. 성분으로 구분한 아이스크림 총 주문량
SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM ICECREAM_INFO I
JOIN FIRST_HALF F ON F.FLAVOR = I.FLAVOR
GROUP BY INGREDIENT_TYPE
ORDER BY TOTAL_ORDER;

# 3. 고양이와 개는 몇 마리 있을까
SELECT ANIMAL_TYPE, COUNT(ANIMAL_ID) AS count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;

# 4. 동명 동물 수 찾기
SELECT NAME, COUNT(NAME) AS 'COUNT'
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) >= 2
ORDER BY NAME;

# 5. 입양 시각 구하기(1)
SELECT HOUR(DATETIME) AS 'HOUR', COUNT(ANIMAL_ID) AS 'COUNT'
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME)
HAVING HOUR >= 9 AND HOUR < 20
ORDER BY HOUR(DATETIME)

# 6. 카테고리 별 도서 판매량 집계하기
SELECT CATEGORY, SUM(SALES) AS TOTAL_SALES
FROM (SELECT * FROM BOOK_SALES 
WHERE SALES_DATE >= '2022-01-01' AND SALES_DATE < '2022-02-01') S
JOIN BOOK B ON B.BOOK_ID = S.BOOK_ID
GROUP BY CATEGORY
ORDER BY CATEGORY;

# 7. 조건에 맞는 사용자와 총 거래금액 조회하기
SELECT USER_ID, NICKNAME, SUM(PRICE) AS TOTAL_SALES
FROM (SELECT * FROM USED_GOODS_BOARD WHERE STATUS = 'DONE') S
JOIN USED_GOODS_USER U ON U.USER_ID = S.WRITER_ID
GROUP BY WRITER_ID
HAVING TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES;

# 8. 즐겨찾기가 가장 많은 식당 정보 출력하기
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (SELECT FOOD_TYPE, MAX(FAVORITES) 
                                 FROM REST_INFO 
                                 GROUP BY FOOD_TYPE)
ORDER BY FOOD_TYPE DESC;

# 9. 진료과별 총 예약 횟수 출력하기
SELECT MCDP_CD AS '진료과코드', COUNT(APNT_NO) AS '5월예약건수'
FROM (SELECT * 
     FROM APPOINTMENT 
     WHERE APNT_YMD >= '2022-05-01' AND APNT_YMD < '2022-06-01') A
GROUP BY MCDP_CD
ORDER BY COUNT(APNT_NO), MCDP_CD;

# 10. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
SELECT MONTH(START_DATE) AS 'MONTH', CAR_ID, COUNT(HISTORY_ID) AS 'RECORDS'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE>='2022-08-01' AND START_DATE<'2022-11-01'
        AND 
        CAR_ID IN (SELECT CAR_ID
                   FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                   WHERE START_DATE>='2022-08-01' AND START_DATE<'2022-11-01'
                   GROUP BY CAR_ID
                   HAVING COUNT(HISTORY_ID)>=5)
GROUP BY MONTH(START_DATE), CAR_ID
HAVING COUNT(HISTORY_ID)>=1
ORDER BY MONTH(START_DATE), CAR_ID DESC;

IS NULL

# 1. 경기도에 위치한 식품창고 목록 출력하기
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IFNULL(FREEZER_YN, 'N')
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기도%'
ORDER BY WAREHOUSE_ID;

# 2. 이름이 없는 동물의 아이디
SELECT ANIMAL_ID 
FROM ANIMAL_INS
WHERE NAME IS NULL;

# 3. 이름이 있는 동물의 아이디
SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL;

# 4. NULL 처리하기
SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name'), SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

# 5. 나이 정보가 없는 회원 수 구하기
SELECT COUNT(USER_ID) AS 'USERS'
FROM USER_INFO
WHERE AGE IS NULL;

JOIN

# 1. 조건에 맞는 도서와 저자 리스트 출력하기
SELECT BOOK_ID, AUTHOR_NAME, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') as PUBLISHED_DATE
FROM AUTHOR A
JOIN BOOK B ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE CATEGORY = '경제'
ORDER BY PUBLISHED_DATE;

# 2. 없어진 기록 찾기
SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_OUTS O
LEFT OUTER JOIN ANIMAL_INS I ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL;

# 3. 있었는데요 없었습니다
SELECT I.ANIMAL_ID, I.NAME
FROM ANIMAL_INS I 
JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.DATETIME > O.DATETIME
ORDER BY I.DATETIME;

# 4. 오랜 기간 보호한 동물(1)
SELECT I.NAME, I.DATETIME
FROM ANIMAL_INS I
LEFT OUTER JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.ANIMAL_ID IS NULL
ORDER BY DATETIME
LIMIT 3;

# 5. 보호소에서 중성화한 동물
SELECT O.ANIMAL_ID, O.ANIMAL_TYPE, O.NAME
FROM ANIMAL_INS I 
JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.SEX_UPON_INTAKE LIKE 'Intact%'
    AND O.SEX_UPON_OUTCOME NOT LIKE 'Intact%';
    
# 6. 5월 식품들의 총매출 조회하기
SELECT P.PRODUCT_ID, PRODUCT_NAME, SUM((AMOUNT*PRICE)) AS TOTAL_SALES
FROM (SELECT *
    FROM FOOD_ORDER
    WHERE PRODUCE_DATE >= '2022-05-01' AND PRODUCE_DATE < '2022-06-01') A
JOIN FOOD_PRODUCT P ON P.PRODUCT_ID = A.PRODUCT_ID
GROUP BY PRODUCT_ID
ORDER BY TOTAL_SALES DESC, PRODUCT_ID;

# 7. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기
SELECT A.CAR_ID, 
        A.CAR_TYPE, 
        ROUND(A.DAILY_FEE*30*((100-DISCOUNT_RATE)/100),0) AS FEE
FROM CAR_RENTAL_COMPANY_CAR A
JOIN (SELECT * 
      FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
      WHERE DURATION_TYPE = '30일 이상') B 
      ON A.CAR_TYPE = B.CAR_TYPE
WHERE A.CAR_TYPE IN ('세단', 'SUV')
AND A.CAR_ID NOT IN (SELECT CAR_ID
                    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                    WHERE END_DATE>='2022-11-01' AND START_DATE<'2022-12-01')
GROUP BY A.CAR_ID
HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC;

String, Date

# 1. 자동차 대여 기록에서 장기/단기 대여 구분하기
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,
        IF(DATEDIFF(END_DATE, START_DATE)>=29, '장기 대여', '단기 대여') AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09%'
ORDER BY HISTORY_ID DESC;

# 2. 루시와 엘라 찾기
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty');

# 3. 이름에 el이 들어가는 동물 찾기
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_TYPE='Dog' AND NAME LIKE '%EL%'
ORDER BY NAME;

# 4. 중성화 여부 파악하기
SELECT ANIMAL_ID, 
        NAME,
        IF(SEX_UPON_INTAKE LIKE 'Intact%', 'X', 'O') AS '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

# 5. 오랜 기간 보호한 동물(2)
SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_OUTS O
JOIN ANIMAL_INS I ON O.ANIMAL_ID = I.ANIMAL_ID
ORDER BY DATEDIFF(O.DATETIME, I.DATETIME) DESC
LIMIT 2;