SQL 4주차(2)

https://teamsparta.notion.site/SQL-4-e8025c63b6bd4e6bb27ae43da874350d

 

Subquery 연습문제

 

<where, select절에 들어가는 Subquery >

 

[연습] 1. 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
(point_users 테이블을 이용해서 avg를 구하고, 다시 point_users와 조인)

[연습] 2. 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
서브쿼리 내에서 users와 inner join

 

 

 

1. point_users 테이블에서 point가 평균보다 높은 select값을 다시 구해 빈칸에 넣음

괄호에 넣으면 아래와 같음.

 

 

2.

두개를 inner join ▼

최종적으로는 아래와 같음.

select * from point_users pu

where point > (

         select avg(point) from point_users pu

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

         where name = '이**'

)

 

 

또는 where절 안에 subquery넣을수도 있음.

 

 이씨성을 가진 유저들의 포인트. 이것을 아래와 같이 평균내고, 이것보다 큰 유저를 구하면 됨

 

 

이씨의 평균을 괄호에 넣으면 그것보다 포인트가 더 큰 유저 구해짐

 

 

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 >

 

[연습] 1. checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
[연습] 2. checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

 

들어가는 필드 : checkin_id, course_id, user_id, likes

 

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

select checkin_id,

          course_id,

          user_id,

          likes

from checkins

첫번째 select의 checkins를 c1, 두번째 select의 checkins를 c2로 하고,

랜덤 course_id 인 '5f0ae408765dae0006002817' 를 c1.course_id로 바꾸고

두번째 select를 괄호안에 넣음

그리고 *를 평균 likes를 하고, 별칭 as 붙임.

 

select c1.checkin_id,

          c1.course_id,

          c1.user_id,

          c1.likes,

          (

          select avg(likes) from checkins c2

          where c2.course_id = c1.course_id

          ) as avg_likes

from checkins c1

 

 

 

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

(과목명 : course_title, 테이블)

inner join 사용하기. inner join을 괄호중간에 하지말고 맨 마지막에 이어붙임, 중간중간 수정.

select c.checkin_id,

          c2.title,

           c.user_id,

           c.likes,

          (

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

          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 >

 

[1] course_id별 유저의 체크인 개수를 구해보기!
checkins 테이블, course_id, group by, distinct

[2] course_id별 인원을 구해보기!
orders 테이블, course_id, group by

[3] course_id별 like 개수에 전체 인원을 붙이기
1과 2를 inner join

[4] 퍼센트를 나타내기
전체 중 얼마나 like를 하는지

[5] 앗, 강의 제목도 나타나면 좋겠네요! 
courses 테이블과 조인, title 붙이기

 

 

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

체크인을 한번이라도 한 유저를 세야 하므로, 유저중복을 제거해야하므로, distinct는 user_id앞에 붙임. 유저의 체크인개수를 구하는 것이므로, checkin_id가 아닌 user_id를 구함.

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

group by course_id

 

2. course_id별 인원을 구해보기!

select course_id, count(*) as cnt_total from orders

group by course_id

 

3. course_id별 like 개수에 전체 인원을 붙이기

 

 

select * from (  )a inner join (  )b on a.course_id = b.course_id

위와 같은 형태로 만들고, 첫번째 괄호와 두번째 괄호에 각각 1, 2번 구한 값을 넣음.

위에 ①번 Inner join select course_id임, 오타

 

첫번째 괄호를 a, 두번째 괄호를 b 처리 해서 a.course_id = b.course_id 로 이어붙임.

 

구해야 할 값 구하기 빨간밑줄 3개

 

 

4. 퍼센트를 나타내기. 전체중 얼마나 like를 하는지

 

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

group by course_id)a

inner join

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

group by course_id)b

on a.course_id = b.course_id

 

 

 

5. 강의제목 붙이기 (courses 테이블과 조인, title 붙이기)

 

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

group by course_id)a

inner join

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

group by course_id

)b on a.course_id = b.course_id

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