SQL 3주차 수업

Ringo Ringo·2022년 3월 20일
0

SQL 수업

목록 보기
3/3

join을 이용해서 key값을 기준으로 테이블들을 연결시켜본다.

ELECT * 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 ;
SELECT
FROM users u ;

SELECT * FROM orders o
inner join users u
on o.user_id = u.user_id ;

SELECT FROM checkins c ;
SELECT
FROM users u ;

SELECT * FROM checkins c
inner join users u
on c.user_id = u.user_id ;

SELECT FROM enrolleds e ;
SELECT
FROM courses c ;

SELECT * FROM enrolleds e
inner join courses c
on e.course_id = c.course_id ;

SELECT FROM checkins c ;
SELECT
FROM courses co ;

SELECT c.course_id, co.title, COUNT(*) as cnt FROM checkins c
inner join courses co
on c.course_id = co.course_id
GROUP by c.course_id ;

SELECT FROM point_users pu ;
SELECT
FROM users u ;

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 FROM orders o ;
SELECT
FROM users u ;

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 FROM point_users pu ;
SELECT
FROM orders o ;

SELECT o.payment_method, ROUND(AVG(pu.point),1) , COUNT(*) FROM point_users pu
inner join orders o
on pu.user_id = o.user_id
GROUP by o.payment_method ;

SELECT FROM enrolleds e ;
SELECT
FROM users u ;

SELECT u.name, e.is_registered, COUNT() 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 COUNT(
) DESC ;

SELECT FROM courses c ;
SELECT
FROM enrolleds e ;

SELECT c.course_id, c.title, COUNT(*) FROM courses c
inner join enrolleds e
on c.course_id = e.course_id
WHERE e.is_registered = 0
GROUP by c.course_id ;

profile
(능력쩔고) 돈 많은 백수 하고 싶당!!!

0개의 댓글