[걷기반] 8) LOL을 하다가 홧병이 나서 병원을 찾아왔습니다.

create table doctors  (
	id int not null auto_increment,
    name varchar(20) not null,
    major varchar(20) not null,
    hire_date datetime,
    constraint lol_users_PK primary key(id)
);

insert into doctors (name, major, hire_date) values ("르탄이", "피부과", "2018-05-10");
insert into doctors (name, major, hire_date) values ("배캠이", "성형외과", "2019-06-15");
insert into doctors (name, major, hire_date) values ("구구이", "안과", "2020-07-20");

이제, 아래와 같은 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(*) as doctors_count
from doctors
group by major;

 

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

select count(*)
from doctors
where TIMESTAMPDIFF(YEAR, hire_date, curdate()) >= 5;

 

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

select name, major, datediff(curdate(), hire_date) as working_days
from doctors;