4주차 숙제 : 블로그에 문법 총정리 하기

tables 살펴보기;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
show tables;
SELECT * FROM orders o ;
SELECT order_no ,created_at ,course_title ,email FROM orders o ;

SELECT /WHERE 연습;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SELECT FROM orders o
WHERE payment_method ='kakaopay';
SELECT FROM point_users pu
WHERE point >=5000;
SELECT FROM orders o
WHERE course_title ='앱개발 종합반' AND payment_method = 'CARD';
SELECT FROM point_users pu
WHERE point > 20000;
SELECT FROM users u
WHERE name = '황**';
SELECT FROM orders o
WHERE course_title ='웹개발 종합반' AND payment_method ='CARD';
SELECT FROM orders o
WHERE course_title != '앱개발 종합반';
select from orders
where created_at between "2020-07-13" and "2020-07-15";
select from checkins
where week in (1, 3);
select from users
where email like '%daum.net';
SELECT FROM orders o
WHERE payment_method != 'CARD';
select from point_users pu
where point between 20000 and 30000;
select from users
where email like 's%com';
select from users
where email like 's%com' AND name = '이*';
SELECT FROM orders o
WHERE payment_method = 'kakaopay'
LIMIT 5;
SELECT DISTINCT (payment_method) FROM orders o ;
select COUNT(*) from users;

count/min/max/avg/sum/round 연습;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SELECT FROM checkins c limit 10;
SELECT week, count() FROM checkins c
group by week ;
SELECT FROM checkins c
WHERE week =1 ;
SELECT week, MIN(likes) FROM checkins c
group by week ;
SELECT week, MAX(likes) FROM checkins c
group by week ;
SELECT week, AVG(likes) FROM checkins c
group by week ;
SELECT week, ROUND(AVG(likes),2) FROM checkins c
group by week ;
SELECT week, ROUND(AVG(likes),0) FROM checkins c
group by week ;
SELECT week, SUM(likes) FROM checkins c
group by week ;
SELECT FROM checkins c
WHERE week = 3 ;

group by / order by 연습;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SELECT FROM users u
group by name ;
SELECT name,COUNT() FROM users u
group by name ;
SELECT name,COUNT() FROM users u
group by name;
SELECT payment_method ,COUNT() FROM orders o
group by payment_method;
SELECT payment_method ,COUNT() FROM orders o
WHERE course_title ='웹개발 종합반'
group by payment_method;
SELECT payment_method ,COUNT() FROM orders o
WHERE course_title ='웹개발 종합반'
group by payment_method
order by COUNT() ASC ;
SELECT payment_method ,COUNT() FROM orders o
WHERE course_title ='웹개발 종합반'
group by payment_method
order by COUNT() DESC ;
SELECT FROM users u
order by email ;
SELECT FROM users u
order by name DESC ;
SELECT FROM users u
order by created_at ASC ;
SELECT payment_method ,COUNT() FROM orders o
group by payment_method ;
SELECT payment_method ,COUNT() FROM orders o
WHERE course_title ='앱개발 종합반'
group by payment_method;
SELECT name ,COUNT() FROM users u
WHERE email LIKE '%gmail.com'
GROUP BY name;
SELECT course_id ,ROUND(AVG(likes),1) FROM checkins c
group by course_id ;
1) show tables로 어떤 테이블이 있는지 살펴보기
2) 제일 원하는 정보가 있을 것 같은 테이블에 select from 테이블명 limit 10 쿼리 날려보기
3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기
4) 테이블을 찾았다! 범주를 나눠서 보고싶은 필드를 찾기
5) 범주별로 통계를 보고싶은 필드를 찾기
6) SQL 쿼리 작성하기!

join 연습;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SELECT FROM users u
left join point_users pu on u.user_id = pu.user_id ;
SELECT FROM users u
inner join point_users pu on u.user_id = pu.user_id ;
SELECT FROM orders o
inner join users u on o.user_id = u.user_id ;
SELECT FROM checkins c
inner join users u on c.user_id = u.user_id ;
SELECT FROM enrolleds e
inner join courses c on e.course_id = c.course_id ;
SELECT c.course_id , c2.title , COUNT(c.likes) as cnt FROM checkins c
inner join courses c2 on c.course_id = c2.course_id
GROUP BY c.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() as cnt FROM orders o
inner join users u on o.user_id = u.user_id
WHERE o.email LIKE '%naver.com'
GROUP by u.name ;
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 ;
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 c.course_id , c.title ,COUNT() as cnt_notstart FROM courses c
inner join enrolleds e on c.course_id =e.course_id
WHERE e.is_registered =0
GROUP by c.title ;
SELECT co.title ,ch.week,COUNT() as cnt FROM courses co
inner join checkins ch on co.course_id = ch.course_id
GROUP by co.title ,ch.week
ORDER by co.title ,ch.week;
SELECT co.title ,ch.week,COUNT() as cnt FROM courses co
inner join checkins ch on co.course_id = ch.course_id
GROUP by co.title ,ch.week
ORDER by co.title ASC ,ch.week DESC ;
SELECT co.title , ch.week, COUNT() as cnt FROM courses co
inner join checkins ch on co.course_id = ch.course_id
INNER JOIN orders o on ch.user_id = o.user_id
WHERE o.created_at >= '2020-08-01'
GROUP by co.title , ch.week
ORDER BY co.title , ch.week;
SELECT u.name, COUNT() as cnt FROM users u
left join point_users pu on u.user_id = pu.user_id
WHERE pu.point_user_id IS NULL
GROUP by u.name ;
SELECT u.name, COUNT() as cnt FROM users u
left join point_users pu on u.user_id = pu.user_id
WHERE pu.point_user_id IS NOT NULL
GROUP by u.name ;
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';
(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
);
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, e.user_id
ORDER BY COUNT() DESC limit 0, 10;
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, e.user_id
ORDER BY max_count DESC ;

subquery 연습;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
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' ;
SELECT u.user_id , u.name , u.email FROM users u
WHERE user_id IN (
select user_id FROM orders o
WHERE payment_method = 'kakaopay'
);
SELECT c.checkin_id ,
c.user_id ,
c.likes ,
(
SELECT ROUND(AVG(likes ),1) FROM checkins c2
WHERE c2.user_id = c.user_id
) AS avg_likes_user
FROM checkins c;
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 point_users pu
WHERE point > (
SELECT AVG(point) FROM point_users
);
SELECT FROM point_users pu
WHERE point > (
SELECT AVG(point) FROM point_users pu
inner join users u on pu.user_id = u.user_id
WHERE u.name = '이'
);
SELECT * FROM point_users pu
WHERE point > (
SELECT AVG(point) FROM point_users pu
WHERE user_id IN (
SELECT user_id from users u
WHERE u.name = '이'
)
);
SELECT FROM point_users pu
WHERE point > (
SELECT AVG(point) FROM point_users pu
inner join users u on pu.user_id = u.user_id
WHERE u.name = '이**'
);
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 = '이*'
)
);
SELECT c.checkin_id ,
c.course_id ,
c.user_id ,
c.likes ,
(
select ROUND(avg(c2.likes),1) FROM checkins c2
WHERE c2.course_id =c.course_id
) AS course_avg
FROM checkins c ;
SELECT c.checkin_id , c3.title , c.user_id , c.likes ,
(
select ROUND(avg(c2.likes),1) FROM checkins c2
WHERE c2.course_id =c.course_id
) AS course_avg
FROM checkins c
inner join courses c3 on c.course_id = c3.course_id ;
SELECT c.checkin_id , c.course_id , c.user_id , c.likes ,
(
select ROUND(avg(c2.likes),1) FROM checkins c2
WHERE c2.course_id =c.course_id
) AS course_avg,
c3.
FROM checkins c
inner join courses c3 on c.course_id = c3.course_id ;
SELECT a.course_id,
a.cnt_checkins,
b.cnt_total,
ROUND((a.cnt_checkins/b.cnt_total),3) as ratio
FROM
(
SELECT course_id,COUNT(DISTINCT (user_id)) as cnt_checkins FROM checkins ch
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 courses co on a.course_id = co.course_id;
SELECT co.title ,
a.cnt_checkins,
b.cnt_total,
ROUND((a.cnt_checkins/b.cnt_total),3) as ratio
FROM
(
SELECT course_id,COUNT(DISTINCT (user_id)) as cnt_checkins FROM checkins ch
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 courses co on a.course_id = co.course_id;

with 연습;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SELECT co.title ,
a.cnt_checkins,
b.cnt_total,
ROUND((a.cnt_checkins/b.cnt_total),3) as ratio
FROM
(
SELECT course_id,COUNT(DISTINCT (user_id)) as cnt_checkins FROM checkins ch
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 courses co on a.course_id = co.course_id;

with table1 as (
SELECT course_id,COUNT(DISTINCT (user_id)) as cnt_checkins FROM checkins ch
group by course_id
), table2 as (
SELECT course_id, COUNT(*) as cnt_total FROM orders o
group by course_id
)
SELECT co.title ,
a.cnt_checkins,
b.cnt_total,
ROUND((a.cnt_checkins/b.cnt_total),3) as ratio
FROM
table1 a
INNER JOIN table2 b ON a.course_id = b.course_id
INNER JOIN courses co on a.course_id = co.course_id;

문자열;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
SELECT u.user_id ,u.email ,SUBSTRING_INDEX(email, '@',-1) FROM users u ;
SELECT o.order_no , o.created_at, SUBSTRING(o.created_at,1,10) as Date FROM orders o ;
SELECT SUBSTRING(o.created_at,1,10) as Date,COUNT() FROM orders o
group by Date;
SELECT o.order_no , o.created_at, SUBSTRING(o.created_at,12,8) FROM orders o ;
Case;
SELECT pu.user_id , pu.point ,
(
CASE when pu.point > 10000 then '잘 하고 있어요!'
ELSE '조금난 더 파이팅!'
END) as msg
FROM point_users pu ;
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 (
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
) a
group by a.lv;
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;
SELECT u.user_id ,u.email ,SUBSTRING_INDEX(email, '@',-1) FROM users u ;
SELECT o.order_no , o.created_at, SUBSTRING(o.created_at,1,10) as Date FROM orders o ;
SELECT SUBSTRING(o.created_at,1,10) as Date,COUNT() FROM orders o
group by Date;
SELECT o.order_no , o.created_at, SUBSTRING(o.created_at,12,8) FROM orders o ;
Case;
SELECT pu.user_id , pu.point ,
(
CASE when pu.point > 10000 then '잘 하고 있어요!'
ELSE '조금난 더 파이팅!'
END) as msg
FROM point_users pu ;
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 (
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
) a
group by a.lv;
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;