1) Subquery 연습하기
✅ Where 절에 들어가는 Subquery 연습해보기
(1) 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
select * from point_users pu
where point > (
select round(avg(point)) from point_users pu
)
(2) 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
# join으로 해결
select * from point_users pu
where point > (
select round(avg(point)) from point_users pu
inner join users u on u.user_id =pu.user_id
where u. name ='이**'
)
# Subquery로 해결
select * from point_users pu
where point > (
select round(avg(point)) from point_users pu
where user_id in(
select user_id from users u where name ='이**'
)
)
✅ Select 절에 들어가는 Subquery 연습해보기
(1) checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
select c.checkin_id ,c.course_id ,c.user_id ,c.likes,
( # 추가되는 우측필드의 내용과 이름에 관한 식
select avg(likes) from checkins
where course_id = c.course_id
) as course_avg
from checkins c
(2) checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기
select c.checkin_id ,c.course_id ,c.user_id ,c.likes,
(
select avg(likes) from checkins
where course_id = c.course_id
) as course_avg,
c2.* # c2의 모든것을 보여줘라
from checkins c
↓ title을 제외하고 잘 들어갔는지 확인 후 c.course_id 자리에 c2. title 넣어주기
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 c2.course_id = c.course_id
order by course_avg
✅ From 절에 들어가는 Subquery 연습해보기
[준비1] course_id별 유저의 체크인 개수를 구해보기
# distinct로 user_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
[진짜 하고 싶은 것] course_id별 like 개수에 전체 인원을 붙이기
select a.course_id, a.cnt_checkins, b.cnt_total 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
[한 걸음 더] 퍼센트를 나타내기
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
[반 걸음 더] 강의 제목도 나타내기
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 c.course_id = a.course_id
-> 결국 위의 식은 select에 inner절을 2번 넣은 것과 같음
2) with절 연습하기
✅ with 절로 더 깔끔하게 쿼리문을 정리하기
# from 안에 있는 Subquery 사용시 with 절을 사용하면 깔끔하게 쿼리 작성 가능
with table1 as(
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
), table2 as (
select course_id, count(*) as cnt_total from orders
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 c.course_id = a.course_id
'스파르타 > SQL' 카테고리의 다른 글
| SQL_4주차_복습 (0) | 2023.01.16 |
|---|---|
| SQL_4주차_실전에서 유용한 SQL 문법 (0) | 2023.01.16 |
| SQL_4주차_Subquery (1) | 2023.01.13 |
| SQL_3주차_숙제 (0) | 2023.01.12 |
| SQL_3주차_ Union (0) | 2023.01.12 |