[250819] 스파르타코딩 본캠프 11일차 (2)

💥  CODEKATA                                                                                                           

~45문제

 

오늘은 임팩트 있는 문제가 없음

just 기록용으로 기재

 

 

 

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

SELECT ai.animal_id, ai.name
from animal_ins ai left join animal_outs ao on ai.animal_id = ao.animal_id
order by (date(ao.datetime) - date(ai.datetime)) desc limit 2

 

 

 

36) 보호소에서 중성화한 동물

select a.animal_id, a.animal_type, a.name
from (
    SELECT ai.animal_id, ai.animal_type, ai.name, ai.sex_upon_intake, ao.sex_upon_outcome, 
           ai.sex_upon_intake != ao.sex_upon_outcome as new_sex_upon
    from animal_ins ai left join animal_outs ao on ai.animal_id = ao.animal_id
) a
where a.new_sex_upon = 1

 

 

 

37) 조건에 맞는 도서와 저자 리스트 출력하기

SELECT b.book_id, a.author_name, substr(b.published_date, 1, 10) as published_date
from book b left join author a on b.author_id = a.author_id
where category = '경제'
order by b.published_date

 

 

 

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

SELECT order_id, product_id, substr(out_date, 1, 10) as out_date, case 
    when out_date <= '2022-05-01' then "출고완료"
    when out_date > '2022-05-01' then "출고대기"
    else "출고미정" end "출고여부"
from food_order
order by order_id

 

 

 

39) 성분으로 구분한 아이스크림 총 주문량

SELECT ii.ingredient_type, sum(fh.total_order)
from first_half fh left join icecream_info ii on fh.flavor = ii.flavor
group by ii.ingredient_type

 

 

 

40) 루시와 엘라 찾기

SELECT animal_id, name, sex_upon_intake
from animal_ins
where name in ("Lucy", "Ella", "Pickle", "Rogan", "Sabrina", "Mitty")
order by animal_id

 

 

 

41) 조건에 맞는 도서 리스트 출력하기

SELECT book_id, substr(published_date, 1, 10) as published_date
from book
where category = "인문" and published_date like "2021%"
order by published_date

 

 

 

42) 평균 일일 대여 요금 구하기

SELECT round(avg(daily_fee),0) as average_fee
from car_rental_company_car
where car_type = "SUV"
group by car_type

 

 

 

43) 조건에 맞는 사용자와 총 거래금액 조회하기

SELECT ub.writer_id as user_id, uu.nickname, sum(ub.price) as total_sales
from used_goods_board ub left join used_goods_user uu on ub.writer_id = uu.user_id
where ub.status = 'DONE'
group by uu.nickname
having total_sales >= 700000
order by total_sales

 

 

 

44) 가격대 별 상품 개수 구하기

SELECT case
        when price between 0 and 9999 then '0'
        when price between 10000 and 19999 then '10000'
        when price between 20000 and 29999 then '20000'
        when price between 30000 and 39999 then '30000'
        when price between 40000 and 49999 then '40000'
        when price between 50000 and 59999 then '50000'
        when price between 60000 and 69999 then '60000'
        when price between 70000 and 79999 then '70000'
        when price between 80000 and 89999 then '80000'
        when price between 90000 and 99999 then '90000'
        end price_group
        , count(1) as products
from product
group by price_group
order by price_group

 

 

 

45) 3월에 태어난 여성 회원 목록 출력하기

SELECT member_id, member_name, gender, substr(date_of_birth, 1, 10) as date_of_birth
from member_profile
where gender = 'W' and date_of_birth like '%-03-%' and tlno is not null
order by member_id