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 ;