프로그래머스 - SQL

특정 옵션이 포함된 자동차 리스트 구하기

코드

SELECT CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY CAR_ID DESC;

조건에 부합하는 중고거래 상태 조회하기

코드

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
CASE
WHEN STATUS = 'SALE' THEN '판매중'
WHEN STATUS = 'RESERVED' THEN '예약중'
WHEN STATUS = 'DONE' THEN '거래완료'
END AS STATUS
FROM USED_GOODS_BOARD
WHERE CREATED_DATE='2022-10-05'
ORDER BY BOARD_ID DESC;

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

코드

SELECT R.REST_ID, I.REST_NAME, I.FOOD_TYPE, I.FAVORITES, I.ADDRESS, ROUND(AVG(R.REVIEW_SCORE), 2) AS SCORE
FROM REST_INFO I RIGHT JOIN REST_REVIEW R ON I.REST_ID = R.REST_ID
GROUP BY R.REST_ID
HAVING I.ADDRESS LIKE '서울%'
ORDER BY SCORE DESC, I.FAVORITES DESC;

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

코드

SELECT YEAR(S.SALES_DATE) AS YEAR, MONTH(S.SALES_DATE) AS MONTH, I.GENDER, COUNT(DISTINCT S.USER_ID) AS USERS
FROM USER_INFO I RIGHT JOIN ONLINE_SALE S ON I.USER_ID = S.USER_ID
WHERE I.GENDER IS NOT NULL
GROUP BY YEAR, MONTH, I.GENDER
ORDER BY YEAR, MONTH, I.GENDER

자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

코드

SELECT CAR_ID,
(CASE WHEN CAR_ID IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '2022-10-16' BETWEEN START_DATE AND END_DATE)
THEN '대여중'
ELSE '대여 가능'
END) AS 'AVAILABILITY'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

오답코드

SELECT CAR_ID,
CASE WHEN(START_DATE<='2022-10-16' AND END_DATE>='2022-10-16') THEN '대여중' 
ELSE '대여가능' END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
ORDER BY CAR_ID DESC;

취소되지 않은 진료 예약 조회하기

코드

SELECT A.APNT_NO, P.PT_NAME, A.PT_NO, A.MCDP_CD, D.DR_NAME, A.APNT_YMD
FROM PATIENT P JOIN APPOINTMENT A ON P.PT_NO = A.PT_NO JOIN DOCTOR D ON A.MDDR_ID = D.DR_ID
WHERE DATE_FORMAT(A.APNT_YMD, '%Y-%m-%d') = '2022-04-13' and A.APNT_CNCL_YN = 'N' AND D.MCDP_CD = 'CS'
ORDER BY A.APNT_YMD;

자동차 평균 대여 기간 구하기

코드

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE)+1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

오답코드

SELECT CAR_ID, ROUND(AVG(END_DATE - START_DATE), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

헤비 유저가 소유한 장소

코드

SELECT * FROM PLACES P1
WHERE EXISTS (
    SELECT 1 FROM PLACES P2
    WHERE P1.HOST_ID = P2.HOST_ID
    GROUP BY HOST_ID
    HAVING COUNT(ID) >= 2
)
ORDER BY ID ASC;

오답코드

SELECT *
FROM PLACES
WHERE COUNT(HOST_ID) >= 2
ORDER BY ID;

우유와 요거트가 담긴 장바구니

코드1

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

코드2

SELECT CART_ID, NAMES
FROM (
    SELECT CART_ID, GROUP_CONCAT(NAME) AS NAMES
    FROM CART_PRODUCTS
    GROUP BY CART_ID
) TMP

WHERE NAMES LIKE '%Milk%' 
    AND NAMES LIKE '%Yogurt%'

자동차 대여 기록에서 장기/단기 대여 구분하기

코드

 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,
CASE WHEN DATEDIFF(END_DATE, START_DATE) < 29 THEN '단기 대여'
ELSE '장기 대여' END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE YEAR(START_DATE) = 2022 AND MONTH(START_DATE) = 9
ORDER BY HISTORY_ID DESC;

오답코드

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, CASE WHEN DATEDIFF(START_DATE, END_DATE)+1 >= 30 THEN '장기 대여'
ELSE '단기 대여' END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE YEAR(START_DATE) = 2022 AND MONTH(START_DATE) = 9
ORDER BY HISTORY_ID DESC;

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

코드

SELECT F.FLAVOR
FROM FIRST_HALF F LEFT JOIN (
    SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
    FROM JULY
    GROUP BY FLAVOR
) J ON F.FLAVOR = J.FLAVOR
ORDER BY (F.TOTAL_ORDER + J.TOTAL_ORDER) DESC LIMIT 3;