[Udemy] Many To Many

The Ultimate MySQL Bootcamp 13강을 공부하며 정리한 내용입니다.

다대다 관계의 테이블은 다음과 같은 경우 설정할 수 있다.

• Books & Authors
  A book can have multiple authors and each author can have multiple books

• Blog Posts(content) & Tags
  each post can have multiple tags and each of those tags can have multiple posts

• Students & Classes
  A student can have multiple classes and each class has multiple students

tv show reviewing application schema

13강에서는 tv show reviewing application을 만들때, 필요한 테이블 관계 설정을 연습한다.

필요한 테이블을 생각해보면, 다대다 관계인 리뷰어, 시리즈, 그리고 이 두개의 테이블을 연결하는 리뷰 테이블이 있다.

테이블 생성 및 데이터 추가

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)
);
// 데이터 추가 부분 생략

challenge 1

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

challenge 2

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

challenge 3

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

//inner join의 경우 테이블 순서가 바뀌어도 됨
SELECT 
  first_name, last_name, rating
FROM reviews
JOIN reviewers
  ON reviews.reviewer_id = reviewers.id;

challenge 4

SELECT title AS unreviewed_series
FROM series
LEFT JOIN reviews //Null값을 확인하기 위해 LEFT JOIN
  ON series.id = reviews.series_id
WHERE reviews.rating IS NULL;

challenge 5

• ROUND(값, 자릿수)
  

SELECT genre, avg(rating) AS avg_rating
FROM series
JOIN reviews //NULL값을 제외하기 위해 INNER JOIN
  ON series.id = reviews.series_id
GROUP BY genre;

// ROUND(값, 자릿수)를 사용해 자릿수+1번째에서 반올림
SELECT 
  genre, 
  ROUND(
    avg(rating), 2
  ) AS avg_rating
FROM series
JOIN reviews //NULL값을 제외하기 위해 INNER JOIN
  ON series.id = reviews.series_id
GROUP BY genre;

challenge 6

• IF(조건, 값1, 값2)
  

SELECT 
  first_name, 
  last_name, 
  count(reviews.id) AS count,
  IFNULL(min(rating),0) AS min,
  IFNULL(max(rating),0) AS max,
  IFNULL(avg(rating),0) AS avg,
  CASE 
    WHEN avg(rating) = 0 THEN 'inactive'
    ELSE 'active'
  END AS status
FROM reviewers
LEFT JOIN reviews
  ON reviewers.id = reviews.reviewer_id
GROUP BY reviewers.id;
/*
테이블을 inner 또는 left join하게 되면 
생성된 조인 테이블에는 동일한 pk 또는 칼럼값을 가지는 데이터들이 있을 수 있는데,
이때, group by를 사용해 동일한 값을 가진 데이터들을 묶을 수 있다. 
*/

//CASE 대신 IF를 사용한 쿼리문
SELECT 
  first_name, 
  last_name, 
  count(reviews.id) AS count,
  IFNULL(min(rating),0) AS min,
  IFNULL(max(rating),0) AS max,
  IFNULL(avg(rating),0) AS avg,
  IF(avg(rating) >= 1, 'active', 'inactive') AS status
FROM reviewers
LEFT JOIN reviews
  ON reviewers.id = reviews.reviewer_id
GROUP BY reviewers.id;
/*
두가지 조건으로 새로운 칼럼을 생성해 값을 추가하는 경우에는 IF함수를 사용할 수 있으며, 
세 가지 이상의 조건은 CASE를 사용하는 것이 적절하다.
*/ 

challenge 7

SELECT 
  title,
  rating,
  CONCAT(first_name, ' ', last_name) AS reviewer
FROM reviewers 
JOIN reviews
  ON reviews.reviewer_id = reviewers.id 
JOIN series
  ON reviews.series_id = series.id
ORDER BY title;