220117 Many To Many

    CREATE TABLE reviewers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        first_name VARCHAR(100),
        last_name VARCHAR(100)
    );
    
    CREATE TABLE series(
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(100),
        released_year YEAR(4),
        genre VARCHAR(100)
    );
    
    CREATE TABLE reviews (
        id INT AUTO_INCREMENT PRIMARY KEY,
        rating DECIMAL(2,1),
        series_id INT,
        reviewer_id INT,
        FOREIGN KEY(series_id) REFERENCES series(id),
        FOREIGN KEY(reviewer_id) REFERENCES reviewers(id)
    );

reviewers와 series 테이블을 만들고 reviews테이블에 각테이블의 id를 FK로 넣어준다.

SELECT 
	title, 
	rating 
FROM series
JOIN reviews
	ON series.id = reviews.series_id;

SELECT
	title,
	AVG(rating) as avg_rating
FROM series
JOIN reviews
	ON series.id = reviews.series_id
GROUP BY series.id
ORDER BY avg_rating;

SELECT
	first_name,
	last_name,
	rating
FROM reviewers
INNER JOIN reviews
	ON reviewers.id = reviews.reviewer_id;

SELECT title AS unreviewed_series
FROM series
LEFT JOIN reviews
	ON series.id = reviews.series_id
WHERE rating IS NULL;

SELECT genre, 
	   Round(Avg(rating), 2) AS avg_rating 
FROM   series 
	   INNER JOIN reviews 
			   ON series.id = reviews.series_id 
GROUP  BY genre; 

SELECT first_name, 
	   last_name, 
	   Count(rating)                               AS COUNT, 
	   Ifnull(Min(rating), 0)                      AS MIN, 
	   Ifnull(Max(rating), 0)                      AS MAX, 
	   Round(Ifnull(Avg(rating), 0), 2)            AS AVG, 
	   IF(Count(rating) > 0, 'ACTIVE', 'INACTIVE') AS STATUS 
FROM   reviewers 
	   LEFT JOIN reviews 
			  ON reviewers.id = reviews.reviewer_id 
GROUP  BY reviewers.id; 

SELECT first_name, 
	   last_name, 
	   Count(rating)                    AS COUNT, 
	   Ifnull(Min(rating), 0)           AS MIN, 
	   Ifnull(Max(rating), 0)           AS MAX, 
	   Round(Ifnull(Avg(rating), 0), 2) AS AVG, 
	   CASE 
		 WHEN Count(rating) >= 10 THEN 'POWER USER' 
		 WHEN Count(rating) > 0 THEN 'ACTIVE' 
		 ELSE 'INACTIVE' 
	   end                              AS STATUS 
FROM   reviewers 
	   LEFT JOIN reviews 
			  ON reviewers.id = reviews.reviewer_id 
GROUP  BY reviewers.id;