SQL_4주차_복습

1) 초급

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

select pu.point_user_id ,

          pu.point,

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

                   else '열심히 합시다!' end) as msg

from point_users pu

✅ 이메일 도메인별 유저의 수 세어보기

# 나열해서 추출하기

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

group by domain

 

# Subquery 사용해서 추출하기

select domain, count(*)as cnt_domain from(

           select SUBSTRING_INDEX(email,'@',-1) as domain from users   # from절의 테이블처럼 사용함

)a

group by domain

✅ '화이팅'이 포함된 오늘의 다짐만 출력해보기

select * from checkins

where comment like '%화이팅%'

 

2)중급

✅ 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기

[순서1] 각 조건에 맞는 select 구문 완성

select enrolled_id ,COUNT(*) as done_cnt from enrolleds_detail

where done = 1

group by enrolled_id

 

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

group by enrolled_id

 

 

[순서2] subquery로 연결하기

select * 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 ed

          group by enrolled_id

)b on a.enrolled_id = b.enrolled_id

 

[순서3] enrolled_id, done_cnt, total_cnt 필드만 추출하기

select a.enrolled_id, a.done_cnt, b.total_cnt 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 ed

          group by enrolled_id

)b on a.enrolled_id = b.enrolled_id

[순서4] with절로 간단하게 정리하기

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 ed

          group by enrolled_id

)

select a.enrolled_id,

           a.done_cnt,

           b.total_cnt

      from table1 a

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

 

 

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

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 ed

          group by enrolled_id

)

select a.enrolled_id,

           a.done_cnt,

           b.total_cnt,

           round((a.done_cnt /  b.total_cnt),2) as ratio

      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 ratio

from enrolleds_detail ed

group by enrolled_id