SQL 예제

 

group by

 

select payment_method , count(*) from orders
where email like '%@naver.com' and course_title = '앱개발 종합반'
group by payment_method

 =>네이버 이메일을 사용하여 앱개발 종합반을 신청한 결제수단별 주문 개수를 구해라. 

---

JOIN

inner join 은 교집합

left join 은 null 값을 같이 출력 -> 왼쪽값 기준으로 

 

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

 => users 테이블이 왼쪽(기준) 에다가 point_users를 붙인다. 기준은 u.user_id = pu.user_id 

 

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

 => orders 테이블과 users 테이블의 교집합을 구한다. 기준은 o.user_id = u.user_id 

 

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 

=>'오늘의 다짐' 정보에 과목 정보를 연결해 과목별 '오늘의 다짐' 갯수를 세어보기

 

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

=> 유저의 포인트 정보가 담긴 테이블에 유저 정보를 연결해서, 많은 포인트를 얻은 순서대로 유저의 데이터를 뽑아보기

 

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

 

=> 주문 정보에 유저 정보를 연결해 네이버 이메일을 사용하는 유저 중, 성씨별 주문건수를 세어보기

 

select o.payment_method , round(avg(pu.point)) 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 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 

=> 결제하고 시작하지 않은 유저들을 성씨별로 세어보기

 

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

=>과목 별로 시작하지 않은 유저들을 세어보기

 

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

=>웹개발, 앱개발 종합반의 week 별 체크인 수를 세어볼까요? 보기 좋게 정리해보기

 

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

=> 8월 1일 이후에 구매한 고객들만 발라내어 보세요!

 

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

=>7월10일 ~ 7월19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보기

 

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 max_count desc

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

 

---

Subquery : 쿼리 안의 쿼리

select * from users
where user_id in (
select user_id  from orders
where payment_method = 'kakaopay'
)

=> kakaopay로 결제한 user_id를 모두 구하기 

 

select * from point_users 
where point > (
select round(avg(point),1) from point_users
)

 

=>전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

 

select * from point_users
where point > (
select avg(point) from users u 
inner join point_users pu on pu.user_id = u.user_id 
where u.name like '이%'
)

=>이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

 

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

=> checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

 

select c.checkin_id ,
(
select title from courses
where title = c2.title 
) as title,
c.course_id ,
c.user_id ,
c.likes,
(
select round(avg(likes),1)from checkins
where course_id = c.course_id
) as avg
from checkins c 
inner join courses c2 on c.course_id = c2.course_id;

=>checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

 

---

 

데이터 표시 포맷

select now() as now
    , date_format(now(), '%Y-%m-01') as start_date
    , date_add(date_add(str_to_date(date_format(now(), '%Y-%m-01'), '%Y-%m-%d'), interval 1 month), interval -1 day ) as end_date
from dual;