susinlee 님의 블로그

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


[단계별 풀이]
1. START_DATE 가 2022년 9월인 행들을 필터

2. END_DATE와 START_DATE의 차이 + 1 (빌리는 당일도 기간에 포함)
3. 위의 컬럼이 30일 이상인 행들을 장기 대여 그렇지 않으면 단기 대여 표시 (RENT_TYPE 컬럼) 

4. 대여 기록 ID를 내림차순 정렬

[정답]

SELECT HISTORY_ID,
       CAR_ID,
       DATE_FORMAT(START_DATE, '%Y-%m-%d') START_DATE,
       DATE_FORMAT(END_DATE, '%Y-%m-%d') 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

[문제]
REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.


[단계별 풀이]
1. 두 테이블을 REST_ID 기준으로 JOIN

2. 주소가 서울인 행을 필터링

3. 식당ID, 이름, 종류, 즐찾수, 주소를 GROUP BY → 하나의 식당이 나옴

4. 그룹화한 리뷰점수들을 평균

5. 평균점수를 내림차순, 즐찾수를 내림차순 정렬

[정답]

SELECT R.REST_ID,
       R.REST_NAME,
       R.FOOD_TYPE,
       R.FAVORITES,
       R.ADDRESS,
       ROUND(AVG(RE.REVIEW_SCORE), 2) AS SCORE
FROM REST_INFO R
JOIN REST_REVIEW RE
    ON R.REST_ID = RE.REST_ID
WHERE ADDRESS LIKE '서울%'
GROUP BY R.REST_ID,
         R.REST_NAME,
         R.FOOD_TYPE,
         R.FAVORITES,
         R.ADDRESS
ORDER BY SCORE DESC, FAVORITES DESC

[문제]
USER_INFO (I) 테이블과 ONLINE_SALE (S) 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.


[단계별 풀이]
1. 테이블을 USER_ID를 기준으로 JOIN

2. 성별이 NULL 인 행은 제외

3. 년, 월, 성별을 GROUP BY

4. 고유한(DISTINCT) S.USER_ID 를 COUNT

5. 년 월 성별 기준으로 오름차순


[정답]

SELECT YEAR(S.SALES_DATE) YEAR,
       MONTH(S.SALES_DATE) MONTH,
       GENDER,
       COUNT(DISTINCT S.USER_ID) USERS
FROM USER_INFO I
JOIN ONLINE_SALE S 
    ON I.USER_ID = S.USER_ID
WHERE I.GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER

[문제]
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요. 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.


[단계별 풀이]
1. CAR_ID 로 GROUP BY (중복제거)

2. 2022-10-16이 START_DATE와 END_DATE에 있다면 1 (TRUE)

3. 대여중이 한번이라도 있으면 대여중이므로 MAX함수 적용한 값이 1이면 대여중 아니면 대여 가능

4. 해당 컬럼을 AVAILABILITY 별칭 지정 후 조회

5. CAR_ID로 내림차순 정렬

[정답]

SELECT CAR_ID,
       CASE 
           WHEN MAX('2022-10-16' BETWEEN START_DATE AND END_DATE)=1 THEN '대여중'
           ELSE '대여 가능'
       END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

[문제]
PATIENT(P), DOCTOR(D) 그리고 APPOINTMENT(A) 테이블에서 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회하는 SQL문을 작성해주세요. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 항목이 출력되도록 작성해주세요. 결과는 진료예약일시를 기준으로 오름차순 정렬해주세요.


[단계별 풀이]
1. 세 테이블을 A.MDDR_ID = D.DR_ID 와  A.PT_NO =P.PT_NO  기준으로 JOIN

2. 예약 일시가 2022-04-13이면서 A테이블의 진료과 코드가 CS이면서 예약 취소 여부가 N인 행들만 필터링

3. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시를 조회

4. 진료예약일시 기준으로 오름차순 정렬


[정답]

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