[SQL] 여러테이블 합치기 실습문제

select * from series;
select * from reviewers;
select * from reviews;

INSERT INTO series (title, released_year, genre) VALUES
   ('Archer', 2009, 'Animation'),
   ('Arrested Development', 2003, 'Comedy'),
   ("Bob's Burgers", 2011, 'Animation'),
   ('Bojack Horseman', 2014, 'Animation'),
   ("Breaking Bad", 2008, 'Drama'),
   ('Curb Your Enthusiasm', 2000, 'Comedy'),
   ("Fargo", 2014, 'Drama'),
   ('Freaks and Geeks', 1999, 'Comedy'),
   ('General Hospital', 1963, 'Drama'),
   ('Halt and Catch Fire', 2014, 'Drama'),
   ('Malcolm In The Middle', 2000, 'Comedy'),
   ('Pushing Daisies', 2007, 'Comedy'),
   ('Seinfeld', 1989, 'Comedy'),
   ('Stranger Things', 2016, 'Drama');
 
 
INSERT INTO reviewers (first_name, last_name) VALUES
   ('Thomas', 'Stoneman'),
   ('Wyatt', 'Skaggs'),
   ('Kimbra', 'Masters'),
   ('Domingo', 'Cortes'),
   ('Colt', 'Steele'),
   ('Pinkie', 'Petit'),
   ('Marlon', 'Crafford');
  
 
INSERT INTO reviews(series_id, reviewer_id, rating) VALUES
   (1,1,8.0),(1,2,7.5),(1,3,8.5),(1,4,7.7),(1,5,8.9),
   (2,1,8.1),(2,4,6.0),(2,3,8.0),(2,6,8.4),(2,5,9.9),
   (3,1,7.0),(3,6,7.5),(3,4,8.0),(3,3,7.1),(3,5,8.0),
   (4,1,7.5),(4,3,7.8),(4,4,8.3),(4,2,7.6),(4,5,8.5),
   (5,1,9.5),(5,3,9.0),(5,4,9.1),(5,2,9.3),(5,5,9.9),
   (6,2,6.5),(6,3,7.8),(6,4,8.8),(6,2,8.4),(6,5,9.1),
   (7,2,9.1),(7,5,9.7),
   (8,4,8.5),(8,2,7.8),(8,6,8.8),(8,5,9.3),
   (9,2,5.5),(9,3,6.8),(9,4,5.8),(9,6,4.3),(9,5,4.5),
   (10,5,9.9),
   (13,3,8.0),(13,4,7.2),
   (14,2,8.5),(14,3,8.9),(14,4,8.9);

 

 

-- 실습문제 3 = 실습5 (10~ 16번 문제)
-- 10번 문제

select series.title, reviews.rating 
from series
join reviews
    on series.id = reviews.series_id;
    
-- 11번 문제

select series.title, avg(reviews.rating) as avg_rating 
from series
join reviews
    on series.id = reviews.series_id
group by series.title
order by avg_rating;

-- 12번 문제

select reviewers.first_name, reviewers.last_name, reviews.rating
from reviewers
join reviews
    on reviewers.id = reviewer_id;
    
-- 13번 문제 (실무에 많이 씀)

select title as unreviewed_series
from series s
left join reviews r -- left는 series right는 reviews 왼쪽먼저 다 불러와서 오른쪽에 붙여라. 
on s.id = r.series_id
where r.id is null; 

-- 14번 문제

select s.genre, round(avg(r.rating), 2) as avg_rating -- round() 반올림, floor() 반내림
from series s
left join reviews r
on s.id = r.series_id
group by s.genre;

-- 15번 문제

-- case 이용
select rv.first_name, rv.last_name, 
count(r.rating) as COUNT,
min(r.rating) as MIN,
max(r.rating) as MAX,
avg(r.rating) as AVG,
case 
when count(r.rating) > 0 then 'ACTIVE'
    else 'INACTIVE'
end as STATUS
from reviewers rv
left join reviews r
on rv.id = r.reviewer_id
group by r.id; 

-- if 이용, ifnull 로 null이 있을 경우 0 처리
select rv.first_name, rv.last_name, 
count(r.rating) as COUNT,
ifnull(min(r.rating), 0 ) as MIN,
ifnull(max(r.rating), 0 ) as MAX,
ifnull(avg(r.rating), 0 ) as AVG,
if(count(r.rating) > 0, 'ACTIVE' , 'INACTIVE') as STATUS
from reviewers rv
left join reviews r
on rv.id = r.reviewer_id
group by r.id; 


-- 16번 문제

select s.title, r.rating, 
concat(rv.first_name, ' ', rv.last_name) as reviewer
from series s
join reviews r
on s.id = r.series_id
join reviewrs rv
on rv.id = r.reviewr_id
order by s.title;