728x90
반응형
◎ 0주차
- 강의명: [왕초보] 엑셀보다 쉬운, SQL
- 기간: 2022년 11월 07일 (월) ~ 2022년 12월 05일 (월), 총 4주
완주할 수 있기를...
◎ 1주차
Download | DBeaver Community
Download Tested and verified for MS Windows, Linux and Mac OS X. Install: Windows installer – run installer executable. It will automatically upgrade version (if needed). MacOS DMG – just run it and drag-n-drop DBeaver into Applications. Debian package
dbeaver.io
- MySQL DB Connection Info.
Server Host: s★★★★★★★.a☆☆☆☆☆☆☆.com
Database: s○○○○○○○
Username: s●●●●●●●
Password: s◎◎◎◎◎◎◎
- 실습 내용
# Syntax
select *
from orders
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
where payment_method = 'kakaopay'
limit 5;
select distinct(payment_method)
from orders;
select count(*)
from orders
where payment_method = 'kakaopay';
select count(distinct(name))
from users
# Quiz ---------------------------------------------------------
select email
from users
where name = '남**';
select *
from users
where created_at between '2020-7-12' and '2020-07-14'
and email like '%gmail.com';
select count(*)
from users
where created_at between '2020-7-12' and '2020-07-14'
and email like '%gmail.com';
select payment_method, count(*) as cnt
from orders o
where o.course_title = '앱개발 종합반'
group by payment_method;
select *
from orders
where payment_method != 'CARD';
select *
from point_users
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 point_users
where point > 20000;
select *
from users
where name = '황**';
select *
from orders
where course_title = '웹개발 종합반'
and payment_method = 'CARD';
select payment_method, count(*)
from orders
where course_title = '웹개발 종합반'
group by payment_method
order by count(*);
select payment_method, count(*)
from orders
where course_title = '앱개발 종합반'
group by payment_method
order by count(*);
select name, count(*)
from users
where email like '%gmail.com'
group by name
order by count(*) desc;
select course_id, round(avg(likes), 1)
from checkins
group by course_id;
◎ 2주차
- 실습 내용
# Syntax
select name, count(*)
from users
where name = '신**'
group by name;
select week, count(*)
from checkins
group by week;
select *
from checkins
where week = 1;
select week, min(likes)
from checkins
group by week;
select week, max(likes)
from checkins
group by week;
select week, avg(likes)
from checkins
group by week;
select week, sum(likes)
from checkins
group by week;
select name, count(*)
from users
group by name
order by count(*) desc;
select *
from checkins
order by likes desc;
select payment_method, count(*)
from orders
where course_title = '웹개발 종합반'
group by payment_method;
select payment_method, count(*) as cnt
from orders o
where o.course_title = '앱개발 종합반'
group by payment_method;
# Quiz ---------------------------------------------------------
select payment_method, count(*)
from orders
where course_title = '웹개발 종합반'
group by payment_method
order by count(*);
select payment_method, count(*)
from orders
where course_title = '앱개발 종합반'
group by payment_method
order by count(*);
select name, count(*)
from users
where email like '%gmail.com'
group by name
order by count(*) desc;
select course_id, round(avg(likes), 1)
from checkins
group by course_id;
◎ 3주차
- 실습 내용
# Syntax
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 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(u.name) as count_name
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 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;
# Quiz ---------------------------------------------------------
select o.payment_method, round(avg(pu.`point`), 0) as avg_point
from orders o
inner join point_users pu
on o.user_id = pu.user_id
group by o.payment_method;
select u.name, count(u.name) as cnt_name
from enrolleds e
inner join users u
on u.user_id = e.user_id
where e.is_registered = 0
group by u.name
order by cnt_name desc;
select c.course_id, c.title, count(c.title) 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.course_id;
select c1.title, c2.week, count(c2.week) as cnt
from courses c1
inner join checkins c2
on c1.course_id = c2.course_id
group by c1.title, c2.week
order by c1.title, c2.week;
select c1.title, c2.week, count(c2.week) 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 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
);
# Homework week 03
select e.enrolled_id, e.user_id, count(ed.done) as cnt
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 cnt desc;
◎ 4주차
- 실습 내용
# Syntax
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'
;
↓↓
/* -- equivalent -- */
↑↑
select user_id, name, email
from users
where user_id in (
select user_id
from orders
where payment_method = 'kakaopay'
)
;
select checkin_id
, user_id
, likes
, (
select avg(likes)
from checkins
where 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
group by user_id
) a
on pu.user_id = a.user_id
;
with table1 as (
select course_id, count(distinct(user_id)) as cnt_checkins
from checkins
group by course_id
), table2 as (
select course_id, count(*) as cnt_total from orders
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 courses c
on a.course_id = c.course_id
;
select user_id, email, SUBSTRING_INDEX(email, '@', 1)
from users
;
select user_id, email, SUBSTRING_INDEX(email, '@', -1)
from users
;
select order_no, created_at, SUBSTRING(created_at, 1, 10) as date
from orders
;
select SUBSTRING(created_at,1,10) as date, count(*) as cnt_date
from orders
group by date
;
select pu.user_id, pu.`point`,
(
case when pu.`point` > 10000 then '잘 하고 있어요!'
else '조금만 더 파이팅!' end
) as msg
from point_users pu
;
select a.lv, count(*)
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
;
↓↓
/* -- equivalent -- */
↑↑
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(*)
from table1 a
group by a.lv
;
# Quiz ---------------------------------------------------------
select *
from point_users pu
where pu.`point` > (
select avg(`point`)
from point_users
)
;
select *
from point_users
where `point` > (
select avg(pu.`point`)
from users u
inner join point_users pu
on u.user_id = pu.user_id
where u.name = '이**'
)
;
↓↓
/* -- equivalent -- */
↑↑
select *
from point_users
where `point` > (
select avg(`point`)
from point_users
where user_id in (
select user_id
from users
where name = '이**'
)
)
;
select c.checkin_id, c.course_id, c2.title, c.user_id, c.likes
, (
select round(avg(likes), 1)
from checkins
where course_id = c.course_id
) as course_avg
from checkins c
inner join courses c2
where c.course_id = c2.course_id
;
select c.course_id
, (select title from courses c2 where c2.course_id = c.course_id) as title
, count(distinct user_id) as cnt_checkins
, a.cnt_total
, count(distinct user_id) / cnt_total as ratio
from checkins c
inner join (
select course_id, count(user_id) as cnt_total
from orders
group by course_id
) a
where c.course_id = a.course_id
group by c.course_id
;
↓↓
/* -- equivalent -- */
↑↑
select a.course_id
, 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
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
inner join courses c
on a.course_id = c.course_id
;
select pu.user_id
, pu.`point`
, (
case when pu.`point` >= (select avg(pu.`point`) from point_users pu) then '잘 하고 있어요!'
else '열심히 합시다!' end
) as msg
from point_users pu
;
select SUBSTRING_INDEX(u.email, '@', -1) as `domain`
, count(SUBSTRING_INDEX(u.email, '@', -1)) as cnt_domain
from users u
group by `domain`
;
↓↓
/* -- equivalent -- */
↑↑
select domain, count(*) as cnt
from (
select SUBSTRING_INDEX(email,'@',-1) as domain from users
) a
group by domain
;
select *
from checkins c
where comment like '%화이팅%'
;
select ed.enrolled_id
, a.done_cnt
, count(ed.enrolled_id) as total_cnt
from enrolleds_detail ed
inner join (
select enrolled_id, count(enrolled_id) as done_cnt
from enrolleds_detail
where done = 1
group by enrolled_id
) a
on ed.enrolled_id = a.enrolled_id
group by ed.enrolled_id
;
↓↓
/* -- equivalent -- */
↑↑
select a.enrolled_id
, b.done_cnt
, a.total_cnt
from (
select enrolled_id
, count(enrolled_id) as total_cnt
from enrolleds_detail
group by enrolled_id
) a
inner join (
select enrolled_id
, count(enrolled_id) as done_cnt
from enrolleds_detail
where done = 1
group by enrolled_id
) b
on a.enrolled_id = b.enrolled_id
;
↓↓
/* -- equivalent -- */
↑↑
with lecture_done as (
select enrolled_id
, count(enrolled_id) as done_cnt
from enrolleds_detail
where done = 1
group by enrolled_id
), lecture_total as (
select enrolled_id
, count(enrolled_id) as total_cnt
from enrolleds_detail
group by enrolled_id
)
select a.enrolled_id
, a.done_cnt
, b.total_cnt
from lecture_done a
inner join lecture_total b
on a.enrolled_id = b.enrolled_id
;
with lecture_done as (
select enrolled_id
, count(enrolled_id) as done_cnt
from enrolleds_detail
where done = 1
group by enrolled_id
), lecture_total as (
select enrolled_id
, count(enrolled_id) 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
from lecture_done a
inner join lecture_total b
on a.enrolled_id = b.enrolled_id
;
select enrolled_id
, sum(done) as done_cnt
, count(enrolled_id) as total_cnt
, round(sum(done) / count(enrolled_id)) as ratio
from enrolleds_detail
group by enrolled_id728x90
반응형
SMALL
'Novice Developer' 카테고리의 다른 글
| [Visual Studio Code] 'auto import' 설정 (0) | 2022.11.16 |
|---|---|
| [Visual Studio Code] 다중 선택 (0) | 2022.11.15 |
| [Visual Studio Code] 일명 VSCode 이용하여 Java Coding 시 기억하면 좋은 내용 (2) | 2022.11.14 |
| [Visual Studio Code] 코드 정렬 단축키 (0) | 2022.11.14 |
| SpartaCodingClub - [왕초보] 비개발자를 위한, 웹개발 종합반 (프로그래밍 실무, 풀스택) (0) | 2022.10.26 |
댓글