3단계(3)

세용용용용 2023. 12. 6. 10:16

sql쿼리 각 절의 우선 순위

from >>> where >>> select >>> order by >>> group by >>> having >>> limit(이절은 order by 이후 실행)

1. 자동차 대여 기록에서 대여중/대여 가능 여부 구분하기

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블 : 자동차 대여 기록 정보

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

나의 쿼리

SELECT A.car_id,
CASE
    WHEN B.car_id is null THEN '대여 가능' # B의 ID값이 NULL이면 대여가능
    ELSE '대여중' # 아니면 대여중
END AS AVAILABILITY
FROM
(SELECT car_id # 기존 테이블
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY car_id) A
LEFT JOIN # 왼쪽 테이블 기준 정렬
(SELECT car_id # 2022-10-16일 대여중인 차량 테이블
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE date_format(START_DATE, '%Y-%m-%d')<='2022-10-16' and date_format(END_DATE, '%Y-%m-%d')>='2022-10-16'
GROUP BY car_id) B
ON A.car_id=B.car_id # 차량 ID 기준 조인
ORDER BY car_id desc # car_id 내림차순 정렬

2. 헤비 유저가 소유한 장소

PLACES 테이블 : 공간 임대 서비스에 등록된 공간의 정보

문제 : 공간을 둘 이상 등록한 사람을 "헤비 유저"라고 부릅니다. 헤비 유저가 등록한 공간의 정보를 아이디 순으로 조회하는 SQL문을 작성

나의 쿼리

SELECT *
FROM PLACES 
WHERE host_id in ( # 공간을 둘 이상 등록한 id 필터
SELECT host_id
FROM PLACES 
GROUP BY host_id
HAVING count(*)>=2
)
ORDER BY id # id 기준 오름차순 정렬

3. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회

USED_GOODS_BOARD 테이블 : 중고 거래 게시판 정보

USED_GOODS_FILE : 중고 거래 첨부파일 정보

문제 : 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성해주세요. 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬해주세요. 기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요. 조회수가 가장 높은 게시물은 하나만 존재

나의 쿼리

SELECT concat('/home/grep/src/', board_id, '/', file_id, file_name, file_ext) as file_path # concat 으로 컬럼 만들어주기
FROM USED_GOODS_FILE 
WHERE board_id = ( # 조회수가 가장많은 id 필터
SELECT board_id
FROM USED_GOODS_BOARD 
ORDER BY views desc
LIMIT 1
)
ORDER BY file_id desc # file_id 내림차순 정렬

4. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블 : 자동차 대여 회사의 자동차 대여 기록 정보

문제 : 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외

나의 쿼리

SELECT date_format(start_date, '%m') as month, car_id, count(*) as records # 컬럼 선택
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE car_id in ( # 22년 8~10월 까지 총 대여 횟수가 5회 이상인 자동차 id 필터
SELECT car_id
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE date_format(start_date,'%Y-%m')>='2022-08' and date_format(start_date,'%Y-%m')<='2022-10'
GROUP BY car_id
HAVING count(*)>=5
)
and (date_format(start_date,'%Y-%m')>='2022-08' and date_format(start_date,'%Y-%m')<='2022-10')
GROUP BY month, car_id # month, car_id 기준으로 group by
ORDER BY month, car_id desc # 월 오름차순, 자동차 id 내림차순 정렬