어제부터 SQL 코드카타 문제 풀기 전에 문제에서 요구하는 조회 데이터와, 조건 등에 대해서 먼저 정리해두고
아티클 SELECT문으로 SQL 쿼리를 시작하지 마세요에서 언급된 내용처럼 작성순서가 아닌, 작동순서에 따라서 쿼리를 작성하고 있다.
이렇게 작성하니
▶️ 장점
- 문제에서 요구하는 바를 놓치지 않고 작성 가능하여, 한번에 정답을 맞추게 됨
▶️ 단점
- TIL을 작성하고 문제를 푸는 시간이 조금 더 소요된다
하지만 점점 익숙해지니까 시간은 비슷하게 걸리는 것 같다. 오히려 머릿속으로 한번 정리하고 작성하다보니 복잡한 쿼리일 수록 시간이 절약된다.
특히, 코드카타 문제를 풀때에는 정렬이나 시간 조건 등 세세한 조건들 중 하나를 놓쳐서 한번에 정답이 안나오는 경우가 많았는데
이렇게 작성하니 한번에 바로바로 정답을 맞추게 되어 정확도가 올라가고 있다!
알고리즘도 이와 비슷한 방식으로 나만의 정답을 찾아가는 길을 만들어 보자 (파이썬 강의 좀 듣고....) 💪
56번. 특정 옵션이 포함된 자동차 리스트 구하기
CAR_RENTAL_COMPANY_CAR 테이블에서 '네비게이션' 옵션이 포함된 자동차 리스트를 출력하는 SQL문을 작성해주세요.
결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.
- 조회하려는 데이터 : 자동차 리스트 출력(전체 데이터, *)
- 조건 : OPTIONS = 네비게이션 포함(LIKE)
- 정렬 : 자동차 ID DESC
SELECT
*
FROM
CAR_RENTAL_COMPANY_CAR
WHERE
OPTIONS LIKE '%네비게이션%'
ORDER BY
CAR_ID DESC;57번. 조건에 부합하는 중고거래 상태 조회하기
USED_GOODS_BOARD 테이블에서 2022년 10월 5일에 등록된 중고거래 게시물의 게시글 ID, 작성자 ID, 게시글 제목, 가격, 거래상태를 조회하는 SQL문을 작성해주세요.
거래상태가 SALE 이면 판매중, RESERVED이면 예약중, DONE이면 거래완료 분류하여 출력해주시고,
결과는 게시글 ID를 기준으로 내림차순 정렬해주세요.
- 조회하려는 데이터 : 게시글 ID(BOARD_ID), 작성자 ID(WRITER ID), 게시글 제목(TITLE), 가격(PRICE), 거래상태(STATUS)
- 조건 : 2022-10-05 등록(CREATED_DATE), 거래상태 SALE이면 판매중, RESERVED면 예약중, DONE이면 거래완료
- 정렬 : 게시글 ID DESC
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 = '2022-10-05'
ORDER BY
BOARD_ID DESC;58번. 취소되지 않은 진료 예약 조회하기 (드디어 테이블 3개짜리 등장)
PATIENT, DOCTOR 그리고 APPOINTMENT 테이블에서 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회하는 SQL문을 작성해주세요.
진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 항목이 출력되도록 작성해주세요.
결과는 진료예약일시를 기준으로 오름차순 정렬해주세요.
- 조회하려는 데이터 :
IN PATIENT - 환자이름(PT_NAME), 환자번호(PT_NO)
IN DOCTOR - 의사이름(DR_NAME), 진료과코드(MCDP_CD)
IN APPOINTMENT - 진료예약번호(APNT_NO), 진료예약일시(APNT_YMD)
✅ 세 개의 테이블 중 APPOINTMENT 테이블이 나머지 두 개의 테이블과 공통 컬럼이 있기 때문에 APPOINTMENT 테이블 기준으로 INNER JOIN하기
(어차피 예약여부의 조회이기 때문에 예약하지 않은 환자 정보는 조회필요없기 때문에 INNER JOIN)
- 조건 : 2022-04-13 취소되지 않은(APNT_CNCL_YN = 'N'), 흉부외과(CS)
- 정렬 : 진료예약일시 ASC
SELECT
A.APNT_NO,
P.PT_NAME,
P.PT_NO,
D.MCDP_CD,
D.DR_NAME,
A.APNT_YMD
FROM
PATIENT P
JOIN APPOINTMENT A ON P.PT_NO = A.PT_NO
JOIN DOCTOR D ON D.DR_ID = A.MDDR_ID
WHERE
DATE_FORMAT(A.APNT_YMD, '%Y-%m-%d') = '2022-04-13'
AND A.APNT_CNCL_YN = 'N'
AND D.MCDP_CD LIKE 'CS%'
ORDER BY
A.APNT_YMD ASC;▶️ 세 개의 테이블 중 공통 컬럼이 있는 테이블을 찾아서 JOIN을 하는 문제였는데, 방법 자체는 간단하나 각 테이블에서 조회하려는 컬럼을 찾아서 작성하는 부분이 조금 번거로운 문제였다.😅 그래서 미리 요구사항을 찾아 써놓고 작성하니까 헷갈리지 않고 이번 문제도 한번에 정답!
59번. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고,
대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여
자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요.
이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고
결과는 자동차 ID를 기준으로 내림차순 정렬해주세요
- 조회하려는 데이터 : 자동차 ID(CAR_ID), 대여 여부 컬럼(AVAILABILITY)
- 조건 : 2022-10-16 대여여부 확인 필요, 반납날짜가 2022-10-16에도 대여중
- 정렬 : 자동차 ID DESC
SELECT
CAR_ID,
CASE
WHEN MAX(CASE WHEN START_DATE <= '2022-10-16'
AND END_DATE >= '2022-10-16' THEN 1 ELSE 0 END) = 1
THEN '대여중'
ELSE '대여 가능'
END AS AVAILABILITY
FROM
CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY
CAR_ID
ORDER BY
CAR_ID DESC;서브쿼리도 써보고 JOIN도 써보고... 정말 어떤 방법으로 접근해야하나 고민하다가 구글링해서 찾아낸 방법!
하나의 자동차 ID에 여러 대여 기록이 남아 있어서, 문제의 조건인 2022-10-16에 '대여중'일 경우에는 '대여중'을 출력하고 '대여 가능'일 경우에는 '대여 가능'을 출력할 수 있도록 MAX 집계함수를 사용한다.
MAX()안에 또 CASE WHEN을 사용할 수 있다는 사실을 처음 알았다..!
이런 방식으로도 기본적인 집계함수를 사용할 줄 알아야한다.
60번. 년, 월, 성별 별 상품 구매 회원 수 구하기
USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요.
결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요.
이때, 성별 정보가 없는 경우 결과에서 제외해주세요.
- 조회하려는 데이터 : 년, 월, 성별(남성 0, 여성 1) 별 상품을 구매한 회원 수 집계 (USER_ID는 중복없이! 상품을 한번이라도 구매했던 회원이 몇명인지를 구하는 것임)
- 조건 : 성별 정보가 없는 경우 결과 제외
- 정렬 : 년, 월, 성별 기준 ASC
피벗 테이블 형태의 결과물이 나와야 함!
SELECT
YEAR(SALES_DATE) AS `YEAR`,
MONTH(SALES_DATE) AS `MONTH`,
U.GENDER,
COUNT(DISTINCT OS.USER_ID) AS USERS
FROM
USER_INFO U
JOIN ONLINE_SALE OS
ON U.USER_ID = OS.USER_ID
WHERE
U.GENDER IS NOT NULL
GROUP BY
`YEAR`,
`MONTH`,
U.GENDER
ORDER BY
`YEAR`,
`MONTH`,
U.GENDER ASC