[MySQL][Practice] 아프면 안됩니다! 항상 건강 챙기세요!

Table - patients

id	name	birth_date	gender	last_visit_date
1	르탄이	1985-04-12	남자	2023-03-15
2	배캠이	1990-08-05	여자	2023-03-20
3	구구이	1982-12-02	여자	2023-02-18
4	이션이	1999-03-02	남자	2023-03-17

---

 

34.patients 테이블에서 각 성별(gender)에 따른 환자 수를 계산하는 쿼리를 작성해주세요!

select gender,
       count(*)
from patients
group by gender

35.patients 테이블에서 현재 나이가 40세 이상인 환자들의 수를 계산하는 쿼리를 작성해주세요!

select count(*) as cnt_patients_40over
from(
select *,
       date_format(now(), '%Y') - date_format(birth_date, '%Y') as age
from patients
)a
where a.age>=40

36.patients 테이블에서 마지막 방문 날짜(last_visit_date)가 1년 이상 된 환자들을 선택하는 쿼리를 작성해주세요!

select *
from (
select *, 
       date(now()) as currentdate,
       datediff(date(now()),last_visit_date) as over_days
from patients
)a
where a.over_days >= 365

37.patients 테이블에서 생년월일이 1980년대인 환자들의 수를 계산하는 쿼리를 작성해주세요!

select count(*) as patients_1980
from patients
where date_format(birth_date, '%Y') between 1980 and 1989

---