프로그래머스 SQL Lv3 문제

1. IFNULL(컬럼명, null인경우 대체할 값)

2. case-when

case
	when 조건식1 then 결과1
	when 조건식2 then 결과2
	else 조건식1,2, ... 가 모두 아니면 결과
end

3. IF

if(컬럼명 is null, '널이다', '널이 아니다')
if(1<2, 'yes', 'no')

4. NULLIF

nullif(expr1, expr2)

=> expr1=expr2 값을 비교해서 true가 아니면 expr1을 return한다. 만약 true이면 null을 return한다.

---

1. 없어진 기록 찾기

https://school.programmers.co.kr/learn/courses/30/lessons/59042

select b.animal_id, b.name
from animal_outs b
left outer join
animal_ins a
on a.animal_id = b.animal_id
where a.animal_id is null
order by b.animal_id, b.name;

두 개의 테이블을 join할 때 오른쪽이 null이 될 수 있도록 만들기 위해서는 left outer join을 사용한다.

2. 있었는데요 없었습니다

https://school.programmers.co.kr/learn/courses/30/lessons/59043

select b.animal_id, b.name
from animal_ins a
join
animal_outs b
on a.animal_id = b.animal_id
where a.datetime > b.datetime
order by a.datetime;

날짜를 비교할 때는 부등호로 비교함

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

https://school.programmers.co.kr/learn/courses/30/lessons/59044

select a.name, a.datetime
from animal_ins a
left outer join
animal_outs b
on a.animal_id = b.animal_id
where b.animal_id is null
order by a.datetime asc limit 3;

limit을 사용할 수 있는가

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

https://school.programmers.co.kr/learn/courses/30/lessons/59411

select b.animal_id, b.name
from animal_ins a
join animal_outs b
on a.animal_id = b.animal_id
order by b.datetime - a.datetime desc limit 2;

order by절에는 컬럼만 쓸 수 있는건 아니다.

5. 헤비 유저가 소유한 장소

https://school.programmers.co.kr/learn/courses/30/lessons/77487

SELECT *
from places
where host_id in (select host_id
                    from places
                    group by host_id
                    having count(host_id)>=2) 
order by id;

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

https://school.programmers.co.kr/learn/courses/30/lessons/131113

select order_id, product_id, date_format(out_date, '%Y-%m-%d'), 
case
    when month(OUT_DATE) is null then '출고미정'
    when month(OUT_DATE)<=4 or (month(OUT_DATE)=5 and day(OUT_DATE)=1) then '출고완료'
    else '출고대기'
end as '출고여부'
from food_order
order by order_id;

date_format(날짜, '%Y-%m-%d') => 2023-11-17 형태로 나타냄

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

https://school.programmers.co.kr/learn/courses/30/lessons/131123

# select food_type, max(favorites)
# from rest_info
# group by food_type;

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;

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

https://school.programmers.co.kr/learn/courses/30/lessons/144855

select category, sum(sales) as `TOTAL_SALES`
from book, book_sales
where book.book_id = book_sales.book_id
and year(sales_date) = 2022 and month(sales_date) = 1
group by category
order by category;

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

https://school.programmers.co.kr/learn/courses/30/lessons/151139

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
        SELECT CAR_ID
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE (DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10')
        GROUP BY CAR_ID
        HAVING COUNT(CAR_ID) >= 5
    ) AND (DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10')
GROUP BY MONTH, CAR_ID
HAVING RECORDS > 0
ORDER BY MONTH ASC, CAR_ID DESC;