코딩테스트 연습(Level.1) / MySQL

코드 입력

SELECT floor(avg(DAILY_FEE)) as AVERAGE_FEE from CAR_RENTAL_COMPANY_CAR
where car_type like "SUV"

코드 입력

SELECT BOOK_ID, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE FROM BOOK
where category = "인문" and YEAR(PUBLISHED_DATE) = "2021%"
order by PUBLISHED_DATE

코드 입력

SELECT DR_NAME,DR_ID,MCDP_CD,DATE_FORMAT(HIRE_YMD,'%Y-%m-%d') as HIRE_YMD From DOCTOR
where MCDP_CD in("CS","GS")
order by HIRE_YMD desc, DR_NAME asc

코드 입력 inner join으로 묶어서 풀이하자.

SELECT a.FLAVOR FROM FIRST_HALF as a
JOIN ICECREAM_INFO as b ON a.FLAVOR = b.FLAVOR
WHERE TOTAL_ORDER > 3000 and b.INGREDIENT_TYPE = "fruit_based"
ORDER BY TOTAL_ORDER DESC

코드 입력 DATEDIFF 함수 : 두개의 날짜값의 차이를 int로 반환

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

`