엑셀보다 쉬운 SQL 4주차 강의

 

서브쿼리(subquery)

 

where절

형식:  where 필드명 in (subquery)

SELECT  * from users u 

where user_id  in (

select user_id from orders o 

WHERE payment_method = 'kakaopay'

)

select절

형식: select 필드명, 필드명, (subquery) from

SELECT c.checkin_id,

c.user_id, 

c.likes,

(select avg(likes) from checkins

WHERE user_id = c.user_id

) as avg_likes_user

from checkins c

 

from절 (가장 많이 사용되는 유형)

SELECT pu.user_id, pu.point from point_users pu 

inner join (

SELECT user_id, ROUND(avg(likes), 1) as avg_likes from checkins c 

group by user_id 

) a on pu.user_id = a.user_id

 

성씨 조건을 걸어서 subquery 연습하기

select * from point_users pu 

WHERE pu.point > ( 

SELECT ROUND(avg(point), 1) as 이씨평균 from point_users pu 

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

where name = '이**')




select * from point_users pu 

WHERE pu.point > (

select avg(point) FROM point_users pu 

where user_id in (

SELECT user_id from users u where name = '이**')

)

 

Q. course_id별 평균 llike 수 붙이기

select ck.checkin_id,

ck.course_id,

ck.user_id,

ck.likes,

(select avg(likes) from checkins ck  where ck.course_id = c.course_id) as course_avg

from checkins ck

 

Q. 과목명별 평균 like 수 붙여보기

select ck.checkin_id,

c.title,

ck.user_id,

ck.likes,

(select round(avg(likes),1) from checkins ck  where ck.course_id = c.course_id) as course_avg

from checkins ck 

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