프로그래머스 SQL 고득점 Kit JOIN 12문항에 대한 풀이입니다. MySQL 문법 기준으로 작성했습니다! 🔥
💡 주문량이 많은 아이스크림들 조회하기
https://school.programmers.co.kr/learn/courses/30/lessons/133027
SELECT F.FLAVOR
FROM FIRST_HALF F
LEFT JOIN (
SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER_JULY
FROM JULY
GROUP BY FLAVOR
) J ON F.FLAVOR = J.FLAVOR
ORDER BY (F.TOTAL_ORDER + COALESCE(J.TOTAL_ORDER_JULY, 0)) DESC
LIMIT 3;
💡 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기
https://school.programmers.co.kr/learn/courses/30/lessons/157339
SELECT
C.CAR_ID,
C.CAR_TYPE,
FLOOR(C.DAILY_FEE * (100 - COALESCE(P.DISCOUNT_RATE, 0)) * 0.01 * 30) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON C.CAR_TYPE = P.CAR_TYPE AND P.DURATION_TYPE = '30일 이상'
WHERE C.CAR_ID NOT IN (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE END_DATE >= '2022-11-01' AND START_DATE <= '2022-11-30'
)
AND C.CAR_TYPE IN ('세단', 'SUV')
AND FLOOR(C.DAILY_FEE * (100 - COALESCE(P.DISCOUNT_RATE, 0)) * 0.01 * 30) BETWEEN 500000 AND 1999999
ORDER BY FEE DESC, C.CAR_TYPE, C.CAR_ID DESC;
💡 5월 식품들의 총매출 조회하기
https://school.programmers.co.kr/learn/courses/30/lessons/131117
SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(O.AMOUNT) * P.PRICE TOTAL_SALES
FROM FOOD_ORDER O
LEFT JOIN FOOD_PRODUCT P
ON O.PRODUCT_ID = P.PRODUCT_ID
WHERE DATE_FORMAT(O.PRODUCE_DATE, '%Y-%m') = '2022-05'
GROUP BY O.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID ASC;
💡 조건에 맞는 도서와 저자 리스트 출력하기
https://school.programmers.co.kr/learn/courses/30/lessons/144854
SELECT
B.BOOK_ID AS BOOK_ID,
A.AUTHOR_NAME AS AUTHOR_NAME,
DATE_FORMAT(B.PUBLISHED_DATE, '20%y-%m-%d') AS PUBLISHED_DATE
FROM
BOOK B
JOIN AUTHOR A
ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE
B.CATEGORY = '경제'
ORDER BY
B.PUBLISHED_DATE;
💡 그룹별 조건에 맞는 식당 목록 출력하기
https://school.programmers.co.kr/learn/courses/30/lessons/131124
SELECT M.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(R.REVIEW_DATE, '%Y-%m-%d') REVIEW_DATE
FROM REST_REVIEW R
LEFT JOIN MEMBER_PROFILE M
ON R.MEMBER_ID = M.MEMBER_ID
WHERE R.MEMBER_ID = (
SELECT MEMBER_ID
FROM REST_REVIEW
GROUP BY MEMBER_ID
ORDER BY COUNT(MEMBER_ID) DESC
LIMIT 1
)
ORDER BY R.REVIEW_DATE, R.REVIEW_TEXT;
💡 없어진 기록 찾기
https://school.programmers.co.kr/learn/courses/30/lessons/59042
SELECT
AOUT.ANIMAL_ID AS ANIMAL_ID,
AOUT.NAME AS NAME
FROM
ANIMAL_INS AIN
RIGHT JOIN ANIMAL_OUTS AOUT
ON AIN.ANIMAL_ID = AOUT.ANIMAL_ID
WHERE
AIN.ANIMAL_ID IS NULL;
💡 있었는데요 없었습니다
https://school.programmers.co.kr/learn/courses/30/lessons/59043
SELECT
AIN.ANIMAL_ID AS ANIMAL_ID,
AIN.NAME AS NAME
FROM
ANIMAL_INS AIN
JOIN ANIMAL_OUTS AOUT
ON AIN.ANIMAL_ID = AOUT.ANIMAL_ID
WHERE
AIN.DATETIME > AOUT.DATETIME
ORDER BY
AIN.DATETIME;
💡 오랜 기간 보호한 동물(1)
https://school.programmers.co.kr/learn/courses/30/lessons/59044
SELECT
AIN.NAME AS NAME,
AIN.DATETIME AS DATETIME
FROM
ANIMAL_INS AIN
LEFT JOIN ANIMAL_OUTS AOUT
ON AIN.ANIMAL_ID = AOUT.ANIMAL_ID
WHERE
AOUT.ANIMAL_ID IS NULL
ORDER BY
AIN.DATETIME
LIMIT 3;
💡 보호소에서 중성화한 동물
https://school.programmers.co.kr/learn/courses/30/lessons/59045
SELECT
AIN.ANIMAL_ID AS ANIMAL_ID,
AIN.ANIMAL_TYPE AS ANIMAL_TYPE,
AIN.NAME AS NAME
FROM
ANIMAL_INS AIN
JOIN ANIMAL_OUTS AOUT
ON AIN.ANIMAL_ID = AOUT.ANIMAL_ID
WHERE
(AIN.SEX_UPON_INTAKE LIKE 'Intact%' AND AOUT.SEX_UPON_OUTCOME LIKE 'Spayed%')
OR
(AIN.SEX_UPON_INTAKE LIKE 'Intact%' AND AOUT.SEX_UPON_OUTCOME LIKE 'Neutered%')
ORDER BY
AIN.ANIMAL_ID;
💡 상품 별 오프라인 매출 구하기
https://school.programmers.co.kr/learn/courses/30/lessons/131533
SELECT
P.PRODUCT_CODE AS PRODUCT_CODE,
SUM(P.PRICE * O.SALES_AMOUNT) AS SALES
FROM
PRODUCT P
JOIN OFFLINE_SALE O
ON P.PRODUCT_ID = O.PRODUCT_ID
GROUP BY
P.PRODUCT_ID, P.PRODUCT_CODE
ORDER BY
SALES DESC,
P.PRODUCT_CODE;
💡 상품을 구매한 회원 비율 구하기
https://school.programmers.co.kr/learn/courses/30/lessons/131534
SELECT
YEAR(OS.SALES_DATE) AS YEAR,
MONTH(OS.SALES_DATE) AS MONTH,
COUNT(DISTINCT OS.USER_ID) AS PURCHASED_USERS,
ROUND(COUNT(DISTINCT OS.USER_ID) / (
SELECT COUNT(*) FROM USER_INFO UI WHERE YEAR(UI.JOINED) = '2021'
), 1) AS PURCHASED_RATIO
FROM
USER_INFO UI
RIGHT JOIN ONLINE_SALE OS
ON UI.USER_ID = OS.USER_ID
WHERE
YEAR(UI.JOINED) = '2021'
GROUP BY
YEAR, MONTH
ORDER BY
YEAR, MONTH;
💡 FrontEnd 개발자 찾기
https://school.programmers.co.kr/learn/courses/30/lessons/276035
SELECT D.ID, D.EMAIL, D.FIRST_NAME, D.LAST_NAME
FROM DEVELOPERS D
WHERE EXISTS (
SELECT 1
FROM SKILLCODES S
WHERE S.CATEGORY = 'Front End'
AND (D.SKILL_CODE & S.CODE) = S.CODE
)
ORDER BY D.ID;
'🧑🏻💻 Develop > SQL' 카테고리의 다른 글
[SQL 고득점 Kit] IS NULL 풀이 (0) | 2024.01.30 |
---|---|
[SQL 고득점 Kit] SUM, MAX, MIN 풀이 (0) | 2024.01.30 |
[SQL 고득점 Kit] SELECT 풀이 (0) | 2023.12.26 |