[SQL] 프로그래머스 문제 (31번~38번)

 

31번

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

프로그래머스

 

 

32번

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

프로그래머스

 

with cte_book as (
    select book_id,
           category
    from BOOK 
)

select category,
       sum(sales) as total_sales
from BOOK_SALES
inner join cte_book
on BOOK_SALES.book_id = cte_book.book_id
where year(sales_date) = 2022 and month(sales_date) = 1
group by category
order by category

 

 

33번✔️✔️

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

프로그래머스

 

select product_code,
       sum(sales_amount) * price as sales
from OFFLINE_SALE s
inner join product p
on s.product_id = p.product_id
group by product_code
order by sales desc, product_code

 

 

group by product_code 사용후

select 문에서 sum(sales_amount) * price

price만 사용해도 해당 product_code에 맞는 price가 출력된다

 

 

 

34번

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

프로그래머스

 

select a_in.animal_id,
       a_in.name
from ANIMAL_INS a_in
inner join ANIMAL_OUTS a_out
on a_in.animal_id = a_out.animal_id
and a_in.datetime > a_out.datetime
order by a_in.datetime

 

 

 

35번

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

프로그래머스

 

select i.animal_id,
       i.name
from ANIMAL_INS i
inner join ANIMAL_OUTS o
on i.animal_id = o.animal_id
order by (o.datetime - i.datetime) desc
limit 2

 

 

 

36번

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

프로그래머스

 

select i.animal_id,
       i.animal_type,
       i.name
from ANIMAL_INS i
inner join ANIMAL_OUTS o
on i.animal_id = o.animal_id
where i.SEX_UPON_INTAKE like '%Intact%' 
and (o.SEX_UPON_OUTCOME like '%Spayed%' or o.SEX_UPON_OUTCOME like '%Neutered%')
order by i.animal_id

 

 

 

37번

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

프로그래머스

 

select b.book_id,
       a.author_name,
       date_format(b.published_date, '%Y-%m-%d') as published_date
from BOOK b
inner join AUTHOR a
on b.author_id = a.author_id
where b.category = '경제'
order by date_format(b.published_date, '%Y-%m-%d')

 

 

 

38번

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

프로그래머스

 

SELECT order_id,
       product_id,
       date_format(out_date, '%Y-%m-%d') as out_date,
       case when date_format(out_date, '%Y-%m-%d') <= '2022-05-01' then '출고완료'
            when date_format(out_date, '%Y-%m-%d') > '2022-05-01' then '출고대기'
            else '출고미정' end as '출고여부'
from FOOD_ORDER