뚝딱뚝딱

https://school.programmers.co.kr/learn/courses/30/lessons/157339

💡 문제

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

문제의 조건

• CAR_TYPE 이 '세단' 또는 'SUV'
• 2022-11-01 부터 2022-11-30 까지 대여가능
• 30일간의 대여금액 >=500000 AND <2000000
• 위를 만족하는 자동차의 CAR_ID, CAR_TYPE, FEE 출력
• 단, 대여금액 기준 내림차순, 자동차 종류 기준 오름차순, 자동차 ID기준 내림차순 정렬

✅ 문제 풀이

• 대여 가능 여부를 판단한다.
  HISTORY 테이블에서 START_DATE와 END_DATE가 있다. 11월 달에 대여한 적이 있는지를 보려면, START_DATE는 11월 30일 이전이어야 하고, END_DATE는 11월 1일 이후여야 한다. SQL문으로 나타내면 다음과 같다.

CAR_RENTAL_COMPANY_RENTAL_HISTORY C 
    WHERE C.START_DATE<='2022-11-30' AND C.END_DATE>='2022-11-01'

• 그럼 이 날짜에 RENTAL한 적이 있는 자동차는 제외하면 된다. 
• 이렇게 생각할 수도 있다. 11월 달에 대여한 적 없는 자동차만 고르면 되는 거 아닌가? 로직을 어떻게 짜냐에 다르긴 하지만, 단순하게 각 레코드 마다 11월달에 대여한 적 없는 경우를 고른다면, 11월에도 대여했었지만 다른 날짜에도 대여했던 동일한 자동차도 포함되어 버릴 수 있기 때문에, 11월에 대여한 자동차의 ID 자체를 제외하는 식으로 접근하였다. 이는 NOT IN 연산자를 통해 제외할 수 있다.

A.CAR_ID NOT IN (
    SELECT DISTINCT C.CAR_ID 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY C 
    WHERE C.START_DATE<='2022-11-30' AND C.END_DATE>='2022-11-01'
)

• 첫 WHERE 조건을 하나 완성하였다.

• 30일간의 대여금액을 계산하기 위해 테이블을 조인한다.
  이를 구하기 위해서는 CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블을 INNER 조인해주어야 한다. 그래서 각 자동차마다 CAR_TYPE에 따라 DISCOUNT 정보를 갖도록 할것이다.

FROM CAR_RENTAL_COMPANY_CAR A JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
ON A.CAR_TYPE=B.CAR_TYPE

• 두 테이블의 공통 속성인 CAR_TYPE을 기준으로 JOIN한다.

• 조건에 따라 WHERE 절에 조건을 추가로 작성해준다.

AND A.CAR_TYPE IN('SUV', '세단') 
AND B.DURATION_TYPE = '30일 이상' 
AND A.DAILY_FEE*30*(1-B.DISCOUNT_RATE*0.01)>=500000 
AND A.DAILY_FEE*30*(1-B.DISCOUNT_RATE*0.01)<2000000

• CAR_TYPE이 'SUV', '세단' 중에 해당하면 조회한다. IN 연산자를 사용하였다.
• 조인 후에는 각 자동차 마다 CAR_TYPE에 따라 3개의 DISCOUNT_RATE 정보가 엮여있을 것이다. 이 중에서 DURATION_TYPE이 '30일 이상' 인것 만 사용하도록 한다.
• 대여금액은 조건에 해당하는 자동차의 DAILY_FEE*30*할인률 이기 때문에, 이 값이 50만원 이상 200만원 미만인 것만 추리도록 한다.

• 필요한 정보를 출력한다.

SELECT A.CAR_ID, A.CAR_TYPE, CAST(A.DAILY_FEE*30*(1-B.DISCOUNT_RATE*0.01)AS SIGNED) AS FEE

• 위에 조건에 부합하는 자동차의 CAR_ID, CAR_TYPE, FEE를 조회하도록 한다.
• 이때 FEE의 경우 *0.01 때문에 소수점 이하 두자리 형태로 출력되게 되는데, 조건의 주의사항에서 FEE의 경우 정수 형태로 출력하도록 되어있기 때문에, CAST 연산자를 사용하여 정수형태로 출력하도록 한다. AS SIGNED는 소수점을 없애면서 부호는 그대로 유지한다는 것이다.

• 조회결과를 주어진 정렬 조건에 맞도록 설정하기

ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;

• 마지막에 ORDER BY 문을 통해서 FEE를 기준으로 내림차순, CAR_TYPE을 기준으로 오름차순, CAR_ID를 기준으로 내림차순 정렬하도록 해준다.

✏ 코드 전문

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

I-2 데이터 모델링

Q 데이터 베이스를 구축하기 위한 용도를 위해 데이터 모델링을 수행하고 업무에 대한 설명은 별도의 표기법을 이용한다.(x)

=> 데이터 모델링이라는 것은 단지 데이터베이스만을 구축하기 위한 용도로 쓰이는 것이 아니라 데이터 모델링 자체로서 업무를 설명하고 분석하는 부분에서도 매우 중요한 의미를 가지고 있다.

I-13 엔터티의 이름을 부여하는 방법

Q 가능하면 약어를 사용하여 엔터티의 이름을 간결하고 명확하게 한다 (x)

=> 가능하면 약어를 사용하지 않는다.

=> 엔터티 생성의미대로 이름을 부여한다.

I-14 속성

Q 업무에서 필요로 하는 인스턴스에서 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위

=> 속성(ATTRIBUTE)

I-12 식별자

Q 다른 엔터티로부터 주식별자를 상속받지 않고, 자신의 고유한 주식별자를 가지며 사원, 부서, 고객, 상품, 자재 등이 예가 될 수 있는 엔터티

=>기본엔터티(키 엔터티)

I-20 관계

Q 데이터 모델링에는 존재적 관계와 행위에 의한 관계를 구분하는 표기법이 없지만, UML에서는 연관관계와 의존관계에 대해 다른 표기법을 가지고 표현하게 되어있다.

=> 실선과 점선의 표기법

I-23 관계

Q  업무기술서, 장표에 관계연결을 가능하게 하는 명사가 있는가? (X)

=> 동사가 있는가?

I-29 관계

Q 부모 엔터티의 주식별자를 자식엔터티에서 받아 손자엔터티까지 계속 흘려보내기 위해 비식별자관계를 고려한다 (X)

=> 식별자 관계를 고려