SQL 3주차

Drumj·2022년 2월 11일
0

SQL 정리

목록 보기
3/5

-- 여러 테이블을 연결할때 join을 사용한다
select * from users u
left join point_users p on u.user_id = p.user_id
-- 포인트가 없는 사람은 [null]값이 나온다
-- left join 의 경우 A(위에선 users)에다가 B(위에선 point_users)를 붙이는 거라서 순서가 아주 중요하다

select * from users u
inner join point_users p on u.user_id = p.user_id
-- 포인트가 있는 것들만 나온다. 교집합 inner join을 사용하면

select * from orders o
inner join users u on o.user_id = u.user_id

select * from checkins c
inner join users u on c.user_id = u.user_id

select * from enrolleds e
inner join courses c on e.course_id = c.course_id

-- 배웠던 문법을 join과 함께 사용해보기
select c1.course_id, c2.title, count(*) as cnt from checkins c1
inner join courses c2 on c1.course_id = c2.course_id
group by c1.course_id
-- checkins 테이블에 courses 테이블 연결해서 통계치 내보기

select pu.user_id, u.name, u.email, pu.point from point_users pu
inner join users u on pu.user_id = u.user_id
order by pu.point desc
-- point_users 테이블에 users 테이블 연결해서 순서대로 정렬해보기

select u.name, count(*) as cnt from orders o
inner join users u on o.user_id = u.user_id
where o.email like '%naver.com'
group by u.name

select o.paymentmethod, round(avg(point),0) as 평균포인트 from point_users pu
inner join orders o on pu.user_id = o.user_id
group by o.payment_method

select u.name, count(*) as 누구냣 from enrolleds e
inner join users u on e.user_id = u.user_id
where e.is_registered = 0
group by u.name
order by 누구냣 desc

select c.course_id ,c.title ,count(*) as cnt_notstart from courses c
inner join enrolleds e on c.course_id = e.course_id
where e.is_registered = 0
group by c.course_id

select c.title, ck.week , count(*) as cnt from courses c
inner join checkins ck on c.course_id = ck.course_id
group by c.title, ck.week
order by c.title, ck.week

select c.title, ck.week, count(*) as cnt from courses c
inner join checkins ck on c.course_id = ck.course_id
inner join orders o on ck.user_id = o.user_id
where o.created_at >= '2020-08-01'
group by c.title, ck.week
order by c.title, ck.week

select u.name, count(*) as cnt from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
group by u.name
order by cnt desc
-- left join 활용하기
select count(pu.point_user_id) as pnt_user_cnt,
count(u.user_id) as tot_user_cnt,
round(count(pu.point_user_id)/count(u.user_id),2) as ratio
from users u
left join point_users pu on u.user_id = pu.user_id
where u.created_at BETWEEN '2020-07-10' and '2020-07-20'

(
select '7월' as month, c1.title, c2.week, count() as cnt from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
inner join orders o on c2.user_id = o.user_id
where o.created_at < '2020-08-01'
group by c1.title, c2.week
-- order by c1.title, c2.week
)
union all
(
select '8월' as month, c1.title, c2.week, count(
) as cnt from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
inner join orders o on c2.user_id = o.user_id
where o.created_at >= '2020-08-01'
group by c1.title, c2.week
-- order by c1.title, c2.week
)
-- 유니온 {안}에 있으면 order by가 먹지 않는다 고로 order by를 없애도 결과는 똑같이 나온다.
-- 유니온 밖에서 서브쿼리를 활용하면 다시 정렬이 가능해진다고 한다. 4주차에 배울것임

-- 3주차 숙제
select e.enrolled_id, e.user_id, count() as max_count from enrolleds e
inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id
where ed.done = 1
group by e.enrolled_id
order by count(
) desc

0개의 댓글