SQL 2주차 마지막으로, 종합 퀴즈 풀기 :)

지금까지 배운 내용들을 가지고, 종합문제를 풀어보면서 내용도 정리하고 복잡한 문제 푸는 방법도 정리해보겠다~

그럼 가봅시다~~

 

 

퀴즈1) 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기

select pu.point_user_id, pu.point,
case when pu.point > (select avg(pu2.point) from point_users pu2 ) then '잘 하고 있어요!'
	else '열심히 합시다' END as comment
	from point_users pu;

이 문제에서 주의할 점..! 이렇게 하면 안돼요..

(select avg(pu2.point) from point_users pu2 ) => avg(pu2.point) 로 쓰면 안된다!!

처음에 이렇게 썼다가.. 이상한 결과값이 나와서 당황..;; 

꼭 서브쿼리를 쓰도록 하자!

 

퀴즈2) 이메일 도메인별 유저의 수 세어보기

select SUBSTRING_INDEX(email,'@',-1), count(*) from users
group by SUBSTRING_INDEX(email,'@',-1);

이건 비교적 최근에 배운 내용이라 그런지 큰 어려움은 없었다~ substring_index를 쓰면 되는 문제!

 

퀴즈3)  '화이팅'이 포함된 오늘의 다짐만 출력해보기

select c.comment from checkins c
where c.comment like '%화이팅%';

이것도 no problem!!

 

퀴즈4) 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해보기

어후.. 이 퀴즈는 좀 헤맸다..ㅠㅠ

이런 퀴즈를 풀 때는 step by step 생각해보자!

먼저, 수강등록정보별 전체 강의 수를 출력해보자!

select ed.enrolled_id , count(*) as total_cnt from enrolleds_detail ed
				group by ed.enrolled_id

그 다음으로 들은 강의 수를 출력해보자!

select ed2.enrolled_id, count(*) as done_cnt from enrolleds_detail ed2 
			  	where ed2.done=1
				group by ed2.enrolled_id

둘을 서브쿼리로 합쳐보자!

with table1 as (select ed.enrolled_id , count(*) as total_cnt from enrolleds_detail ed
				group by ed.enrolled_id),
	 table2 as(select ed2.enrolled_id, count(*) as done_cnt from enrolleds_detail ed2 
			  	where ed2.done=1
				group by ed2.enrolled_id )
select a.enrolled_id, a.total_cnt, b.done_cnt from table1 a
inner join table2 b
on a.enrolled_id=b.enrolled_id

그리고 진도율까지 구해주면, 문제 끝!

with table1 as (select ed.enrolled_id , count(*) as total_cnt from enrolleds_detail ed
				group by ed.enrolled_id),
	 table2 as(select ed2.enrolled_id, count(*) as done_cnt from enrolleds_detail ed2 
			  	where ed2.done=1
				group by ed2.enrolled_id )
select a.enrolled_id, 
	   a.total_cnt, 
	   b.done_cnt,
	   round(b.done_cnt/a.total_cnt,2) as '진도율' from table1 a
inner join table2 b
on a.enrolled_id=b.enrolled_id

 

이 퀴즈를 다음과 같은 방법으로도 해결할 수 있다~

select enrolled_id,
       sum(done) as cnt_done,
       count(*) as cnt_total,
       round(sum(done)/count(*),2) as '진도율'
from enrolleds_detail ed
group by enrolled_id

가끔은.. 서브쿼리를 사용하지 않고도 더 간단히 데이터를 정리하는 방법이 있을 수 있다..ㅎㅎ

 

여기까지~  SQL에서 저장된 데이터를 읽어오고, 정리하는 방식, Read기능에 대해 배워보았다~