[SQL문제풀기] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

문제

MYSQL

내가 작성한 오답

SELECT CAR_ID, CAR_TYPE, MIN(FEE) AS FEE
FROM
    (SELECT 
    C.CAR_ID, C.CAR_TYPE,
            CASE
                WHEN CAST(REGEXP_REPLACE(DURATION_TYPE, '[^0-9]+', '')AS UNSIGNED) <= 30
                    THEN ROUND((100-DISCOUNT_RATE)*DAILY_FEE*30*0.01,0)
                WHEN CAST(REGEXP_REPLACE(DURATION_TYPE, '[^0-9]+', '')AS UNSIGNED) > 30
                    THEN ROUND(DAILY_FEE*30,0) 
                END AS FEE
    FROM CAR_RENTAL_COMPANY_CAR C
    LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
              ON C.CAR_ID = H.CAR_ID
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
              ON C.CAR_TYPE = P.CAR_TYPE
    WHERE C.CAR_TYPE IN ('세단', 'SUV')
        AND (CAST(DATE_FORMAT(START_DATE,'%y%m%d') AS UNSIGNED) < 221101
            AND CAST(DATE_FORMAT(END_DATE,'%y%m%d') AS UNSIGNED) > 221130)) AS CONDITION_TB   
WHERE 500000 <= FEE AND FEE <2000000
GROUP BY CAR_ID
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;

WHERE C.CAR_ID NOT IN (SELECT CAR_ID
                        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY                        
                        WHERE CAST(DATE_FORMAT(START_DATE,'%y%m%d') AS UNSIGNED) < 221101
                            AND CAST(DATE_FORMAT(END_DATE,'%y%m%d') AS UNSIGNED) > 221130)

내가 작성한 정답

WITH FEE_TB AS (
        SELECT C.CAR_TYPE,C.CAR_ID,
            CASE
                WHEN CAST(REGEXP_REPLACE(DURATION_TYPE, '[^0-9]+', '')AS UNSIGNED) <= 30
                    THEN CAST((100-DISCOUNT_RATE)*DAILY_FEE*30*0.01 AS UNSIGNED)
                WHEN CAST(REGEXP_REPLACE(DURATION_TYPE, '[^0-9]+', '')AS UNSIGNED) > 30
                     THEN CAST(DAILY_FEE*30 AS UNSIGNED)                 END AS FEE
            FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
            JOIN CAR_RENTAL_COMPANY_CAR C ON C.CAR_TYPE = P.CAR_TYPE
            )
SELECT C.CAR_ID,C.CAR_TYPE, MIN(F.FEE) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN FEE_TB F ON C.CAR_ID = F.CAR_ID
WHERE C.CAR_ID NOT IN (SELECT CAR_ID
                        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY                        
                        WHERE CAST(DATE_FORMAT(START_DATE,'%y%m%d') AS UNSIGNED) <= 221130
                            AND CAST(DATE_FORMAT(END_DATE,'%y%m%d') AS UNSIGNED) >= 221101)
AND C.CAR_TYPE IN ('세단', 'SUV')
GROUP BY C.CAR_ID,C.CAR_TYPE
HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;

1. 처음 코드 작성 시 시분초까지 출력되어 DATE_FORMAT으로 형변환 시킨 후 다시 숫자로 변환했지만 시분초까지 출력되어도 DATE는 비교가 가능하다.



WHERE CAST(DATE_FORMAT(START_DATE,'%y%m%d') AS UNSIGNED) < 221101
      AND CAST(DATE_FORMAT(END_DATE,'%y%m%d') AS UNSIGNED) > 221130)

WHERE START_DATE < '2022-11-01' AND END_DATE > '2022-11-30'

2. 할인의 종류에는 7일이상, 30일 이상, 90일 이상이 존재한다고 하였는데 모든 차종에 3가지 경우의 할인이 존재한다는 생각을 하지 않고 어떤 차는 7일이상만 존재 어떤 차는 30일이상만 존재하거나 모두 존재하는 것으로 이해를 하고 모든 경우의 수를 넣어 계산했다.

WITH FEE_TB AS (
        SELECT C.CAR_TYPE,C.CAR_ID,
            CASE
                WHEN CAST(REGEXP_REPLACE(DURATION_TYPE, '[^0-9]+', '') AS UNSIGNED) <= 30
                    THEN CAST((100-DISCOUNT_RATE)*DAILY_FEE*30*0.01 AS UNSIGNED)
                WHEN CAST(REGEXP_REPLACE(DURATION_TYPE, '[^0-9]+', '') AS UNSIGNED) > 30
                     THEN CAST(DAILY_FEE*30 AS UNSIGNED)                 END AS FEE
            FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
            JOIN CAR_RENTAL_COMPANY_CAR C ON C.CAR_TYPE = P.CAR_TYPE
            )

위의 2개를 참고하여 다시 작성한 답

SELECT CAR_ID,CAR_TYPE,FEE
FROM (SELECT C.CAR_TYPE,C.CAR_ID,ROUND((100-DISCOUNT_RATE)*DAILY_FEE*30*0.01 , 0) AS FEE
            FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
            JOIN CAR_RENTAL_COMPANY_CAR C ON C.CAR_TYPE = P.CAR_TYPE
               WHERE P.DURATION_TYPE = '30일 이상'
            ) FEE_TB
WHERE CAR_ID NOT IN (SELECT CAR_ID
                        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY                        
                        WHERE START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01')
                            AND CAR_TYPE IN ('세단', 'SUV')
    AND FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;

ORACLE

내가 작성한 답

SELECT C.CAR_ID, C.CAR_TYPE, ROUND((100-P.DISCOUNT_RATE)*0.01*30*C.DAILY_FEE,0) FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
    ON C.CAR_TYPE = P.CAR_TYPE
WHERE C.CAR_TYPE IN ('세단','SUV')
    AND C.CAR_ID NOT IN (SELECT CAR_ID
                        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                        WHERE TO_CHAR(START_DATE,'YYYY-MM-DD') <= '2022-11-30'
                                AND TO_CHAR(END_DATE,'YYYY-MM-DD') >='2022-11-01')
    AND P.DURATION_TYPE='30일 이상'
    AND ((100-P.DISCOUNT_RATE)*0.01*30*C.DAILY_FEE >= 500000
        AND (100-P.DISCOUNT_RATE)*0.01*30*C.DAILY_FEE <2000000)
ORDER BY FEE DESC, C.CAR_TYPE ASC, C.CAR_ID DESC;

REGEXP 정규표현식

1. ORACLE과 MYSQL에서 REGEXP —> 이제는MYSQL도 ORACLE 표현식으로 쓰면 된다.

SELECT * 
FROM employees 
WHERE REGEXP_LIKE(name, '^[A-Z]');

SELECT * 
FROM employees 
WHERE name REGEXP '^[A-Z]';

2. REGEXP_REPLACE를 이용한 숫자만 추출하기

SELECT REGEXP_REPLACE(column_name, '[^0-9]', '') AS digits
FROM your_table;

3. REGEX함수들 정리 - ORACLE, MYSQL 공통적으로 사용가능

• REGEXP_LIKE : 특정 문자열이 정규 표현식과 일치하는지 확인

SELECT * 
FROM your_table 
WHERE REGEXP_LIKE(column_name, '정규표현식');
//column_name에 있는 값 중에서 '정규표현식' 패턴과 일치하는 모든 행이 결과로 반환

• REGEXP_INSTR : 정규 표현식과 일치하는 문자열의 위치를 반환

SELECT REGEXP_INSTR(column_name, '정규표현식') AS position 
FROM your_table;

• REGEXP_SUBSTR: 정규 표현식과 일치하는 부분 문자열을 반환

SELECT REGEXP_SUBSTR(column_name, '정규표현식') AS matched 
FROM your_table;

• REGEXP_REPLACE: 정규 표현식에 따라 문자열을 대체

SELECT REGEXP_REPLACE(column_name, '찾을패턴', '대체할문자열') AS replaced 
FROM your_table;