✍️ 스파르타코딩클럽 SQL (4주차)

✏️ 혼자 연습하려고 기록하는 블로깅.. ^^

🐼 몰랐던 부분 & 헷갈린 부분만 정리

• Subquery 쿼리 안의 쿼리! (더 편하고 간단하게 원하는 데이터를 얻을 수 있씀!)
  1) where 절 안에 query문 넣기

select u.user_id, u.name, u.email from users u 
inner join orders o on u.user_id = o.user_id 
where o.payment_method = 'kakaopay'

-> 이 코드를 subquery를 활용해서 동일하게 만들어보장 !

select user_id, name, email from users u 
where user_id in (
	select user_id from orders o 
	where payment_method = 'kakaopay'
)
# 큰 쿼리문 안에 들어가는 작은 쿼리문.
# 가장 안에 들어가있는 쿼리문부터 시작해서 결과를 만들고 그 다음 밖에 있는걸 실행함
# in 안에 들어있는 쿼리문에 해당하는 user_id만 걸러낼거야~~~

2) select문 안에 query문 넣기

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
# c.user_id와 일치하는 것들의 평균값들을 다 추출해줘~~

3) from절 안에 query문 넣기

select pu.user_id, pu.point, a.avg_likes 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
# 내가 지금 만든 select문을 마치 원래부터 있었던 테이블로 사용하는게 from 절 안에 있는 subquery

4) Subquery 연습(where, select)
🌱 퀴즈1

select * from point_users pu 
where point > (
	select avg(point) from point_users pu 
)

🌱 퀴즈2

#1
select * from point_users pu 
where point > (
	select avg(point) from point_users pu
	inner join users u2 on pu.user_id = u2.user_id
	where name = '이**'
)

#2
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 = '이**'
	)
)
# subquery안에 subquery~~~

🌱 퀴즈3

-> 내답안

select checkin_id, 
	   course_id, 
	   user_id, 
	   likes, (
		select avg(likes) from checkins c
		group by course_id
				) 
		as course_avg from checkins c

-> 정답

select c.checkin_id, 
	   c.course_id, 
	   c.user_id, 
	   c.likes, (
		select avg(likes) from checkins c
		where course_id = c.course_id
				) 
		as course_avg from checkins c

• 난 course_id별 평균 likes값을 구할 때 나는 group by를 써주었는데 샘은 where절로 course_id = c.course_id를 써주었다. 뭐가 다른걸까? ... 고민!

5) Subquery 연습(from, inner join)
🌱 퀴즈1

select c.course_id, COUNT(DISTINCT(user_id)) as cnt_checkins from checkins c
group by course_id
# count안에 distinct넣기!! 주의!!

🌱 퀴즈2

select course_id, count(user_id) as cnt_total from orders o 
group by course_id

🌱 퀴즈3

-> 내 답안

select c.course_id, 
	   COUNT(DISTINCT(c.user_id)) as cnt_checkins,
	   count(*) as cnt_total
	   from checkins c
inner join orders o on c.user_id = o.user_id 
group by c.course_id

# checkins테이블과 orders 테이블을 inner join 해주었는데
# count 세어줄 때 cnt_total이 안먹힌다. 왜냐면 이건 orders 테이블에서 가져온거라
# select 구문을 읽어줄 때 뒤에 inner join에 나오는 orders를 못읽는 것 같다
# 어떻게 해야나올까??

-> 샘 답안

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 * from () a
# inner join () b on 조건
# 저 괄호 안에 각각의 select문을 넣어주는 것... 아 너무 신기한데 너무 헷갈령..ㅠㅠ
🦖

🌱 퀴즈4

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 c
group by course_id) a
inner join (select course_id, count(*) as cnt_total from orders o
group by course_id) b on a.course_id = b.course_id

🌱 퀴즈5

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 c
	   group by course_id) a
inner join (select course_id, count(*) as cnt_total 
	   from orders o
	   group by course_id) b 
	   on a.course_id = b.course_id
inner join (select * from courses) c 
	   on c.course_id = b.course_id
       
# inner join 2개 들어갈 떄 그냥 밑에다가 써주면 될듯 ~!

• with 절 연습!!

with table1 as (
	select course_id, count(DISTINCT(user_id)) as cnt_checkins from checkins c
	group by course_id
),	 table2 as (
	select course_id, count(*) as cnt_total from orders o
	group by course_id
)
# table1과 table2가 임시테이블처럼 활용됨. alias로 select 구문을 넣어주는 것임 !

-> with 절을 사용해서 좀더 가독성을 높여보장 ! 요러케~~

with table1 as (
	select course_id, count(DISTINCT(user_id)) as cnt_checkins from checkins c
	group by course_id
),	 table2 as (
	select course_id, count(*) as cnt_total from orders o
	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 (select * from courses) c 
	   on c.course_id = b.course_id

• 실전에서 유용한 SQL 문법!! (문자열)
• 문자열 쪼개보기
  -> SUBSTRING_INDEX 라는 문법을 사용해보는 것이야 ! 🌈

SUBSTRING_INDEX(email,'@',1)
# email을 '@'기준으로 쪼갤껀데 첫번째 것만 보여줘~!
# -1 하면? 마지막꺼만 보여줘~!

• 문자열 일부만 출력하기
  -> SUBSTRING 이라는 문법을 사용 !! 🌈

substring(created_at,1,10)
# 시작포인트, 시작포인트부터 몇자 자를건지!

• 실전에서 유용한 SQL 문법!! (Case)

case when pu.point > 10000 then '잘하고 있어요!'
	else '조금만 더 화이팅!' end
    
# 포인트가 10000보다 크면 잘하고 있어요 또는, 그게 아니면 조금만 더 화이팅하고 끝!
# select구문에 들어가는 걸 기억해주...!

with table1 as (
	select pu.user_id, pu.point,
		(case when pu.point > 10000 then '1만 이상'
		  	  when pu.point > 5000 then '5천 이상'
	          else '5천미만' end) as lv
		from point_users pu
		)


select a.lv, count(*) as cnt FROM table1 a
group by a.lv

# 요건 with 구문까지 합쳐진 case when 문!

📌 과제1(초급)

내풀이)
select pu.point_user_id, pu.point, 
	(case when pu.point >= avg(pu.point) then '잘 하고 있어요'
	 	else '열심히 합시다!' end) as msg
	 from point_users pu

-> 왜 데이터가 하나밖에 안나올까...?ㅎ....

샘풀이)
select pu.point_user_id, pu.point,
case 
when pu.point > (select avg(pu2.point) from point_users pu2) then '잘 하고 있어요!'
else '열심히 합시다!'
end as 'msg'
from point_users pu

# avg(point) 부분에 select 서브쿼리를 넣어줬네...?
# avg(point)가 들어갈 자리에 저렇게도 넣어줄 수 있다는게 넘 신기.. 저렇게 넣으니까 쫘르르 다 나옴! 😇

📌 과제2(초급)

내풀이)
select SUBSTRING_INDEX(email,'@',-1) as domain, count(*) from users u
group by DOMAIN 

샘풀이)
select domain, count(*) as cnt from (
select SUBSTRING_INDEX(email,'@',-1) as domain, count(*) from users u
) a
group by DOMAIN

# 샘은 from 절에 subquery를 넣어주었는데 답은 둘다 같다. 뭐가 다른걸까? 

select * from checkins c 
where comment like '%화이팅%'

내답안)
select a.enrolled_id, a.done_cnt, b.total_cnt from (
	select enrolled_id, count(*) as done_cnt from enrolleds_detail
	where done = 1
	group by enrolled_id
) a
inner join(
	select enrolled_id, count(*) as total_cnt from enrolleds_detail
	group by enrolled_id
) b on a.enrolled_id = b.enrolled_id

샘답안)
with table1 as (
	select enrolled_id, count(*) as done_cnt from enrolleds_detail
	where done = 1
	group by enrolled_id
), table2 as (select enrolled_id, count(*) as total_cnt from enrolleds_detail
	group by enrolled_id
)
select a.enrolled_id, a.done_cnt, b.total_cnt from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id

with table1 as (
	select enrolled_id, count(*) as done_cnt from enrolleds_detail
	where done = 1
	group by enrolled_id
), table2 as (select enrolled_id, count(*) as total_cnt from enrolleds_detail
	group by enrolled_id
)
select a.enrolled_id, a.done_cnt, b.total_cnt, round(a.done_cnt/b.total_cnt,2) as ratio, 2 from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id

# 주의할 것: round사용할 때 전체 블록잡고 ctrl+enter 하기

select enrolled_id, 
	   sum(done) as cnt_done, 
	   count(*) as cnt_total
from enrolleds_detail ed
group by enrolled_id

#sum을 활용해서 done=1을 셀 수 있음! 간단하게!