프로그래머스 SQL 고득점 KIT 풀이

재구매가 일어난 상품과 회원 리스트 구하기

SELECT user_id, product_id
FROM online_sale
group by user_id, product_id // (user_id, product_id)를 그룹으로 만들어줌
having count(user_id) > 1 //  (user_id, product_id) 2개 이상인 그룹을 필터링
order by user_id asc, product_id desc

과일로 만든 아이스크림 고르기

SELECT f.flavor
FROM FIRST_HALF f INNER JOIN ICECREAM_INFO i
ON f.flavor = i.flavor
WHERE f.total_order > 3000 and i.ingredient_type = "fruit_based"
ORDER BY f.total_order desc

FIRST_HALF의 기본 KEY는 flavor이고 ICECREAM_INFO 또한 flavor이다.

평균 일일 대여 요금 구하기

SELECT round(avg(daily_fee)) as AVERAGE_FEE
FROM car_rental_company_car
WHERE car_type = 'SUV' -- 자동으로 더해줌

사전시험

SELECT branch_id, SUM(salary) AS total
FROM employees
GROUP BY branch_id
ORDER BY branch_id

조건에 부합하는 중고거래 댓글 조회하기

SELECT bd.title, bd.board_id, reply_id, rep.writer_id, rep.contents, date_format(rep.created_date, "%Y-%m-%d") as CREATED_DATE
FROM used_goods_board bd JOIN used_goods_reply rep ON (bd.board_id = rep.board_id) // join하기
WHERE date_format(bd.CREATED_DATE, "%Y-%m") = "2022-10" 
ORDER BY rep.created_date ,bd.title

join 사용하기

SELECT flavor
FROM FIRST_HALF fh JOIN
(
    SELECT FLAVOR, SUM(TOTAL_ORDER) AS total_order
    FROM JULY
    GROUP BY FLAVOR
) J
USING(FLAVOR)
ORDER BY J.TOTAL_ORDER + fh.TOTAL_ORDER DESC
LIMIT 3

SELECT a.PRODUCT_ID, a.PRODUCT_NAME, a.PRICE * SUM(b.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT AS a INNER JOIN FOOD_ORDER as b
USING(PRODUCT_ID)
WHERE date_format(b.PRODUCE_DATE, "%Y-%m") = '2022-05' 
GROUP BY a.PRODUCT_ID //SELECT에서 SUM할때 필요
ORDER BY TOTAL_SALES DESC, a.PRODUCT_ID

SELECT a.BOOK_ID, b.AUTHOR_NAME, date_format(a.PUBLISHED_DATE, "%Y-%m-%d") AS PUBLISHED_DATE //그냥 하면 시간까지 나옴
FROM BOOK a INNER JOIN AUTHOR b
USING (AUTHOR_ID)
WHERE a.CATEGORY = "경제"
ORDER BY a.PUBLISHED_DATE

날짜 차이 구하기 DATEDIFF

SELECT HISTORY_ID,
       CAR_ID, 
       DATE_FORMAT(START_DATE,'%Y-%m-%d') AS START_DATE,
       DATE_FORMAT(END_DATE,'%Y-%m-%d') AS END_DATE,
       IF(DATEDIFF(END_DATE, START_DATE) + 1 >= 30, '장기 대여', '단기 대여') AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE DATE_FORMAT(START_DATE, '%Y-%m') = '2022-09'
ORDER BY HISTORY_ID DESC;

문자열 합치기 CONCAT

SELECT CONCAT("/home/grep/src/", b.BOARD_ID, "/", b.FILE_ID ,b.FILE_NAME,b.FILE_EXT) AS FILE_PATH
FROM USED_GOODS_BOARD a INNER JOIN USED_GOODS_FILE b USING(BOARD_ID)
WHERE a.board_id = b.board_id AND VIEWS = (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD)
ORDER BY FILE_ID DESC

WHERE NAME IN, DISTINCT, HAVING COUNT

SELECT CART_ID
FROM CART_PRODUCTS
WHERE NAME IN ("Milk", "Yogurt")
GROUP BY CART_ID
HAVING COUNT(DISTINCT NAME) = 2
ORDER BY CART_ID

WHERE NAME IN (SELECT

SELECT ID, NAME, HOST_ID
FROM PLACES
WHERE HOST_ID in ( 
SELECT HOST_ID
FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(HOST_ID) > 1
)
ORDER BY ID