Subquery, with절 연습

Where 절에 들어가는 Subquery 연습해보기

 

[연습] 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

ex)

SELECT * from point_users pu

where point > (

select avg(point) from point_users pu

)

[연습] 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

ex)

select * from point_user pu

where point > (

SELECT avg(pu.point) from users u

inner join point_users pu on u.user_id = pu.user_id

where u.name = '이**'

)

 

select * from point_users pu

where point > (

select avg(point) from point_users pu

where user_id in (

select user_id from users where name = '이**'

)

)

 

Select 절에 들어가는 Subquery 연습해보기

 

checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

ex)

select c.checkin_id, c.course_id, c.user_id, c.likes,

(

select avg(likes) from checkins c

where course_id = c.course_id

) as course_avg

from checkins c

 

checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

ex)

select c.checkin_id, c2.title, c.user_id, c.likes,

(

select round(avg(likes),1) from checkins c

where course_id = c.course_id

) as course_avg

from checkins c

inner join courses c2 on c.course_id = c2.course_id

 

From 절에 들어가는 Subquery 연습해보기

 

course_id별 유저의 체크인 개수를 구해보기!

ex)

select course_id, count(distinct(user_id)) as cnt_checkins from checkins c

group by course_id

 

course_id별 checkin개수에 전체 인원을 붙이기

ex)

select a.course_id, a.cnt_checkins, b.cnt_total from

(

select course_id, count(distinct(user_id)) as cnt_checkins from checkins c

group by course_id

) a

inner JOIN

(

select course_id, count(*) as cnt_total from orders o

group by course_id

) b on a.course_id = b.course_id

 

퍼센트를 나타내기

ex)

select a.course_id, a.cnt_checkins, b.cnt_total, (a.cnt_checkins/b.cnt_total) AS ratio from

(

select course_id, count(distinct(user_id)) as cnt_checkins from checkins c

group by course_id

) a

inner JOIN

(

select course_id, count(*) as cnt_total from orders o

group by course_id

) b on a.course_id = b.course_id

 

강의 제목도 나타나면 좋겠네요!

ex)

select c.title, a.cnt_checkins, b.cnt_total, (a.cnt_checkins/b.cnt_total) as ratio from

(

select course_id, count(distinct(user_id)) as cnt_checkins from checkins c

group by course_id

) a

inner JOIN

(

select course_id, count(*) as cnt_total from orders o

group by course_id

) b on a.course_id = b.course_id

INNER JOIN courses c ON a.course_id = c.course_id

 

with절

ex)

with table1 as(

select course_id, count(distinct(user_id)) as cnt_checkins from checkins c

group by course_id

), table2 as(

select course_id, count(*) as cnt_total from orders o

group by course_id

)

select c.title,

a.cnt_checkins,

b.cnt_total,

(a.cnt_checkins/b.cnt_total) as ratio

from table1 a

inner JOIN table2 b on a.course_id = b.course_id

INNER JOIN courses c ON a.course_id = c.course_id