[프로그래머스/SQL 고득점 Kit/MySQL] JOIN(12문제)

https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit 

프로그래머스

---

<JOIN>

Q. 상품 별 오프라인 매출 구하기 | Level 2

select p.product_code, (sum(os.sales_amount) * p.price) as sales
from offline_sale os 
left join product p on os.product_id = p.product_id
group by os.product_id
order by sales desc, p.product_code asc

 

Q. 조건에 맞는 도서와 저자 리스트 출력하기 | Level 2

select b.book_id , a.author_name, DATE_FORMAT(b.published_date, "%Y-%m-%d") as published_date
from author a 
inner join book b on a.author_id = b.author_id and b.category = '경제'
order by b.published_date asc
-- DATE_FORMAT !!

 

Q. 없어진 기록 찾기 | Level 3

/* 오답!
select ao.animal_id, ao.name
from animal_outs ao 
inner join animal_ins ai on ao.animal_id != ai.animal_id
order by ao.animal_id;
*/

select ao.animal_id, ao.name
from animal_outs ao 
left join animal_ins ai on ao.animal_id = ai.animal_id
where ai.animal_id is null
order by ao.animal_id;
-- !! a left join b on a.key = b.key where b.key is null 조건 !

 

Q. 있었는데요 없었습니다 | Level 3

select ai.animal_id, ai.name
from animal_ins ai
inner join animal_outs ao on ai.animal_id = ao.animal_id
where ao.datetime < ai.datetime
order by ai.datetime

 

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

select ai.name, ai.datetime
from animal_ins ai 
left join animal_outs ao on ai.animal_id = ao.animal_id
where ao.animal_id is null
order by ai.datetime asc 
limit 3

 

Q. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 | Level 4 >> 하는 즁 ! 

/* 오답
select 
    A.car_id
    , A.car_type
    , round(daily_fee*30*(1-discount_rate/100),0) as fee
from (
    select *
    from car_rental_company_car crcc 
    where crcc.car_type in ('세단', 'SUV')
    and crcc.car_id not in (
        select crcrh.car_id -- 대여 불가 (기간 내 대여중)
        from car_rental_company_rental_history crcrh
        where crcrh.start_date between '2022-11-01' and '2022-12-01'
        or crcrh.end_date between '2022-11-01' and '2022-12-01'
        )
    ) A
left join (
    select * 
    from car_rental_company_discount_plan crcdp
    where crcdp.duration_type like "30%"
    order by discount_rate desc 
    ) B
on A.car_type = B.car_type
-- fee 조건 ... 
order by fee desc, car_type asc, car_id desc

*/
-- 대여 start_date, end_date 조건 주의 !
-- CAST(REPLACE(DISCOUNT_RATE, '%', '') AS DECIMAL) 안해도 되네 ? 

-- (1-DISCOUNT_RATE/100) 계산식

 

 

Q. 5월 식품들의 총매출 조회하기 | Level 4

Q. 주문량이 많은 아이스크림들 조회하기 | Level 4

Q. 그룹별 조건에 맞는 식당 목록 출력하기 | Level 4

Q. 보호소에서 중성화한 동물 | Level 4

Q. FrontEnd 개발자 찾기 | Level 4

Q. 상품을 구매한 회원 비율 구하기 | Level 5