sql 걷기반 6~8번

6-1

select *,
        rank() over(order by rating desc) "순위"
from lol_users

 

6-2
select name,
        max(join_date) "게임시작"
from lol_users

6-3
select *
from lol_users
order by region, rating desc

6-4
select region,
        avg(rating) "평균레이팅"
from lol_users
group by region

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

7-1
select *
from lol_feedbacks
order by satixfactons_score desc

 

7-2
select user_name,
        max(feedback_date) "최신 피드백 날짜"
from feedback_date
group by user_name

7-3
select count(1) "5점 피드백의 수"
from  feedback_date
where satisfaction_score=5

 

7-4
select user_name,
        count(1) " 피드백 횟수"
from  feedback_date
group by user_name

 

7-5
select feedback_date,
        avg(satisfaction_score) "평균 만족도가 가장 높은 날짜"
from lol_feedbacks
group by feedbacks
order by avg(satisfaction_score) desc 
limit 1;
-------------------------------------------------------------
8-1
select name,
        major
from doctors
where major="성형외과"

8-2
select major,
        count(major) "전공 별 의사수"
from doctors
group by major

 

8-3

SELECT 
         COUNT(1) "5년이상 근무한 의사 수"
FROM 
         doctors
WHERE 
          2024 - YEAR(hire_date) >= 5;

SELECT   *,
           YEAR(CURDATE()) - YEAR(hire_date) "근무기간"
FROM 
           doctors;