Programmers_SQL_LV3_MySQL

https://school.programmers.co.kr/learn/challenges?order=acceptance_desc&page=1&languages=mysql&levels=3

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

• 오랜 기간 보호한 동물(1)

-- 코드를 입력하세요
SELECT a.NAME, a.DATETIME 
from ANIMAL_INS as a 
left join ANIMAL_OUTS as b 
on a.ANIMAL_ID = b.ANIMAL_ID 
where b.ANIMAL_ID is null 
order by a.DATETIME 
limit 3;

• 오랜 기간 보호한 동물(2)

-- 코드를 입력하세요
SELECT a.ANIMAL_ID, a.NAME 
from ANIMAL_INS as a 
left join ANIMAL_OUTS as b 
on a.ANIMAL_ID=b.ANIMAL_ID 
order by (b.DATETIME-a.DATETIME)
DESC limit 2;

• 있었는데요 없었습니다

-- 코드를 입력하세요
SELECT a.ANIMAL_ID, a.NAME 
from ANIMAL_INS as a 
left join ANIMAL_OUTS  as b 
on a.ANIMAL_ID=b.ANIMAL_ID 
where a.DATETIME	> b.DATETIME 
order by a.DATETIME;

• 조건별로 분류하여 주문상태 출력하기

-- 코드를 입력하세요
SELECT ORDER_ID,
    PRODUCT_ID,
    DATE_FORMAT(OUT_DATE, "%Y-%m-%d") AS OUT_DATE,    
    IF(OUT_DATE IS NULL, "출고미정",
        IF(DATEDIFF("2022-05-01", OUT_DATE) >= 0, "출고완료", "출고대기")
    ) AS 출고여부
FROM FOOD_ORDER 
ORDER BY ORDER_ID ASC;

• 없어진 기록 찾기

-- 코드를 입력하세요
SELECT b.ANIMAL_ID, b.NAME 
from ANIMAL_OUTS AS b 
left join ANIMAL_INS AS a 
on b.ANIMAL_ID=a.ANIMAL_ID 
where a.ANIMAL_ID is null 
order by b.ANIMAL_ID;

• 카테고리 별 도서 판매량 집계하기

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

• 즐겨찾기가 가장 많은 식당 정보 출력하기

-- 코드를 입력하세요
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO 
WHERE FAVORITES IN (
    SELECT MAX(FAVORITES)
    FROM REST_INFO
    GROUP BY FOOD_TYPE
)
GROUP BY FOOD_TYPE
ORDER BY FOOD_TYPE DESC;

-- 코드를 입력하세요
SELECT r.FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO as r
JOIN (
    SELECT FOOD_TYPE, MAX(FAVORITES) AS MAXFAVORITES
    FROM REST_INFO
    GROUP BY FOOD_TYPE
) as t
ON r.FOOD_TYPE = t.FOOD_TYPE
WHERE r.FAVORITES = MAXFAVORITES
ORDER BY FOOD_TYPE DESC;

• 헤비 유저가 소유한 장소

-- 코드를 입력하세요
SELECT ID, NAME, p.HOST_ID
FROM PLACES as p
JOIN (
    SELECT HOST_ID, COUNT(HOST_ID) as cnt
    FROM PLACES
    GROUP BY HOST_ID
) as t
ON p.HOST_ID = t.HOST_ID
WHERE cnt>=2
ORDER BY ID;

• 대여 기록이 존재하는 자동차 리스트 구하기

-- 코드를 입력하세요
SELECT DISTINCT ccrh.CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY as ccrh
LEFT JOIN CAR_RENTAL_COMPANY_CAR as crcc
ON ccrh.CAR_ID = crcc.CAR_ID
WHERE car_type="세단" AND START_DATE like "%-10-%"
ORDER BY ccrh.CAR_ID DESC;

• 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

-- 코드를 입력하세요
SELECT CAR_ID,
    IF( max(END_DATE) >= "2022-10-16", "대여중", "대여 가능") as AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE <= "2022-10-16"
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

• 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

-- 코드를 입력하세요
SELECT substring(START_DATE, 6, 2)%13 AS MONTH,
    CAR_ID,
    COUNT(*) as RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
where CAR_ID IN(
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
    WHERE substring(START_DATE, 6, 2)%13 between 8 AND 10
    GROUP BY CAR_ID
    HAVING COUNT(*) >=5
    ) 
    AND
    substring(START_DATE, 6, 2)%13 between 8 AND 10
GROUP BY MONTH, CAR_ID
HAVING COUNT(*) > 0
ORDER BY MONTH, CAR_ID DESC;

-- 코드를 입력하세요
SELECT substring(START_DATE, 6, 2)%13 as MONTH,
    c1.CAR_ID,
    COUNT(*) as RECORDS    
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY as c1
LEFT JOIN (
    SELECT CAR_ID, COUNT(*) as cnt
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE substring(START_DATE, 6, 2)%13 between 8 AND 10
    GROUP BY CAR_ID
) as c2
ON c1.CAR_ID = c2.CAR_ID
WHERE cnt >= 5 AND substring(START_DATE, 6, 2)%13 between 8 AND 10
GROUP BY c1.CAR_ID, MONTH
HAVING COUNT(*) > 0
ORDER BY MONTH, CAR_ID DESC;