내일배움캠프 사전캠프 TIL/3일차 SQL 걷기반 완료

오늘의 목표

 걷기반을 마무리하고 자바로 들어가는게 목표!

SQL 걷기반 7 - 마무리 까지

 

> 걷기반 7단계

 

25. select * from lol_feedbacks order by satisfaction_score desc;

26. select user_name,max(feedback_date) from lol_feedbacks group by user_name;

27. select count(*) from lol_feedbacks group by satisfaction_score having satisfaction_score = 5;

28. select user_name, count(*) cnt from lol_feedbacks group by user_name order by cnt desc limit 3;

29. select max(a.avg) from (select avg(satisfaction_score) avg ,feedback_date from lol_feedbacks group by feedback_date)a;

 

 

 

> 걷기반 8단계

 

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

31. select major ,count(*) from doctors group by major;

32. select * from doctors where TIMESTAMPDIFF(year,hire_date ,now()) >= 5;

33. select datediff(now(),hire_date) from doctors;

 

 

> 걷기반 9단계

34. select count(*) from patients group by gender;

35. select count(*) from patients where timestampdiff(year,birth_date,now()) >= 40;

36. select name,last_visit_date from patients where timestampdiff(year,last_visit_date,now()) >= 1;

37. select count(*) from (select date_format(birth_date,'%y') year from patients) a where year between 80 and 90;

 

 

> 걷기반 10단계

 

38. select count(*) from departments;

39. select e.id,e.name,d.name from departments d,employees e where d.id = e.department_id;

40. select e.id,e.name,d.name from departments d,employees e where d.id = e.department_id and d.name = '기술팀';

41. select department_id,count(*) from employees group by department_id;

42. select * from departments where id not in(select department_id from employees group by department_id);

43. select * from employees where department_id = 101 and name in(select name from employees group by name having count(*) = 1);

 

 

> 걷기반 마무리 단계

 

 

44. select o.id,p.name from products2 p,orders2 o where p.id = o.product_id;

45. select o.product_id as id , sum(o.quantity * p.price) as 총매출 from orders2 o , products2 p 
where p.id = o.product_id  group by o.product_id order by 총매출 desc limit 1; 

46. select id, sum(quantity) from orders2 group by id;

47. select o.id,p.name from products2 p,orders2 o where p.id = o.product_id and o.order_date > '2023-03-03';

48. select p.name, sum(o.quantity)as sum from orders2 o,products2 p where p.id = o.product_id group by o.product_id order by sum desc limit 1;

49. select id, avg(quantity) from orders2 group by id;

50. select p.name,p.id from orders2 o,products2 p where p.id not in(select product_id from orders2 group by product_id) group by p.id;

 

45번에서 order by 와 limit를 쓰지않고 해결하고 싶어서 고민을 해봤는데 문제점이 있었다

 

1.집계함수와 id 같이 출력하기

 

  두 개를 같이 출력하기 위해선 group by로 묶어줘야 했는데 그렇면 결국 order by 와 limit를 사용해야 했다..

  이럴 경우 집계 함수를 where이나 having 조건문에서 사용하면 된다.

 

2. SQL Error [1111] [HY000]: Invalid use of group function

 

이 오류는 GROUP 함수를 잘못 사용했을 경우에 발생하는데 groupy로 묶었는데 where에서 집계 함수를 조건으로 사용하면 나는 오류다 즉 having 절에서 집계 함수를 사용해야 한다.

 

최종적으로 아래 같이 해결했다(더 길어진건 비밀..)

select o.product_id as id , max(o.quantity * p.price) as

총매출 from orders2 o , products2 p where p.id = o.product_id group by o.product_id having max(o.quantity * p.price) = (select max(o.quantity * p.price) from orders2 o , products2 p where p.id = o.product_id);

 

마무리 하며

   생각보다 술술 풀리지 않고 뭔가 하드하게 짠거 같아서 아쉽지만 나름 재미있었고 다시 조금씩 성장하는게 느껴져서
   좋은거 같다!

 

 

* 혼자 공부하며 작성한 글이라 오류가 있을 수 있으니 피드백 환영합니다..