String, DATE

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

 SELECT a.APNT_NO, p.PT_NAME, p.PT_NO
    , a.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.MCDP_CD = 'CS'
    AND APNT_YMD LIKE '2022-04-13%'
    AND APNT_CNCL_YN = 'N'
ORDER BY APNT_Y

---

조건별로 분류하여 주문상태 출력하기

SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE, '%Y-%m-%d')
, (CASE 
    WHEN OUT_DATE > '2022-05-01' THEN '출고대기'
    WHEN OUT_DATE <= '2022-05-01' THEN '출고완료'
    ELSE '출고미정'
    END ) AS 출고여부
FROM FOOD_ORDER
ORDER BY ORDER_ID

---

자동차 대여 기록 별 대여 금액 구하기

with truck AS ( 
    SELECT HISTORY_ID, c.CAR_ID, CAR_TYPE, DAILY_FEE
    , DATEDIFF(END_DATE, START_DATE) + 1 AS DAYS
    FROM CAR_RENTAL_COMPANY_CAR c
    JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h
        ON c.CAR_ID = h.CAR_ID)

SELECT HISTORY_ID
    , ROUND(CASE WHEN DAYS < 7 THEN DAILY_FEE*DAYS
                WHEN DAYS < 30 THEN (daily_fee-(daily_fee*0.05))*DAYS
                WHEN DAYS < 90 THEN (daily_fee-(daily_fee*0.08))*DAYS
                ELSE (daily_fee-(daily_fee*0.15))*DAYS
        END, 0) AS FEE
FROM truck
WHERE CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC

---

대여 기록이 존재하는 자동차 리스트 구하기

SELECT DISTINCT c.CAR_ID
FROM CAR_RENTAL_COMPANY_CAR c
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h
    ON c.CAR_ID = h.CAR_ID
WHERE CAR_TYPE = '세단'
    AND START_DATE LIKE '2022-10-%'
ORDER BY CAR_ID DESC

---

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

SELECT CAR_ID
    , ROUND(AVERAGE, 1) AS AVERAGE_DURATION
FROM (SELECT *, SUM(DATEDIFF(END_DATE, START_DATE) + 1) / COUNT(CAR_ID) AS AVERAGE
      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
      GROUP BY CAR_ID) as A
WHERE AVERAGE >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

---

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

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

---

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

• 1 더해줘야 함

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 BETWEEN '2022-09-01' AND '2022-09-30'
ORDER BY HISTORY_ID DESC

---

루시와 엘라 찾기

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY ANIMAL_ID

---

이름에 el이 들어가는 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE LOWER(NAME) LIKE LOWER('%el%') 
    AND ANIMAL_TYPE='Dog'
ORDER BY NAME

---

중성화 여부 파악하기

SELECT ANIMAL_ID, NAME,
    CASE 
        WHEN SEX_UPON_INTAKE REGEXP 'Neutered|Spayed' 
            THEN 'O'
            ELSE 'X'
        END AS 중성화
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

---

오랜 기간 보호한 동물(2)

SELECT o.ANIMAL_ID, o.NAME
FROM ANIMAL_INS i
JOIN ANIMAL_OUTS o
	ON i.ANIMAL_ID = o.ANIMAL_ID
ORDER BY o.DATETIME - i.DATETIME DESC 
LIMIT 2

---

카테고리 별 상품 개수 구하기

SELECT SUBSTRING(PRODUCT_CODE	, 1, 2) CATEGORY, COUNT(*) PRODUCTS
FROM PRODUCT
GROUP BY SUBSTRING(PRODUCT_CODE	, 1, 2)
ORDER BY CATEGORY

---

DATETIME에서 DATE로 형 변환

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') as 날짜
FROM ANIMAL_INS

---