SQL 3주차

3주차

JOIN

• 두 테이블을 연결해서 한 테이블처럼 보는 것
• 두 테이블에 공통된 정보(key 값)를 기준으로 연결 할 수 있음

Left Join

select * from users u
left join point_users pu on u.user_id = pu.user_id

• A테이블에 B테이블을 붙여라
• 비어있는 필드(null 값)가 있는 데이터가 있음
• user_id를 key 값으로 사용

Inner Join

select * from users u
inner join point_users pu on u.user_id = pu.user_id;

• A테이블과 B테이블의 교집합
• 비어있는 필드가 있는 데이터가 없음
• user_id를 key 값으로 사용
• user_id를 두 테이블에서 모두 가지고 있는 데이터만 출력

+ 쿼리가 실행되는 순서: from -> join -> select

 

 

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해서 데이터를 깔끔하게 볼 수 있음

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

• order 테이블에 users 테이블 연결해서 네이버 이메일을 사용하는 유저의 성씨별 주문건수 세어보기

+ 쿼리가 실행되는 순서: from -> join -> where -> group by -> select

 

 

퀴즈 1 - 결제 수단 별 유저 포인트의 평균값 구하기

select o.payment_method, round(avg(pu.point),0) as avg_point from point_users pu
inner join orders o on pu.user_id = o.user_id
group by o.payment_method

 

퀴즈 2 - 결제하고 시작하지 않은 유저들을 성씨별로 세어보기

select u.name, count(*) as cnt_name 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 cnt_name desc

 

퀴즈 3 - 과목별로 시작하지 않은 유저들 세어보기

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

 

퀴즈 4 - 웹개발, 앱개발 종합반의 week 별 체크인 수 세어보기

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

 

퀴즈 5 - 퀴즈 4에서 8월 1일 이후에 구매한 고객들만 뽑아내기

select c.title, c2.week, count(*) as cnt from courses c
inner join checkins c2 on c.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 c2.week, c.title
order by c.title, c2.week

• where절에서 날짜도 크기 비교 가능

퀴즈 6 - 7월 10일 ~ 7월 19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 전체 숫자, 비율 뽑아내기

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'

• between A and B : B는 포함 안됨

 

UNION

• select를 두 번 하지 않고 한 번에 모아서 보고 싶은 경우 이어서 볼 수 있도록 함
• 필드명이 같아야 함
• order by가 작동하지 않음

(
	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
)

 

3주차 숙제

enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기. user_id도 같이 출력되어야 한다.

select e.enrolled_id, e.user_id, count(e.enrolled_id) 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, e.user_id
order by max_count desc