Programmers_SQL_LV4_MySQL

https://school.programmers.co.kr/learn/challenges?order=acceptance_desc&levels=4&languages=mysq

코딩테스트 연습 | 프로그래머스 스쿨

• 보호소에서 중성화한 동물

-- 코드를 입력하세요
SELECT a.ANIMAL_ID, a.ANIMAL_TYPE, a.NAME 
from ANIMAL_INS as a 
left join ANIMAL_OUTS as b 
on a.ANIMAL_ID=b.ANIMAL_ID 
where a.SEX_UPON_INTAKE like 'Intact%' 
	and (b.SEX_UPON_OUTCOME like 'Spayed%' 
    or b.SEX_UPON_OUTCOME like 'Neutered%') 
order by a.ANIMAL_ID;

• 5월 식품들의 총매출 조회하기

-- 코드를 입력하세요
SELECT 
    fp.PRODUCT_ID,
    PRODUCT_NAME,
    SUM(PRICE * AMOUNT) TOTAL_SALES
FROM FOOD_PRODUCT as fp
JOIN FOOD_ORDER as fo
ON fp.PRODUCT_ID = fo.PRODUCT_ID
WHERE fo.PRODUCE_DATE like "2022-05%"
GROUP BY PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, PRODUCT_ID;

• 식품분류별 가장 비싼 식품의 정보 조회하기

-- 코드를 입력하세요
SELECT 
    f.CATEGORY,
    f.PRICE as MAX_PRICE,
    f.PRODUCT_NAME
FROM FOOD_PRODUCT as f
LEFT JOIN (
    SELECT PRODUCT_ID, CATEGORY, MAX(PRICE) as maxPrice, PRODUCT_NAME
    FROM FOOD_PRODUCT
    GROUP BY CATEGORY
) as f2
ON f.CATEGORY = f2.CATEGORY
WHERE f.PRICE = f2.maxPrice AND f.CATEGORY in ("과자", "국", "김치", "식용유")
ORDER BY f.PRICE DESC;

• 우유와 요거트가 담긴 장바구니

-- 코드를 입력하세요
SELECT DISTINCT c.CART_ID
FROM (
    SELECT ID, CART_ID, NAME, PRICE
    FROM CART_PRODUCTS 
    WHERE NAME = "Yogurt"
)as c
LEFT JOIN (
    SELECT ID, CART_ID, NAME, PRICE
    FROM CART_PRODUCTS 
    WHERE NAME = "Milk"
) as c2
ON c.CART_ID = c2.CART_ID
where c.NAME = "Yogurt" AND c2.NAME = "Milk";

• 년, 월, 성별 별 상품 구매 회원 수 구하기

-- 코드를 입력하세요
SELECT
    LEFT(o.SALES_DATE, 4) AS YEAR,
    SUBSTRING(o.SALES_DATE, 6,2)%13 AS MONTH,
    u.GENDER,
    COUNT(DISTINCT u.USER_ID) as USERS
FROM ONLINE_SALE as o
LEFT JOIN USER_INFO as u
ON u.USER_ID = o.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR ASC, MONTH ASC, GENDER ASC;

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

-- 코드를 입력하세요
SELECT ri.REST_ID,
    ri.REST_NAME,
    FOOD_TYPE,
    ri.FAVORITES as FAVORITES,
    ADDRESS,
    ROUND(AVG(REVIEW_SCORE),2) as SCORE
FROM REST_REVIEW as rr 
LEFT JOIN REST_INFO as ri
ON ri.REST_ID = rr.REST_ID
WHERE ADDRESS like "서울%"
GROUP BY ri.REST_ID
ORDER BY SCORE DESC, FAVORITES DESC;

• 취소되지 않은 진료 예약 조회하기

-- 코드를 입력하세요
SELECT f1.APNT_NO,
    f1.PT_NAME,
    f1.PT_NO,
    f1.MCDP_CD,
    d.DR_NAME,
    f1.APNT_YMD
FROM (
    SELECT a.APNT_YMD, 
        a.APNT_NO, 
        a.PT_NO, 
        a.MCDP_CD,
        a.MDDR_ID,         
        p.PT_NAME
    FROM APPOINTMENT as a
    LEFT JOIN PATIENT as p
    ON a.PT_NO = p.PT_NO
    WHERE a.APNT_YMD like "2022-04-13%" AND a.MCDP_CD = "CS" AND a.APNT_CNCL_YN = "N"
) as f1
LEFT JOIN DOCTOR as d
ON f1.MDDR_ID = d.DR_ID
ORDER BY f1.APNT_YMD;

• 주문량이 많은 아이스크림들 조회하기

-- 코드를 입력하세요
SELECT f.FLAVOR
FROM FIRST_HALF as f
LEFT JOIN (
        SELECT FLAVOR, SUM(TOTAL_ORDER) as julysum
        FROM JULY  
        GROUP BY FLAVOR
    )
    as j
ON f.FLAVOR = j.FLAVOR
GROUP BY f.FLAVOR
ORDER BY TOTAL_ORDER+julysum DESC
limit 3;

• 저자 별 카테고리 별 매출액 집계하기

-- 코드를 입력하세요
SELECT  a.AUTHOR_ID,
    AUTHOR_NAME,
    CATEGORY,
    SUM(SALES * price) as TOTAL_SALES
FROM BOOK_SALES as bs
LEFT JOIN BOOK as b
ON bs.BOOK_ID = b.BOOK_ID
LEFT JOIN AUTHOR as a
ON a.AUTHOR_ID = b.AUTHOR_ID
WHERE sales_date like "2022-01%"
GROUP BY CATEGORY, a.AUTHOR_ID
ORDER BY a.AUTHOR_ID ASC, CATEGORY DESC;

• 입양 시각 구하기(2)

-- 코드를 입력하세요
WITH RECURSIVE recur AS (
    SELECT 0 AS h
    UNION ALL
    SELECT h+1 FROM recur WHERE h<23
)

SELECT h as HOUR, IFNULL(COUNT, 0) AS COUNT
FROM (
    SELECT HOUR(DATETIME) AS HOUR, count(ANIMAL_ID) AS COUNT
    FROM ANIMAL_OUTS 
    GROUP BY HOUR  
) as f
RIGHT JOIN recur as r
ON f.HOUR = r.h
ORDER BY r.h;

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

-- 코드를 입력하세요
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 ASC, PRODUCT_ID ASC, USER_ID ASC;

• 그룹별 조건에 맞는 식당 목록 출력하기

-- 코드를 입력하세요
SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, "%Y-%m-%d") as REVIEW_DATE
FROM (
    SELECT MEMBER_ID
    FROM REST_REVIEW 
    GROUP BY MEMBER_ID
    ORDER BY COUNT(*) DESC
    LIMIT 1
) as f1
JOIN REST_REVIEW as f2
ON f1.MEMBER_ID = f2.MEMBER_ID
JOIN MEMBER_PROFILE as f3
ON f1.MEMBER_ID = f3.MEMBER_ID
ORDER BY REVIEW_DATE ASC, REVIEW_TEXT ASC;

• 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

-- 코드를 입력하세요
SELECT CAR_ID, f1.CAR_TYPE, ROUND(DAILY_FEE * 30 * ((100-DISCOUNT_RATE)/100), 0) as FEE
FROM (
        SELECT CAR_ID, CAR_TYPE, DAILY_FEE
        FROM CAR_RENTAL_COMPANY_CAR 
        WHERE CAR_TYPE REGEXP '세단|SUV'
            AND CAR_ID NOT IN(
                SELECT car_id
                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
                WHERE date_format(START_DATE, '%Y-%m-%d') <= '2022-11-30' 
                    AND date_format(END_DATE, '%Y-%m-%d') >= '2022-11-01'
                GROUP BY car_id
            )
    ) as f1
LEFT JOIN (
        SELECT CAR_TYPE, DISCOUNT_RATE
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN    
        WHERE duration_type = '30일 이상'  
    )as f2
ON f1.CAR_TYPE = f2.CAR_TYPE
WHERE DAILY_FEE * 30 * ((100-DISCOUNT_RATE)/100) BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, f1.CAR_TYPE ASC, CAR_ID DESC;

• 자동차 대여 기록 별 대여 금액 구하기

-- 코드를 입력하세요
SELECT f1.HISTORY_ID, 
    IF(f2.DISCOUNT_RATE IS NULL,
        day * CAR.DAILY_FEE,
        ROUND((CAR.DAILY_FEE * ((100-f2.DISCOUNT_RATE) / 100))) * f1.day) as FEE
FROM CAR_RENTAL_COMPANY_CAR as CAR, (
    SELECT history_id, 
        car_id, 
        TIMESTAMPDIFF(DAY, START_DATE, END_DATE)+ 1 as day,    
        CASE
            WHEN TIMESTAMPDIFF(DAY, START_DATE, END_DATE) + 1 >= 90 THEN "90일 이상"
            WHEN TIMESTAMPDIFF(DAY, START_DATE, END_DATE) + 1 >= 30 THEN "30일 이상"
            WHEN TIMESTAMPDIFF(DAY, START_DATE, END_DATE) + 1 >= 7 THEN "7일 이상"
        end as DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
) as f1
LEFT JOIN(
    SELECT DURATION_TYPE, DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE CAR_TYPE = '트럭'
) as f2 
ON f1.DURATION_TYPE = f2.DURATION_TYPE
WHERE CAR.CAR_ID = f1.CAR_ID and CAR.CAR_TYPE = '트럭'
ORDER BY FEE DESC, f1.HISTORY_ID DESC;