#3. SQL_study_with Programmers

STRING,DATE 연습 (2)

 

 

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

문제

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

프로그래머스

 

환자, 의사, 진료 예약 정보를 담은 테이블에서
▶️ 2022년 4월 13일,
▶️ 취소되지 않은 흉부외과(CS) 예약 내역을 조회하는 SQL 작성

출력 항목:

• 진료예약번호 (APNT_NO)
• 환자이름 (PT_NAME)
• 환자번호 (PT_NO)
• 진료과코드 (MCDP_CD)
• 의사이름 (DR_NAME)
• 진료예약일시 (APNT_YMD)

정렬 조건:

• 진료예약일시 (APNT_YMD) 기준 오름차순 정렬

 

 

오답 쿼리

SELECT A.APNT_NO, T.PT_NAME, T.PT_NO, A.MCDP_CD, T.DR_NAME, A.APNT_YMD
FROM 
    (SELECT P.PT_NAME, D.DR_NAME, P.PT_NO
    FROM PATIENT AS P
    JOIN DOCTOR AS D
    ON P.TLNO = D.TLNO) AS T
JOIN APPOINTMENT AS A
ON T.PT_NO = A.PT_NO
WHERE A.MCDP_CD = 'CS' and left(A.APNT_YMD,10) = '2022-04-13'

오답 원인

• 3테이블 연결 위해 JOIN과 sub query 남발 (?)
• date 형식 무시

 

정답 쿼리

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_CNCL_YN = 'N'
    AND left(a.APNT_YMD,10) = '2022-04-13'
    AND a.MCDP_CD = 'CS'
ORDER BY a.APNT_YMD ASC;

 

배운점

• join 연달아 가능
• 날짜 조건-> left 함수 사용
• 조인 후 select 시-> 어디 테이블에서 온 컬럼인지 표시 필수

 

 

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

문제

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

프로그래머스

자동차 대여 기록 테이블 CAR_RENTAL_COMPANY_RENTAL_HISTORY에서
▶️ 평균 대여 기간이 7일 이상인 자동차의
▶️ 자동차 ID와 평균 대여 기간을 조회하는 SQL 작성

요구사항:

• 평균 대여 기간은 소수점 둘째 자리에서 반올림
• 결과는 평균 대여 기간 기준 내림차순 정렬
• 평균 대여 기간이 같으면 자동차 ID 기준으로 내림차순 정렬

출력 항목:

• 자동차 ID (CAR_ID)
• 평균 대여 기간 (AVERAGE_DURATION)

오답 쿼리

SELECT *
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE ROUND(AVG(LEFT(END_DATE,10)-LEFT(START_DATE,10),2) AS AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION, CAR_ID

오답 원인

• 날짜 간 차이 구하는 함수 사용x
  • 날짜 사이의 차이 != 경과된 일수
• alias를 where 절에 사용 (select에 사용할 것)

 

 

정답 쿼리

SELECT
car_id as 자동차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;

배운 점

• DATEDIFF: 두 날짜 간 차이
  • DATEDIFF(END_DATE, START_DATE): 두 날짜 사이의 차이 (경과 일수, 대여 첫날 제외)
    • START_DATE = 2022-10-01
    • END_DATE = 2022-10-03
      => 이라고 하면 DATEDIFF(END_DATE, START_DATE)는 2일로 계산
  • 하지만 실제 대여 기간을 생각해보면:
    • 10월 1일 (대여 시작일)
    • 10월 2일
    • 10월 3일 (대여 종료일)
      => 이라고 하면 실제 사용한 날짜는 3일이 맞음
  • 즉, DATEDIFF로 나온 값에 +1을 해줘야 실제 대여 기간이 됨!! (실제 대여 기간, 대여 첫날 포함)
• ROUND(~,1): 둘째자리에서 반올림(=첫째자리까지 표현)
• alias는 select 절에서, 모든 계산 이후 마지막에 as~로 간단히 나타낼 수 있음
• ORDER BY: 두 기준 나타낼 때=> 따로따로 기재(DESC & ASC와 함께)