[MySQL][Practice] LOL을 하다가 홧병이 나서 병원을 찾아왔습니다.

Table - doctors

id	name	major	hire_date
1	르탄이	피부과	2018-05-10
2	배캠이	성형외과	2019-06-15
3	구구이	안과	2020-07-20

---

30. doctors 테이블에서 전공(major)가 성형외과인 의사의 이름을 알아내는 쿼리를 작성해주세요!

select name
from doctors
where major = '성형외과'

31. doctors 테이블에서 각 전공 별 의사 수를 계산하는 쿼리를 작성해주세요!

select major,
       count(1) as cnt_major
from doctors
group by major

32. doctors 테이블에서 현재 날짜 기준으로 5년 이상 근무(hire_date)한 의사 수를 계산하는 쿼리를 작성해주세요!

#방법1
select count(1) as cnt_dc
from(
select major,
       curdate(),
       hire_date,
       datediff(curdate(),hire_date)/365 as period_of_hire
from doctors
)a
where period_of_hire >= 5

-------------------

#방법2
select count(1) as cnt_dc
from doctors
where hire_date <= date_sub(curdate(),interval 5 year)

33. doctors 테이블에서 각 의사의 근무 기간을 계산하는 쿼리를 작성해주세요!

select name, 
       concat(datediff(curdate(),hire_date),' days') as period_of_hire
from doctors

---