String, Date | MySQL

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

SELECT LEFT(PRODUCT_CODE, 2) AS CATEGORY, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY CATEGORY
;

SELECT CONCAT('/home/grep/src/', B.BOARD_ID, '/', B.FILE_ID, B.FILE_NAME, B.FILE_EXT) AS PATH
FROM USED_GOODS_BOARD A JOIN USED_GOODS_FILE B 
ON A.BOARD_ID = B.BOARD_ID
WHERE A.VIEWS = (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD)
ORDER BY B.FILE_ID DESC
;

SELECT B.USER_ID, B.NICKNAME,
CONCAT(B.CITY, ' ', B.STREET_ADDRESS1, ' ', B.STREET_ADDRESS2) AS '전체주소',
CONCAT(SUBSTR(B.TLNO, 1, 3), '-', SUBSTR(B.TLNO, 4, 4), '-', SUBSTR(B.TLNO, 8, 4)) AS '전화번호'
FROM USED_GOODS_BOARD A JOIN USED_GOODS_USER B
ON A.WRITER_ID = B.USER_ID
GROUP BY A.WRITER_ID
HAVING COUNT(A.WRITER_ID) >= 3
ORDER BY B.USER_ID DESC
;

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

SELECT H.HISTORY_ID, 
TRUNCATE(C.DAILY_FEE
* (DATEDIFF(H.END_DATE, H.START_DATE) + 1)
* (CASE 
    WHEN DATEDIFF(H.END_DATE, H.START_DATE) +1 < 7 THEN 1
    WHEN DATEDIFF(H.END_DATE, H.START_DATE) +1 < 30 THEN 0.95
    WHEN DATEDIFF(H.END_DATE, H.START_DATE) +1 < 90 THEN 0.92
    ELSE 0.85 END), 0) AS FEE
FROM
CAR_RENTAL_COMPANY_CAR C 
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C.CAR_ID = H.CAR_ID
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D ON C.CAR_TYPE = D.CAR_TYPE
 
WHERE C.CAR_TYPE = '트럭'
GROUP BY H.HISTORY_ID
ORDER BY FEE DESC, H.HISTORY_ID DESC
;