최초 발행일 2020-01-26

ManyToMany

Reviewers	Series	Reviews
id	id	id
first_name	title	rating
last_name	released_year	series_id
	genre	reviewer_id

CREATE TABLE reviewers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(100),
  last_name VARCHAR(100)
);

CREATE TABLE series (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(100),
  released_year YEAR(4),
  genre VARCHAR(100)
);

CREATE TABLE reviews (
  id INT PRIMARY KEY AUTO_INCREMENT,
  -- ex) 9.9, 4.3
  rating DECIMAL(2, 1),
  series_id INT,
  reviewer_id INT,
  FOREIGN KEY(series_id) REFERENCES series(id),
  FOREIGN KEY(reviewer_id) REFERENCES reviewers(id)
);

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);

challenge 1

다음의 결과가 나오도록 SQL문을 구성하여라

+----------------------+------------+
| title                | avg_rating |
+----------------------+------------+
| General Hospital     |    5.38000 |
| Bob's Burgers        |    7.52000 |
| Seinfeld             |    7.60000 |
| Bojack Horseman      |    7.94000 |
| Arrested Development |    8.08000 |
| Archer               |    8.12000 |
| Curb Your Enthusiasm |    8.12000 |
| Freaks and Geeks     |    8.60000 |
| Stranger Things      |    8.76667 |
| Breaking Bad         |    9.36000 |
| Fargo                |    9.40000 |
| Halt and Catch Fire  |    9.90000 |
+----------------------+------------+

answer

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;

challenge 2

다음의 결과가 나오도록 SQL문을 구성하여라

+------------+-----------+--------+
| first_name | last_name | rating |
+------------+-----------+--------+
| Thomas     | Stoneman  |    8.0 |
| Thomas     | Stoneman  |    8.1 |
| Thomas     | Stoneman  |    7.0 |
| Wyatt      | Skaggs    |    7.5 |
| Wyatt      | Skaggs    |    7.6 |
| Wyatt      | Skaggs    |    9.3 |
| Wyatt      | Skaggs    |    6.5 |
| Wyatt      | Skaggs    |    8.4 |
| Kimbra     | Masters   |    8.5 |
| Kimbra     | Masters   |    8.0 |
| Kimbra     | Masters   |    7.1 |
| Kimbra     | Masters   |    7.8 |
...

answer

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

challenge 3

다음의 결과가 나오도록 SQL문을 구성하여라

+-----------------------+
| unreviewed_series     |
+-----------------------+
| Malcolm In The Middle |
| Pushing Daisies       |
+-----------------------+

answer

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

challenge 4

다음의 결과가 나오도록 SQL문을 구성하여라

+-----------+------------+
| genre     | avg_rating |
+-----------+------------+
| Animation |    7.86000 |
| Comedy    |    8.16250 |
| Drama     |    8.04375 |
+-----------+------------+

answer

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

challenge 5

다음의 결과가 나오도록 SQL문을 구성하여라.
사용자가 남긴 리뷰가 없을 시 INACTIVE, 리뷰 1개 이상 남겼을 시 ACTIVE, 10개 이상 남겼을 시 POWER USER 출력

+------------+-----------+-------+-----+-----+------+------------+
| first_name | last_name | COUNT | MIN | MAX | AVG  | STATUS     |
+------------+-----------+-------+-----+-----+------+------------+
| Thomas     | Stoneman  |     5 | 7.0 | 9.5 | 8.02 | ACTIVE     |
| Wyatt      | Skaggs    |     9 | 5.5 | 9.3 | 7.80 | ACTIVE     |
| Kimbra     | Masters   |     9 | 6.8 | 9.0 | 7.99 | ACTIVE     |
| Domingo    | Cortes    |    10 | 5.8 | 9.1 | 7.83 | POWER USER |
| Colt       | Steele    |    10 | 4.5 | 9.9 | 8.77 | POWER USER |
| Pinkie     | Petit     |     4 | 4.3 | 8.8 | 7.25 | ACTIVE     |
| Marlon     | Crafford  |     0 | 0.0 | 0.0 | 0.00 | INACTIVE   |
+------------+-----------+-------+-----+-----+------+------------+

answer

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;

challenge 6

다음의 결과가 나오도록 SQL문을 구성하여라

+----------------------+--------+-----------------+
| title                | rating | reviewer        |
+----------------------+--------+-----------------+
| Archer               |    8.9 | Colt Steele     |
| Archer               |    8.0 | Thomas Stoneman |
| Archer               |    7.7 | Domingo Cortes  |
| Archer               |    8.5 | Kimbra Masters  |
| Archer               |    7.5 | Wyatt Skaggs    |
| Arrested Development |    8.1 | Thomas Stoneman |
| Arrested Development |    6.0 | Domingo Cortes  |
| Arrested Development |    8.0 | Kimbra Masters  |
| Arrested Development |    8.4 | Pinkie Petit    |
| Arrested Development |    9.9 | Colt Steele     |
| Bob's Burgers        |    7.0 | Thomas Stoneman |
| Bob's Burgers        |    8.0 | Domingo Cortes  |
| Bob's Burgers        |    7.1 | Kimbra Masters  |
| Bob's Burgers        |    7.5 | Pinkie Petit    |
| Bob's Burgers        |    8.0 | Colt Steele     |
| Bojack Horseman      |    7.6 | Wyatt Skaggs    |
| Bojack Horseman      |    7.5 | Thomas Stoneman |
| Bojack Horseman      |    8.3 | Domingo Cortes  |
| Bojack Horseman      |    7.8 | Kimbra Masters  |
| Bojack Horseman      |    8.5 | Colt Steele     |
| Breaking Bad         |    9.3 | Wyatt Skaggs    |
| Breaking Bad         |    9.5 | Thomas Stoneman |
| Breaking Bad         |    9.1 | Domingo Cortes  |
| Breaking Bad         |    9.0 | Kimbra Masters  |
| Breaking Bad         |    9.9 | Colt Steele     |
...

answer

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