SQL 문제

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

ex)

select pu.point_user_id, pu.point,

case

when pu.point > (select avg(p.point) from point_users p) then '잘 하고 있어요'

else '열심히 합시다'

end as 'msg'

from point_users pu

 

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

ex)

select SUBSTRING_INDEX(email, '@', -1) as domain, count(*) as cnt_domain from users u

group by domain

 

ex)

select domain, count(*) as cnt from(

select SUBSTRING_INDEX(email, '@', -1) as domain from users u

)a

group by domain

 

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

ex)

select * from checkins c

where comment like '%화이팅%'

 

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

ex)

select * from checkins c

where c.comment like '%화이팅%'

 

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

ex)

select a.enrolled_id, a.done_cnt, b.total_cnt, (a.done_cnt/b.total_cnt) as ratio from

(

select enrolled_id, count(*) as done_cnt from enrolleds_detail

where done=1

group by enrolled_id

)a

inner join

(

select enrolled_id, count(*) as total_cnt from enrolleds_detail

group by enrolled_id

)b on a.enrolled_id = b.enrolled_id

 

**

select enrolled_id, sum(done) as done_cnt, count(*) as total__cnt from enrolleds_detail

group by enrolled_id

-> 이렇게 쓸 수도 있음

 

-> 간단히 (with)

ex)

with table1 as (

select enrolled_id, count(*) as done_cnt from enrolleds_detail

where done=1

group by enrolled_id

), table2 as (

select enrolled_id, count(*) as total_cnt from enrolleds_detail

group by enrolled_id

)

select a.enrolled_id, a.done_cnt, b.total_cnt, (a.done_cnt/b.total_cnt) as ratio

from table1 a inner join table2 b on a.enrolled_id = b.enrolled_id