[MySQL] SQL 문제풀이 (String, Date)

SQL에서 글자와 날짜는 어떻게 다루지?

 

풀지 않은 10문제를 풀어보며 알아보도록 하겠습니다

---

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

처음엔 아래와 같이 중첩 IF문을 사용했는데 CASE를 사용하면 좋을 것 같다고 생각했다.

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, IF(STATUS='DONE', '거래완료', IF(STATUS='SALE', '판매중', '예약중')) AS STATUS
FROM USED_GOODS_BOARD
WHERE CREATED_DATE LIKE '2022-10-05'
ORDER BY BOARD_ID DESC;

 

CASE를 사용하면 아래와 같다!

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;

---

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

우선 시작 날짜와 종료 날짜에서, 시간을 제외한 날짜만 추출해야하기 때문에 LEFT를 사용했다.

그리고 장기와 단기를 구분하기 위해 IF를 사용했다.

여기서 날짜 차이가 29일인 것을 처음에 단기라고 했다가 틀렸다.

문제는 30일 이상인 경우 장기라고 되어있지만,  29일이 차이나는 기록도 장기다

 

왜냐하면...

2022-09-01 과 2022-09-02의 경우 차이가 1이지만 실제 대여 날짜는 2일이기 때문이다.

그래서 차이가 29일 나도, 실제 대여는 30일을 한것이기 때문에 장기에 속하는 것이다 (두둥)

SELECT HISTORY_ID, CAR_ID,
    LEFT(START_DATE, 10) AS START_DATE,
    LEFT(END_DATE, 10) AS END_DATE, 
    IF(DATEDIFF(END_DATE, START_DATE) < 29, '단기 대여', '장기 대여') AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09-%'
ORDER BY HISTORY_ID DESC;

---

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

이 문제는, 두 테이블을 조인한 뒤 세단이며 10월에 대여한 기록이 있는 차의 번호를 중복 없이 출력하면 된다.

10월에 대여한 차를 출력하라고 했는데, 2022 데이터만 있는지

2022-10-% 로 해도 정답이고 %-10-%으로 해도 정답이다.

SELECT DISTINCT(C.CAR_ID)
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H LEFT JOIN CAR_RENTAL_COMPANY_CAR AS C
ON C.CAR_ID = H.CAR_ID
WHERE C.CAR_TYPE = '세단' AND H.START_DATE LIKE '%-10-%'
ORDER BY C.CAR_ID DESC;

---

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

LIKE를 통해 네비게이션이 포함되었는지 판단할 수 있다.

테이블에 있는 모든 속성을 불러와야하기 때문에 모두 불러오는 와일드카드인 * 를 사용했다.

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

---

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

우선 유저 아이디 별로 그룹화를 한다음에, having을 사용해 3번 이상의 기록이 있는 유저를 조회하면 된다.

다만, 주소 표기 방식과 전화번호 표기 방식을 유의해야 한다.

 

주소의 경우 '도시, 주소1, 주소2' 를 하나의 문자열로 합쳐야한다. 이를 위해 CONCAT을 사용했다.

CONCAT(문자열1, 문자열2)를 하면 문자열 1과 문자열 2가 합쳐지고,

중간에 띄어쓰기를 하고 싶다면 ' '를 추가하면 된다.

 

전화번호의 경우, 문자열을 쪼갠 뒤 문자를 삽입해야 한다.

SUBSTRING(문자열, 시작위치, 길이) 를 사용하면, 문자열을 자를 수 있다. 문자열은 인덱스 1부터 시작한다.

string의 str만 자르고 싶다면, SUBSTRING('string', 1, 3) 을 하면 앞의 str만 잘라진다.

그리고 사이에 - 기호를 추가해야 하므로 CONCAT을 이용했다.

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

---

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

처음에는 LIMIT 1을 해서 조회수가 가장 많은 것을 조회하려고 했다.

하지만 조회수가 많은 상품의, 첨부파일이 여러개일 수 있으므로 서브쿼리를 이용했다.

서브쿼리를 이용해서, 가장 많은 조회수를 가진 게시글과 같은 조회수를 가진 파일을 select 했다.

 

위 문제와 같이, CONCAT를 이용했다.

SELECT CONCAT('/home/grep/src/', F.BOARD_ID, '/', F.FILE_ID, F.FILE_NAME, F.FILE_EXT) AS FILE_PATH
FROM USED_GOODS_BOARD AS B LEFT JOIN USED_GOODS_FILE AS F
ON B.BOARD_ID = F.BOARD_ID
WHERE B.VIEWS = (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD);

---

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

SQL 고득점 키트를 풀면서 제일 까다롭다고 느낀 문제가 이 문제다.

세 테이블을 다 join 해야하고, 대여 기간에 따른 각각의 할인률을 적용해야한다.

여러가지 방법이 있겠지만 고심 끝에 고른 방법은 아래와 같다.

 

일단 세 테이블을 조인하는데, 할인률과 관련된 테이블을 조인할 때에는

on절에, 원하는 할인률을 가진 컬럼만 조인하게 했다.

죽, [ 대여기간 - DURATION_TYPE ] 이

[ 8 - 7일 이상] 인 것은 필요해도

[ 8 - 30일 이상] 인 결과는 필요가 없으므로, ON절을 통해 DURATION_TYPE을 맞춘 것이다.

 

그런데 이 CASE WHEN.. 으로 지정한 구문이 WHERE절로 가면 안 된다..

왜냐하면 7일 이하일 경우, NULL 값을 저장하고 싶은데 WHERE절에 이 조건이 있으면 7일 이하는 칼럼은 조인 결과에 포함이 안되기 때문이다.

 

CASE WHEN을 ON절에 넣고 JOIN 시키면 결과는 다음과 같다.

우리가 원하는대로, 대여 기간에 따른 할인율이 잘 매칭되어 조인된 것을 볼 수 있다.

7일 이하의 대여기간을 가진 것은 NULL로 설정했기 때문에 비어있는 것을 확인할 수 있다.

 

그 다음, SELECT 절을 통해, 할인률이 NULL이라면 요금을 다 청구하게 하고,

그렇지 않다면 할인률에 따라 가격을 측정하면 된다.

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

---

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

if를 사용하여 조건에 맞게 조회하면 된다.

SELECT ORDER_ID, PRODUCT_ID, LEFT(OUT_DATE, 12) AS OUT_DATE,
    IF(OUT_DATE <= '2022-05-01', '출고완료',
       IF(OUT_DATE > '2022-05-01', '출고대기', '출고미정')) AS 출고여부
FROM FOOD_ORDER
ORDER BY ORDER_ID;

---

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

car_id로 그룹화를 한 다음, 대여 기간을 (기간은 datediff 함수를 통해 구할 수 있다)

avg를 통해 대여 기간의 평균을 구한 뒤,

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 AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

---

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

세 테이블을 join한 뒤, 조건에 맞추어 조회하면 된다!

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

---

 

SQL 고득점 Kit 끝!

언제 다 푸나 했는데 올해 안에 다 풀어서 뿌듯하다