SQL 복습

1~5주차 연습문제 풀면서 복습

1~4주차는 쉬움.

5주차에 좀 틀렸음.

 

틀린거 : 

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

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

group by course_id

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

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

group by course_id

3. course_id별 checkin개수에 전체 인원을 붙이기                        <<<<<<<<<<<<                      이거 틀림

select a.course_id, b.cnt_checkins, a.cnt_total from

(

select course_id, count(*) as cnt_total from orders

group by course_id

) a

inner join (

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

group by course_id

) b

on a.course_id = b.course_id

4. with절로 묶는 연습까지~

with

table1 as

(

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

group by course_id

),

table2 as

(

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

group by course_id

)

select c2.title,

a.course_id,

b.cnt_checkins,

a.cnt_total,

(b.cnt_checkins/a.cnt_total) as ratio

from

table1 a

inner join

table2 b

on a.course_id = b.course_id

inner join

courses c2 on a.course_id = c2.course_id

5. 이메일에서 아이디만 가져와보기                                      <<<<<<<<<<<<                      이거 틀림

select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users

@를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!