프로그래머스 SQL 고득점 Kit - STRING, DATE

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, IF(DATEDIFF(END_DATE, START_DATE) >= 29, "장기 대여", "단기 대여") 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

// 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문

 

- DATEDIFF(날짜1, 날짜2) 를 통해 두 날짜의 차이(일 수)를 구함, 기간을 구하려면 결과값에 1 더함

ex) DATEDIFF('2022-09-02', '2022-09-01') 는 1 리턴

 

2. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

SELECT CONCAT("/home/grep/src/", BOARD_ID, "/", FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE
WHERE BOARD_ID = (
    SELECT BOARD_ID
    FROM USED_GOODS_BOARD
    ORDER BY VIEWS DESC
    LIMIT 1
)
ORDER BY FILE_ID DESC

// 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문

 

- 서브쿼리를 활용해 조회수가 가장 높은 중고거래 게시물 게시글 ID 확보

- CONCAT(문자열1, 문자열2, ...) 은 둘 이상의 문자열을 합쳐서 반환, NULL이 포함되면 NULL을 반환

- CONCAT_WS(구분자, 문자열1, 문자열2, ...) 은 둘 이상의 문자열 있는 경우 사이에 구분자 넣어서 반환

 

3. 조건에 맞는 사용자 정보 조회하기

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

// 중고 거래 게시물을 3건 이상 등록한 사용자의 사용자 ID, 닉네임, 전체주소, 전화번호를 조회하는 SQL문

 

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

SELECT B.HISTORY_ID, FLOOR(A.DAILY_FEE*B.DURATION*(100-MAX(IFNULL(C.DISCOUNT_RATE,0)))/100) AS FEE
FROM CAR_RENTAL_COMPANY_CAR A
JOIN (
    -- DURATION 열과 함께 조인
    SELECT *, DATEDIFF(END_DATE, START_DATE)+1 AS DURATION
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
) B ON A.CAR_ID = B.CAR_ID
LEFT JOIN (
    -- 할인율 따라 조인, 7일 미만이면 NULL, 적용 가능한 할인률들
    SELECT CAR_TYPE, REGEXP_REPLACE(DURATION_TYPE, '[^0-9]', '') AS DURATION_TYPE, DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN 
) C ON A.CAR_TYPE = C.CAR_TYPE AND B.DURATION >= C.DURATION_TYPE
WHERE A.CAR_TYPE = '트럭'
GROUP BY B.HISTORY_ID
ORDER BY FEE DESC, B.HISTORY_ID DESC

// 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문

 

- 가독성은 좀 떨어지지만 DURATION 열을 별도로 추가하여 조인해주었고,

- DURATION 값이 7, 30, 90 이상이면 할인율 열을 LEFT JOIN 해주었음, NULL이면 할인율 0퍼이고 아니면 여러개 중 할인율 최대값을 취하는 방식으로..

SELECT A.HISTORY_ID, FLOOR((DATEDIFF(A.END_DATE, A.START_DATE)+1) * B.DAILY_FEE * (100-IFNULL(C.DISCOUNT_RATE, 0)) / 100) AS FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY A
JOIN CAR_RENTAL_COMPANY_CAR B ON A.CAR_ID = B.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C ON B.CAR_TYPE = C.CAR_TYPE
AND CASE
    WHEN DATEDIFF(A.END_DATE, A.START_DATE)+1 >= 90 THEN "90일 이상"
    WHEN DATEDIFF(A.END_DATE, A.START_DATE)+1 >= 30 THEN "30일 이상"
    WHEN DATEDIFF(A.END_DATE, A.START_DATE)+1 >= 7 THEN "7일 이상"
    ELSE NULL
END = C.DURATION_TYPE
WHERE B.CAR_TYPE = '트럭'
ORDER BY FEE DESC, A.HISTORY_ID DESC

- CASE, WHEN, THEN, ELSE, END 문법을 사용해서 좀 더 직관적으로 풀 수도 있음

- 마찬가지로 LEFT JOIN 하는데, JOIN 조건을 잘 주어 DISCOUNT RATE가 3가지 중 하나 또는 NULL 값으로 주어지게 함

 

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

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

// 2022년 10월 5일에 등록된 중고거래 게시물의 게시글 ID, 작성자 ID, 게시글 제목, 가격, 거래상태를 조회하는 SQL문

 

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

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

// 5월 1일을 기준으로 주문 ID, 제품 ID, 출고일자, 출고여부를 조회하는 SQL문

 

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

SELECT DISTINCT A.CAR_ID
FROM CAR_RENTAL_COMPANY_CAR A
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B 
ON A.CAR_ID = B.CAR_ID AND B.START_DATE BETWEEN '2022-10-01' AND '2022-10-31'
WHERE A.CAR_TYPE = '세단'
ORDER BY A.CAR_ID DESC

// 자동차 종류가 '세단'인 자동차들 중 10월에 대여를 시작한 기록이 있는 자동차 ID 리스트를 출력하는 SQL문

 

- JOIN 조건에서 필터링해도 WHERE에서 MONTH(START_DATE)=10 이렇게 필터링해도 됨

- ID 중복 제거하기 위해 DISTINCT 적용해도 되고, GROUP BY A.CAR_ID 해도 됨

 

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

SELECT *
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE "%네비게이션%"
ORDER BY CAR_ID DESC

// '네비게이션' 옵션이 포함된 자동차 리스트를 출력하는 SQL문

 

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

SELECT CAR_ID, ROUND(SUM(DATEDIFF(END_DATE,START_DATE)+1)/COUNT(HISTORY_ID), 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

// 평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION) 리스트를 출력하는 SQL문

 

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

SELECT A.APNT_NO, P.PT_NAME, P.PT_NO, D.MCDP_CD, D.DR_NAME, A.APNT_YMD
FROM APPOINTMENT A
JOIN PATIENT P ON A.PT_NO = P.PT_NO 
JOIN DOCTOR D ON A.MDDR_ID = D.DR_ID
WHERE A.APNT_YMD >= '2022-04-13 00:00:00' AND A.APNT_YMD <= '2022-04-13 23:59:59' AND A.APNT_CNCL_YN = 'N' AND D.MCDP_CD = 'CS'
ORDER BY A.APNT_YMD ASC

// 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회하는 SQL문

 

- BETWEEN '2022-04-13 00:00:00' AND '2022-04-13 23:59:59' 해도 되고, A.APNT_YMD LIKE "2022-04-13%" 해도 되고, DATE(A.APNT_YMD) = '2022-04-13' 이렇게 해도 됨

- TIME은 HH:MM:SS 형태의 출력, DATE는 YYYY-MM-DD 형태의 출력, DATETIME은 YYYY-MM-DD HH:MM:SS 형태의 출력, TIMESTAMP는 YYYY-MM-DD HH:MM:SS(.FFFFFF) 형태의 출

 

11. 루시와 엘라 찾기

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

// 동물 보호소에 들어온 동물 중 이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의 아이디와 이름, 성별 및 중성화 여부를 조회하는 SQL 문

 

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

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME REGEXP 'EL' AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME ASC

// 동물 보호소에 들어온 동물 이름 중, 이름에 "EL"이 들어가는 개의 아이디와 이름을 조회하는 SQL문을 작성 (대소문자 구분하지 않음)

 

- REGEXP는 대소문자를 구분하지 않음 (정 불안하면 WHERE NAME REGEXP '[Ee][Ll]' 이렇게 하면 됨)

- UPPER(NAME) LIKE "%EL%" 혹은 LOWER(NAME) LILKE "%el%" 이렇게 해도 됨

 

13. 중성화 여부 파악하기

SELECT ANIMAL_ID, NAME, CASE
    WHEN SEX_UPON_INTAKE LIKE "%Neutered%" THEN "O"
    WHEN SEX_UPON_INTAKE LIKE "%Spayed%" THEN "O"
    ELSE "X"
END AS 중성화
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

// 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문

 

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

SELECT I.ANIMAL_ID, I.NAME
FROM ANIMAL_INS I
JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
ORDER BY DATEDIFF(O.DATETIME, I.DATETIME) DESC
LIMIT 2

// 입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 SQL문

 

15. DATETIME에서 DATE로 형 변환

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, "%Y-%m-%d") AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

// 각 동물의 아이디와 이름, 들어온 날짜를 조회하는 SQL문

 

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

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

// 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문