엑셀보다 쉬운 SQL 1~3주차 강의

 

limit: 일부 데이터만 가져오기

select * from orders
where payment_method = "kakaopay"
limit 5;

 

distinct: 중복데이터 제외하고 가져오기

select distinct(payment_method) from orders;

 

count: 몇 개인지 세보기

select count(*) from orders

 

Alias: 별칭 기능 

select * from orders o
where o.course_title = '앱개발 종합반'



select payment_method, count(*) as cnt from orders o
where o.course_title = '웹개발 종합반'
group by payment_method

 

join

left join: 왼쪽 테이블에 오른쪽 테이블을 붙임, 매칭 안되도 null값으로 나옴 -> 합집합

Inner join: 조건이 딱 맞는(교집합)이 있는 테이블이 출력됨

outer join

 

쿼리 실행 순서: from -> join -> where -> group by -> select

select u.name, COUNT(*)  from orders o 

inner join users u on o.user_id = u.user_id

WHERE o.email LIKE '%naver.com'

group by name

 

Q. 웹개발, 앱개발 종합반 Week 별 체크인 수 세기 -> 8월 1일 이후 구매한 고객들만 조회하기

SELECT title, 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

 

Q. 7월 10일 ~ 7월 19일 가입 고객 중 포인트를 가진 고객의 숫자 , 전체숫자, 비율 조회

select count(pu.point_user_id) as pnt_user_cnt, count(*) as tot_user_cnt, round(count(pu.point_user_id)/count(*), 2)  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'

 

 

 

 

UNION에서는 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

)

 

강의

https://spartacodingclub.kr/online/sql

스파르타코딩클럽 [엑셀보다 쉬운 SQL]