🌎 2022.02.23 updated

Level 1️⃣

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

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

select *
from car_rental_company_car
where options regexp '네비게이션'
order by car_id desc

자동차 대여 기록에서 장기/단기 대여 구분하기

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

select history_id, car_id, date_format(start_date, '%Y-%m-%d') as start_date, date_format(end_date, '%Y-%m-%d') as end_date, if(datediff(end_date, start_date)+1>=30, '장기 대여', '단기 대여') as rent_type
from car_rental_company_rental_history
where start_date like '2022-09%'
order by history_id desc

두 날짜 간의 차이를 구할 때는 DATEDIFF 함수를 사용한다.
DATEDIFF(날짜 1, 날짜 2) = 날짜 1 - 날짜 2

• 대여 시작일도 대여 기간에 포함되므로 datediff로 날짜 차이를 구한 뒤 +1을 해주어야 한다
  • ex) 대여 시작일이 9월 1일이고 대여 종료일이 9월 1일이면, DATEDIFF의 결과는 0이지만 대여 기간은 1일이다

Level 2️⃣

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

-- CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION) 리스트를 출력하는 SQL문을 작성해주세요. 
-- 평균 대여 기간은 소수점 두번째 자리에서 반올림하고, 결과는 평균 대여 기간을 기준으로 내림차순 정렬해주시고, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬해주세요.

select car_id, round(avg(datediff(end_date, start_date)+1), 1) as average_duration
from car_rental_company_rental_history
group by car_id
having average_duration >= 7
order by average_duration desc, car_id desc

루시와 엘라 찾기

-- 동물 보호소에 들어온 동물 중 이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의 아이디와 이름, 성별 및 중성화 여부를 조회하는 SQL 문을 작성해주세요.
select animal_id, name, sex_upon_intake
from animal_ins
where name in ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
order by animal_id

이름에 el이 들어가는 동물 찾기

-- 보호소에 돌아가신 할머니가 기르던 개를 찾는 사람이 찾아왔습니다. 
-- 이 사람이 말하길 할머니가 기르던 개는 이름에 'el'이 들어간다고 합니다. 
-- 동물 보호소에 들어온 동물 이름 중, 이름에 "EL"이 들어가는 개의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 
-- 이때 결과는 이름 순으로 조회해주세요. 
-- 단, 이름의 대소문자는 구분하지 않습니다.

select animal_id, name
from animal_ins
where name regexp 'el|EL|eL|El'
and animal_type='Dog'
order by name

중성화 여부 파악하기

-- 보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다.
-- 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다.
-- 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시해주세요.

select animal_id, name, if(sex_upon_intake regexp 'Neutered|Spayed', 'O', 'X') as 중성화
from animal_ins

카테고리 별 상품 개수 구하기

-- PRODUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 
-- 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요.

select substr(product_code, 1, 2) as category, count(product_id) as products
from product
group by category
order by category

문자열을 자를 때는 SUBSTR 함수를 사용한다

• SUBSTR(str, a, b): str라는 문자열을 a번째부터 b글자를 추출한다.
  • 인덱스는 1부터 시작한다

DATETIME에서 DATE로 형 변환

-- ANIMAL_INS 테이블에 등록된 모든 레코드에 대해, 각 동물의 아이디와 이름, 들어온 날짜를 조회하는 SQL문을 작성해주세요. 
-- 이때 결과는 아이디 순으로 조회해야 합니다.
-- 들어온 날짜: 시각(시-분-초)을 제외한 날짜(년-월-일)만 보여주세요.

select animal_id, name, date_format(datetime, '%Y-%m-%d') as 날짜
from animal_ins
order by animal_id

-- date_format 인자는 컬럼명, 포맷 순

Level 3️⃣

조건별로 분류하여 주문상태 출력하기

-- FOOD_ORDER 테이블에서 5월 1일을 기준으로 주문 ID, 제품 ID, 출고일자, 출고여부를 조회하는 SQL문을 작성해주세요. 
-- 출고여부는 5월 1일까지 출고완료로 이 후 날짜는 출고 대기로 미정이면 출고미정으로 출력해주시고, 결과는 주문 ID를 기준으로 오름차순 정렬해주세요.

select order_id, product_id, date_format(out_date, '%Y-%m-%d'),
    case
        when out_date is null
        then '출고미정'
        when out_date > '2022-05-01'
        then '출고대기'
        when out_date <= '2022-05-01'
        then '출고완료'
    end as '출고여부'
from food_order
order by order_id

대여 기록이 존재하는 자동차 리스트 구하기

-- CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 
-- 자동차 종류가 '세단'인 자동차들 중 10월에 대여를 시작한 기록이 있는 자동차 ID 리스트를 출력하는 SQL문을 작성해주세요. 
-- 자동차 ID 리스트는 중복이 없어야 하며, 자동차 ID를 기준으로 내림차순 정렬해주세요.

select distinct(h.car_id)
from car_rental_company_rental_history as h, car_rental_company_car as c
where h.car_id=c.car_id
and c.car_type='세단'
and month(h.start_date)=10
order by h.car_id desc

오랜 기간 보호한 동물(2)

-- 입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 SQL문을 작성해주세요.
-- 이때 결과는 보호 기간이 긴 순으로 조회해야 합니다.

select o.animal_id, o.name
from animal_ins as i, animal_outs as o
where i.animal_id=o.animal_id
order by (o.datetime - i.datetime) desc
limit 0,2

-- 처음에 order by에서 timediff 함수를 사용하여 틀렸다.
-- timediff는 최대 838:59:59까지 표현할 수 있기 때문에 이보다 큰 값이 나올 경우 사용할 수 없다.

Level 4️⃣

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

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

select a.apnt_no, pt_name, a.pt_no, a.mcdp_cd, dr_name, apnt_ymd
from patient as p, doctor as d, appointment as a
where p.pt_no=a.pt_no and d.dr_id=a.mddr_id
and apnt_ymd like'2022-04-13%'
and a.mcdp_cd='CS'
and apnt_cncl_yn='N'
order by apnt_ymd

자동차 대여 기록 별 대여 금액 구하기

-- CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 
-- CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 
-- 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 
-- 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

select history_id,
    if(discount_rate is null, floor(daily_fee*diff), floor((daily_fee*diff)*((100-discount_rate)/100))) as fee
from (
    select *
    from car_rental_company_car
    where car_type='트럭'
) as car
left join (
    select history_id, car_id, datediff(end_date, start_date)+1 as diff,
    case when datediff(end_date, start_date)>=90 then '90일 이상'
        when datediff(end_date, start_date)>=30 then '30일 이상'
        when datediff(end_date, start_date)>=7 then '7일 이상'
        else '7일 미만' end as duration
    from car_rental_company_rental_history
) as history on car.car_id=history.car_id
left join (
    select car_type, duration_type, discount_rate
    from car_rental_company_discount_plan
) as discount on car.car_type=discount.car_type and history.duration=discount.duration_type
order by fee desc, history_id desc