SQL 56-60.

56. 특정 옵션이 포함된 자동차 리스트 구하기

CAR_RENTAL_COMPANY_CAR 테이블에서 '네비게이션' 옵션이 포함된 자동차 리스트를 출력하는 SQL문을 작성해주세요. 결과는 자동차 ID를 기준으로 내림차순 정렬

select car_id,
       car_type,
       daily_fee,
       options
from car_rental_company_car
where options like '%네비게이션%'
order by car_id desc

★ 출제포인트

like 를 써서 네비게이션이 있는지 확인

 

57. 조건에 부합하는 중고거래 상태 조회하기

USED_GOODS_BOARD 테이블에서 2022년 10월 5일에 등록된 중고거래 게시물의 게시글 ID, 작성자 ID, 게시글 제목, 가격, 거래상태를 조회하는 SQL문을 작성해주세요. 거래상태가 SALE 이면 판매중, RESERVED이면 예약중, DONE이면 거래완료 분류하여 출력해주시고, 결과는 게시글 ID를 기준으로 내림차순 정렬

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 date_format(created_date, '%Y-%m-%d') = '2022-10-05'
order by board_id desc

중요

date_format(컬럼, '%Y-%m-%d')를 하면 문자열(string)으로 니오기 때문에 '2022-10-05' 따옴표 필수

아래처럼 case status when~으로 푸는 것도 가능

case 조건컬럼

       when '값1' then '출력값1'

       when '값2' then '출력값2'

       else '본값' end as 별칭

select board_id,
       writer_id,
       title,
       price,
       case status 
            when 'SALE' then '판매중'
            when 'RESERVED' then '예약중'
            when 'DONE' then '거래완료' end as status
from used_goods_board
where date_format(created_date, '%Y-%m-%d') = '2022-10-05'
order by board_id desc

 

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

 

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

select a.apnt_no,
       p.pt_name,
       a.pt_no,
       a.mcdp_cd,
       d.dr_name,
       a.apnt_ymd
from appointment a join patient p on a.pt_no = p.pt_no
     join doctor d on a.mddr_id = d.dr_id
where a.mcdp_cd = 'CS' and date(a.apnt_ymd) = '2022-04-13' and a.apnt_cncl_yn = 'N'
order by a.apnt_ymd asc

중요

테이블 3개 조인하는 방법은 걍 join 붙여서 이어서 하면 됨!

대신 중심이 될 공통 테이블 (여기서는 appointment a) 설정 중요

컬럼 apnt_ymd는 TIMESTAMP 타입이기 때문에, 날짜 연-월-일 부분만 추출해주는 DATE( ) 를 쓰면 'YYYY-MM-DD'만 남김

 

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

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

 

59-1. 서브쿼리 + CASE WHEN(2)

select car_id,
    case
        when sum(case when '2022-10-16' between start_date and end_date then 1 else 0 end) > 0 then '대여중'
        else '대여 가능' end as availabity
from car_rental_company_rental_history
group by car_id
order by car_id desc

중요

'2022-10-16'이 start_date와 end_date 사이에 들어간다면 1, 아니면 0을 추출하는 CASE WHEN 1을 만들고,CASE WHEN 1의 값들을 SUM(합계) = 한 자동차가 한 번이라도 저 날짜에 대여중이면 합계가 1이상 = '대여중'

728x90

59-2. CASE WHEN(1) + MAX사용

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

중요

각 자동차 대여기록마다 '2022-10-16'이 start_date와 end_date 사이에 포함되면 1(참), 아니면 0(거짓)으로 계산됨

MAX(~~~) 는 그룹내에서 제일 큰 값을 반환. 즉 힌 번이라도 1이 있으면 1(참)이 됨 = '대여중'

 

60. 년, 월, 성별 별 상품 구매 회원 수 구하기

 

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

select year(o.sales_date) as year,
       month(o.sales_date) as month,
       u.gender,
       count(distinct u.user_id) as users
from user_info u join online_sale o on u.user_id = o.user_id
where u.gender is not null
group by 1, 2, 3
order by 1 asc, 2 asc, 3 asc

중요

1) 판매일의 연도와 월을 추출하여 각각 YEAR, MOTH 컬럼으로 만듦

2) ★ count(distinct o.user_id) as users   

문제에서는 회원 수를 구하라고 함.    

근데 distinct를 안 쓸 경우, 같은 회원이 한 달에 여러 번 구매하면 여러 명인 것으로 집계됨!   

따라서, 중복 없이 구매한 회원 수를 카운트하기 위해 DISTINCT필요

3) 두 테이블 join

4) 조건 : 성별 정보 없는 회원 제외

5) 년, 월, 일로 집계6) 오름차순 정렬