[Mysql] SQL 문법 연습 풀이/order by, group by +업데이트

🥑 학습내용

** 240906 업데이트) 오답 수정 및 대체 가능 코드 추가

 

 

7) 랭크게임 하다가 싸워서 피드백 남겼어요…

아래와 같은 lol_feedbacks (LOL 피드백 테이블)이 있습니다.

25

lol_feedbacks 테이블에서 만족도 점수(satisfaction_score)에 따라 피드백을 내림차순으로 정렬하는 쿼리를 작성해주세요!

 

select *

from lol_feedbacks

order by satisfation_score desc

 

26

lol_feedbacks 테이블에서 각 유저별로 최신 피드백을 찾는 쿼리를 작성해주세요!

 

select user_name, max(feedback_date) "최신피드백"

from lol_feedbacks

group by user_name

 

27

lol_feedbacks 테이블에서 각 유저별로 최신 피드백을 찾는 쿼리를 작성해주세요!

 

select count(1) cnt_ss

from lol_feedbacks

where satisfaction_score='5'

 

28

lol_feedbacks 테이블에서 가장 많은 피드백을 남긴 상위 3명의 고객을 찾는 쿼리를 작성해주세요!

select user_name

from lol_feedbacks

group by user_name

order by count(user_name) DESC

limit 3

SELECT user_name, COUNT(*) AS feedback_count 
FROM lol_feedbacks 
GROUP BY user_name 
ORDER BY feedback_count DESC 
LIMIT 3;

** 위와 같이 작성하면 조금 더 정리된 느낌을 준다.

 

29

lol_feedbacks 테이블에서 평균 만족도 점수가 가장 높은 날짜를 찾는 쿼리를 작성해주세요!

 

select avg(satisfaction_score) avg_sc,

feedback_date

from lol_feedbacks

group by feedback_date

order by avg(satisfaction_score) desc

limit 1

 

 

 

 

 8) LOL을 하다가 홧병이 나서 병원을 찾아왔습니다.

이제, 아래와 같은 doctors(의사) 테이블이 있습니다.

 

30

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

 

SELECT name, major

from doctors

where major='성형외과'

 

31

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

 

SELECT major, count(1) cnt_maj

from doctors

group by major

 

32

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

 

SELECT count(1) num_doc

from doctors

where hire_date<=date_sub(now(),Interval 5 year)

**  now() 대신 curdate()를 사용해도 된다.

 

 

33

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

 

select name, datediff(now(),hire_date) "근무기간"

from doctors

*group by name (*팀원에게 피드백 받아서 수정)

** now() 대신 curdate() 를 사용해도 된다.

 

 

 

 

9) 아프면 안됩니다! 항상 건강 챙기세요!

의사가 있으면 당연히 의사에게 진료받는 환자가 있겠죠? 아래와 같은 patients(환자) 테이블이 있습니다.

 

34

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

 

SELECT gender, count(1) cnt_gender

from patients

group by gender

 

35

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

 

select count(1)

from

(

select date_format(now(),'%Y') - date_format(birth_date,'%Y') as age

from patients

)

where age>=40

select count(1)
from patients
where birth_date<=date_sub(curdate(),interval 40 year)

 * 팀원분이 이렇게도 풀 수 있다고 알려주셨다!

** 이렇게 작성하는 것이 더 간편해서 보기 좋다.

 

나는 date_format 함수를 사용하여 현재now와 birth_date의 네자리(0000)의 연도값을 구한 뒤 빼준 것을 나이로 지정하고 where절을 이용해 40세 이상만 표시되도록 하였다.

팀원분은 date_sub 함수를 사용하여  현재curdate에 40년을 뺀것이 birth_date보다 크거나 같은 경우를 40세 이상이라고 지정하셨다.

 

 

36 

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

 

SELECT *,

datediff(day,last_visit_Date,now()) "방문경과일"

from patients

where datediff(day,last_visit_Date,now()) "방문경과일">=365

*이 문제는 도저히 접근 방법을 모르겠어서 거의 포기했다. 아마 date_sub 함수를 사용하는 데 익숙하지 않아서 생각을 못 했던 것 같다.

 

SELECT id, name, birth_date, gender, last_visit_date 
FROM patients 
WHERE last_visit_date <= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

** 위와 같이 DATE_SUB를 사용하여 풀 수 있다.

 

 

37

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

select count(1)

from patients

where birth_date like '198%'

** 위 답도 틀린 것은 아니지만 WHERE birth_date BETWEEN '1980-01-01' AND '1989-12-31' 이 더 정확한 값을 출력할 수 있다.

 

---

혼자만 공부하면 놓칠 수도 있는 부분을 팀원을과 공유하면서 같은 값을 여러 방법으로 풀어볼 수 있어서 좋았따. 36번은 다시 생각해도 이해가 잘 안되어서 날짜와 시간함수를 조금 더 공부해보고 다시 풀이할 생각이다ㅠㅠ