[Programmers/Level 4] JOIN(MySQL)

Level 4

주문량이 많은 아이스크림들 조회하기

중복된 맛을 가진 아이스크림 주문 정보는 JULY 테이블에만 있으므로 JULY 테이블의 맛을 기준으로 FIRST_HALF 테이블의 데이터를 가져온다.

그리고 FLAVOR를 기준으로 그룹화한 후 총 주문량을 더한 다음 내림차순으로 정렬하고 LIMIT를 활용해 세 번째 데이터까지 출력한다.

SELECT B.FLAVOR
FROM JULY B INNER JOIN FIRST_HALF A ON A.FLAVOR = B.FLAVOR
GROUP BY B.FLAVOR
ORDER BY SUM(A.TOTAL_ORDER + B.TOTAL_ORDER) DESC LIMIT 3

 

특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

먼저 3개의 테이블을 Join한다. 그리고 CAR_TYPE가 '세단'이거나 'SUV'인 것들만 찾는다.

그리고 대여 시작일과 종료일이 전부 2022년 11월인 데이터들을 찾는다.

그리고 대여 기간 종류가 '30일 이상'이고, 30일간의 대여 금액이 500,000에서 2,000,000인 것들만 찾는다.

이 때 30일간의 대여 금액은 (DAILY_FEE * 30)인데, 여기에 (100 - DISCOUNT_RATE) / 100을 곱하는 것으로 할인율을 적용시킨다.

마지막으로 CAR_ID, CAR_TYPE, FEE를 속성으로 가지는 DB를 리스트로 출력해야하는데, FEE는 정수 부분만 출력해야 하므로 ROUND() 함수를 통하여 소수점 첫 번째 자리에서 반올림한다.

SELECT DISTINCT A.CAR_ID, A.CAR_TYPE,
ROUND(((A.DAILY_FEE * 30) * (100 - C.DISCOUNT_RATE) / 100), 0) AS FEE
FROM CAR_RENTAL_COMPANY_CAR A
LEFT OUTER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B
ON A.CAR_ID = B.CAR_ID
LEFT OUTER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C
ON A.CAR_TYPE = C.CAR_TYPE
WHERE A.CAR_TYPE IN ('세단', 'SUV')
AND A.CAR_ID NOT IN (
    SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '2022-11' BETWEEN DATE_FORMAT(START_DATE, '%Y-%m')
    				AND DATE_FORMAT(END_DATE, '%Y-%m')
)
AND (C.DURATION_TYPE LIKE '%30일 이상%'
	AND ((A.DAILY_FEE * 30) * (100 - C.DISCOUNT_RATE) / 100) BETWEEN 500000 AND 2000000
)
ORDER BY FEE DESC, A.CAR_TYPE ASC, A.CAR_ID DESC

 

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

상품의 ID를 기준으로 Join하고 PRODUCE_DATE의 년도가 2022고 월이 5인 데이터들 중에서 상품의 ID별로 그룹화한 후 가격과 양을 곱해 총매출을 구한다.

SELECT A.PRODUCT_ID, A.PRODUCT_NAME, SUM(A.PRICE * B.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT A INNER JOIN FOOD_ORDER B ON A.PRODUCT_ID = B.PRODUCT_ID
WHERE YEAR(B.PRODUCE_DATE) = 2022 AND MONTH(B.PRODUCE_DATE) = 5
GROUP BY A.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, A.PRODUCT_ID ASC

 

그룹별 조건에 맞는 식당 목록 출력하기

회원의 ID를 기준으로 Join을 하고, WHERE절에서 회원별로 그룹화한 후 리뷰의 개수를 세고 가장 큰 결과값을 가지는 회원을 찾는다.

SELECT A.MEMBER_NAME, B.REVIEW_TEXT,
DATE_FORMAT(B.REVIEW_DATE, '%Y-%m-%d') AS 'REVIEW_DATE'
FROM MEMBER_PROFILE A
JOIN REST_REVIEW B ON A.MEMBER_ID = B.MEMBER_ID
WHERE A.MEMBER_ID = (
    SELECT MEMBER_ID FROM REST_REVIEW
    GROUP BY MEMBER_ID
    ORDER BY COUNT(*) DESC LIMIT 1
)
ORDER BY B.REVIEW_DATE ASC, B.REVIEW_TEXT ASC

 

보호소에서 중성화한 동물

동물의 ID를 기준으로 Join을 하고, SEX_UPON_INTAKE에 'Intact'가 포함되고 SEX_UPON_OUTCOME에 'Neutered'나 'Spayed'가 포함되었다면 보호소에 들어왔을 때는 중성화가 안 됐지만 보호소를 나갈 때는 중성화가 진행된 동물이다.

SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME
FROM ANIMAL_INS A LEFT OUTER JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.SEX_UPON_INTAKE LIKE '%Intact%' AND
(B.SEX_UPON_OUTCOME LIKE '%Neutered%' OR B.SEX_UPON_OUTCOME LIKE '%Spayed%')
ORDER BY A.ANIMAL_ID ASC