Programmers 정리

1. 서울에 위치한 식당 목록 출력하기

SELECT rr.rest_id, rest_name, food_type, favorites, ri.address, ROUND(avg(rr.review_score),2) score
from rest_info ri
join rest_review rr on rr.rest_id = ri.rest_id
where ri.address like '서울%'
group by 1,2,3,4,5
order by 6 desc, 4 desc

• left join하면 안됨 → Null값이 포함되므로

1. 오프라인/온라인 판매 데이터 통합하기

SELECT sales_date, product_id, sales_amount
FROM ONLINE_SALE
UNION
SELECT sales_date, product_id, sales_amount
FROM OFFLINE_SALE

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

SELECT 
 A.BOOK_ID,
 B.AUTHOR_NAME,
 DATE_FORMAT(A.PUBLISHED_DATE,'%Y-%m-%d') PUBLISHED_DATE
FROM BOOK A
JOIN AUTHOR B
using (author_id)
WHERE A.CATEGORY = '경제'
ORDER BY A.PUBLISHED_DATE ASC

1. 상품 별 오프라인 매출 구하기

SELECT A.PRODUCT_CODE, sum(a.price * B.SALES_AMOUNT) SALES
FROM PRODUCT A
JOIN OFFLINE_SALE B
ON a.PRODUCT_ID = b.product_id
group by 1
ORDER BY 2 desc, 1 asc

1. 없어진 기록 찾기

SELECT b.animal_id, b.name
FROM ANIMAL_INS A
right JOIN ANIMAL_OUTS B
using (animal_id)
where a.sex_upon_intake is null
order by 1

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

SELECT a.animal_id, a.name
from animal_ins a
join animal_outs b
using (animal_id)
where timediff(a.datetime,b.datetime) > 0
order by a.datetime

1. 5월 식품들의 총 매출 조회하기

SELECT a.product_id, a.product_name, sum(a.price * b.amount)
from food_product a
join food_order b
using (product_id)
where b.produce_date like '2022-05%'
group by 1,2
order by 3 desc, 1 asc

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

SELECT distinct a.car_id
    from car_rental_company_car a
        join car_rental_company_rental_history b
        using (car_id)
        where b.start_date like '%-10-%' and a.car_type = '세단'
order by 1 desc

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

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 1)
order by food_type Desc;

1. 저자 별 카테고리별 매출액 집계하기

SELECT b.author_id, b.author_name, a.category, sum(c.sales * a.price)
FROM BOOK a
    join AUTHOR b using (author_id)
    join book_sales c on a.book_id = c.book_id
where c.sales_date like '2022-01%'
group by 1,2, 3
order by 1 asc, 3 desc

1. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

select CONCAT('/home/grep/src/', c.BOARD_ID ,'/', c.file_id, c.file_name, c.file_ext) as FILE_PATH
from 
    (SELECT *, RANK() OVER (ORDER BY a.views desc) as ranking
        from used_goods_board a
        join used_goods_file b using (board_id)) c
where ranking = 1
order by 1 desc

1. 식품분류별 가장 비싼 식품의 정보 조회하기

select category, max(price), product_name
from food_product
where (category,price) IN (select category, max(price) from food_product group by 1)
and category IN ('과자','국','김치','식용유')
group by 1, 3
order by 2 desc

1. 년, 월, 성별 별 상품 구매 회원수 구하기

select category, max(price), product_name
from food_product
where (category,price) IN (select category, max(price) from food_product group by 1) 
and category IN ('과자','국','김치','식용유')
group by 1, 3
order by 2 desc

1. 입양 시각 구하기(2)

WITH RECURSIVE cte AS (
SELECT 0 AS num
UNION ALL
SELECT num+1
FROM cte
WHERE num < 23
)
SELECT cte.num, IFNULL(a.입양횟수, 0)
FROM cte
LEFT JOIN (SELECT HOUR(datetime) 시간대, COUNT(*) 입양횟수
FROM animal_outs
GROUP BY 시간대
ORDER BY 시간대) a
ON cte.num = a.시간대;