[mysql] LV 1~2 정답률 85% 이하의 문제들

LV 1~2 정답률 85% 이하의 문제들

https://school.programmers.co.kr/learn/challenges?order=acceptance_desc&page=2&levels=2%2C1&languages=mysql

많은 문제들 중에, 정답률이 낮은 8문제의 쿼리를 기록으로 남겨본다…

최댓값 구하기

SELECT DATETIME AS "시간"
FROM ANIMAL_INS
ORDER BY DATETIME DESC
LIMIT 1;

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

SELECT FIRST_HALF.FLAVOR
FROM FIRST_HALF, ICECREAM_INFO
WHERE FIRST_HALF.TOTAL_ORDER>=3000 AND
    ICECREAM_INFO.INGREDIENT_TYPE="fruit_based"

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

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 LIKE "2022-10-05"
ORDER BY BOARD_ID DESC;

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

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

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

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 AVG(DATEDIFF(END_DATE,START_DATE)+1) >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_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(END_DATE,START_DATE) >= 29
        THEN '장기 대여'
        ELSE '단기 대여'
    END AS 'RENT_TYPE'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09-%'
ORDER BY HISTORY_ID DESC;

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

SELECT B.TITLE, 
       B.BOARD_ID, 
       R.REPLY_ID, 
       R.WRITER_ID, 
       R.CONTENTS, 
       DATE_FORMAT(R.CREATED_DATE,"%Y-%m-%d") "CREATED_DATE"
FROM USED_GOODS_BOARD B
JOIN USED_GOODS_REPLY R
ON B.BOARD_ID=R.BOARD_ID
WHERE B.CREATED_DATE LIKE "2022-10%"
ORDER BY CREATED_DATE, TITLE;