[MySQL] 프로그래머스 SQL 고득점 Kit - String, Date 16개

String, Date 쪽은 생소한 함수가 많은 것 같으니 풀면서 다 외워버리기

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

날짜 차이 구하는 함수 : DATEDIFF(끝나는 날짜, 시작하는 날짜) + 1

ROUND를 활용하여 반올림 할 자릿수 써주기 (소수 두번째 자리 반올림 → 소수 첫째자리까지만 나타냄)

-- 코드를 입력하세요
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

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

-- 코드를 입력하세요
SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, 
    CASE WHEN STATUS = 'SALE' THEN '판매중' 
         WHEN STATUS = 'DONE' THEN '거래완료' 
         ELSE '예약중'
         END AS STATUS
FROM USED_GOODS_BOARD
WHERE CREATED_DATE LIKE '2022-10-05'
ORDER BY BOARD_ID DESC

답은 맞았으나 date를 조금 확실히 기재하는게 나은듯

WHERE DATE_FORMAT(CREATED_DATE, '%Y-%m-%d') = '2022-10-05'

date_format 함수를 사용해주는게 좋은듯

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

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

3개 테이블을 조인할 때는 항상 침착하게 할것 join(inner join)

-- 코드를 입력하세요
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 DATE_FORMAT(A.APNT_YMD,'%Y-%m-%d')='2022-04-13' and A.APNT_CNCL_YN = 'N' and A.MCDP_CD = 'CS'
ORDER BY A.APNT_YMD

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

date_format 활용하기!!

-- 코드를 입력하세요
SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE, '%Y-%m-%d') as OUT_DATE, 
    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

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

중고거래 게시판과 중고거래 게시판 첨부파일을 join, board_id로 join

concat으로 문자열, column 합치기!!

조회수가 가장 많은 것은 view 기준 내림차순으로 Limit 1

-- 코드를 입력하세요
select concat('/home/grep/src/',B.BOARD_ID, '/', F.FILE_ID, F.FILE_NAME, F.FILE_EXT) as FILE_PATH
from USED_GOODS_BOARD B join USED_GOODS_FILE F on B.BOARD_ID = F.BOARD_ID
WHERE B.BOARD_ID = (
    SELECT BOARD_ID FROM USED_GOODS_BOARD 
    order by views desc
    limit 1)
order by F.file_id desc

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

-- 코드를 입력하세요
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 C.CAR_TYPE = '세단' AND H.START_DATE like '2022-10-%'
ORDER BY C.CAR_ID DESC

자동차 대여 기록 별 대여 금액 구하기 Lv4 → 최근 본 금융권 코테에서 비슷한 문제 나왔었다. 잔뜩긴장 3중 JOIN

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

역대급으로 너무 어렵다. 여러번 풀어보자..

WITH value AS (
    SELECT car.daily_fee, car.car_type, his.history_id,
           DATEDIFF(end_date, start_date) + 1 AS period,
    CASE 
      WHEN DATEDIFF(end_date, start_date) + 1 >= 90 THEN '90일 이상'
      WHEN DATEDIFF(end_date, start_date) + 1 >= 30 THEN '30일 이상'
      WHEN DATEDIFF(end_date, start_date) + 1 >= 7 THEN '7일 이상'
      ELSE 'NONE' END AS duration_type
FROM car_rental_company_rental_history AS his
INNER JOIN car_rental_company_car AS car ON car.car_id = his.car_id
WHERE car.car_type = '트럭')   

SELECT value.history_id, 
    ROUND(value.daily_fee * value.period * 
          (100 - IFNULL(plan.discount_rate,0)) / 100) AS FEE
FROM value
LEFT JOIN car_rental_company_discount_plan AS plan 
    ON plan.duration_type = value.duration_type 
    AND plan.car_type = value.car_type
ORDER BY 2 DESC, 1 DESC

join on 뒤에 and로 여러개의 조건을 둘 수 있다!!

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

CAR_RENTAL_COMPANY_CAR 테이블에서 '네비게이션' 옵션이 포함된 자동차 리스트를 출력하는 SQL문을 작성해주세요. 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.

포함!!

-- 코드를 입력하세요
SELECT *
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%네비게이션%'
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

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

USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 중고 거래 게시물을 3건 이상 등록한 사용자의 사용자 ID, 닉네임, 전체주소, 전화번호를 조회하는 SQL문을 작성해주세요. 이때, 전체 주소는 시, 도로명 주소, 상세 주소가 함께 출력되도록 해주시고, 전화번호의 경우 xxx-xxxx-xxxx 같은 형태로 하이픈 문자열(-)을 삽입하여 출력해주세요. 결과는 회원 ID를 기준으로 내림차순 정렬해주세요.

-- 코드를 입력하세요
SELECT USER_ID, NICKNAME, CONCAT(CITY, ' ', STREET_ADDRESS1,' ',STREET_ADDRESS2) AS '전체주소', 
CONCAT(SUBSTR(TLNO,1,3),'-', SUBSTR(TLNO,4,4),'-',SUBSTR(TLNO,8))
AS '전화번호'

FROM USED_GOODS_USER 
WHERE USER_ID IN (
    SELECT WRITER_ID
    FROM USED_GOODS_BOARD
    GROUP BY WRITER_ID
    HAVING COUNT(*) >= 3
    )
ORDER BY USER_ID DESC

감격적이게도 이젠 뭘 찾지도 않고 금새 문자열처리를 어떻게든 하는 나를 발견할 수 있었다

concat(left(TLNO,3),'-',mid(TLNO,4,4),'-',right(TLNO,4)) as 전화번호 이렇게 하는 방법도 있다! 왼쪽에서부터 자르고 중간에서 자르고 오른쪽에서부터자르기..!!!

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

날짜 차이 구하는 함수 : DATEDIFF(끝나는 날짜, 시작하는 날짜) + 1

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문을 작성해주세요. 결과는 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

-- 코드를 입력하세요
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

루시와 엘라 찾기 Lv2

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

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME LIKE 'LUCY' OR 
        NAME LIKE 'Ella' OR
        NAME LIKE 'Pickle' OR
        NAME LIKE 'Rogan' OR
        NAME LIKE 'Sabrina' OR
        NAME LIKE 'Mitty'

효율적으로 풀면,,

-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')

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

동물 보호소에 들어온 동물 이름 중, 이름에 "EL"이 들어가는 개의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 이름 순으로 조회해주세요. 단, 이름의 대소문자는 구분하지 않습니다.

-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME LIKE "%EL%" AND ANIMAL_TYPE ='DOG'
ORDER BY NAME

중성화 여부 파악하기 Lv2

중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시해주세요.

-- 코드를 입력하세요
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

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

입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 기간이 긴 순으로 조회해야 합니다.

with tmp1 as( 
    select *
    from animal_ins), 
    tmp2 as(
    select *
    from animal_outs)

SELECT t1.ANIMAL_ID, t1.NAME
FROM tmp1 t1 join tmp2 t2 on t1.animal_id = t2.animal_id
order by t2.datetime - t1.datetime desc
limit 2

join으로 간단하게 풀고 DATEDIFF 이용해도 괜찮았을 것 같다

DATETIME에서 DATE로 형 변환 Lv2

ANIMAL_INS 테이블에 등록된 모든 레코드에 대해, 각 동물의 아이디와 이름, 들어온 날짜1를 조회하는 SQL문을 작성해주세요. 이때 결과는 아이디 순으로 조회해야 합니다.

-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME,'%Y-%m-%d') AS DATE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

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

PRODUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요.

-- 코드를 입력하세요
SELECT SUBSTR(PRODUCT_CODE,1,2) AS CATEGORY, COUNT(*)
FROM PRODUCT
GROUP BY CATEGORY
ORDER BY CATEGORY ASC

SQL 문자열 자르기

• SUBSTRING( 문자열, 시작위치, 길이 ) : 문자열에서 시작 위치부터 길이만큼 출력
• LEFT( 문자열, 길이 ) : 문자열에서 왼쪽부터 길이만큼 출력
• RIGHT( 문자열, 길이 ) : 문자열에서 오른쪽부터 길이만큼 출력