Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글

Many To Many

이 글에서는 여러가지 문제들위주로 풀어보겠다.
스키마는 바로 이전 글에서 만들었던 스키마를 이용한다.

Q1.다음과 같이 출력해라.

+----------------------+--------+
| title                | rating |
+----------------------+--------+
| Archer               |    8.0 |
| Archer               |    7.5 |
| Archer               |    8.5 |
| Archer               |    7.7 |
| Archer               |    8.9 |
| Arrested Development |    8.1 |
| Arrested Development |    6.0 |
+----------------------+--------+

답 :

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

Q2.다음과 같이 출력해라.

+----------------------+------------+
| 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 |
+----------------------+------------+

답 :

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

Q3.다음과 같이 출력해라.

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

답 :

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

Q4.다음과 같이 출력하라.(리뷰가 없는 시리즈의 타이틀)

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

답 :

SELECT series.title AS unreviewed_series -- , reviews.id
FROM series
LEFT JOIN reviews
	ON series.id = reviews.series_id
WHERE ISNULL(reviews.id);

-- 또는
SELECT series.title AS unreviewed_series-- , reviews.id
FROM series
LEFT JOIN reviews
	ON series.id = reviews.series_id
WHERE reviews.id IS NULL;

Q5. 다음과 같이 출력하라.(각 장르멸 평균 레이팅)

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

답 :

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

-- 또는 ROUND()함수(반올림 함수)를 이용해 소수2째자리까지만 표현해줄 수 도 있다.
SELECT series.genre, ROUND(AVG(rating),2) AS avg_rating
FROM series
INNER JOIN reviews
	ON series.id = reviews.series_id
GROUP BY series.genre;

Q6. 다음과 같이 출력하라. (review를 한번도 안 쓴 사람은 STATUS가 INACTIVE)

+------------+-----------+-------+-----+-----+---------+----------+
| first_name | last_name | COUNT | MIN | MAX | AVG     | STATUS   |
+------------+-----------+-------+-----+-----+---------+----------+
| Thomas     | Stoneman  |     5 | 7.0 | 9.5 | 8.02000 | ACTIVE   |
| Wyatt      | Skaggs    |     9 | 5.5 | 9.3 | 7.80000 | ACTIVE   |
| Kimbra     | Masters   |     9 | 6.8 | 9.0 | 7.98889 | ACTIVE   |
| Domingo    | Cortes    |    10 | 5.8 | 9.1 | 7.83000 | ACTIVE   |
| Colt       | Steele    |    10 | 4.5 | 9.9 | 8.77000 | ACTIVE   |
| Pinkie     | Petit     |     4 | 4.3 | 8.8 | 7.25000 | ACTIVE   |
| Marlon     | Crafford  |     0 | 0.0 | 0.0 | 0.00000 | INACTIVE |
+------------+-----------+-------+-----+-----+---------+----------+

답 :

SELECT 
	reviewers.first_name, 
    reviewers.last_name, 
    COUNT(reviews.rating) AS COUNT,
    IFNULL(MIN(reviews.rating),0) AS MIN,
    IFNULL(MAX(reviews.rating),0) AS MAX,
    IFNULL(AVG(reviews.rating),0) AS AVG,
    CASE
		WHEN reviews.reviewer_id IS NULL THEN 'INACTIVE'
        ELSE 'ACTIVE'
    END AS 'STATUS'
FROM reviewers
LEFT JOIN reviews
	ON reviewers.id = reviews.reviewer_id
GROUP BY reviewers.id;

--IF문으로
SELECT 
	reviewers.first_name, 
    reviewers.last_name, 
    COUNT(reviews.rating) AS COUNT,
    IFNULL(MIN(reviews.rating),0) AS MIN,
    IFNULL(MAX(reviews.rating),0) AS MAX,
    IFNULL(AVG(reviews.rating),0) AS AVG,
    IF(reviews.reviewer_id IS NULL,'INACTIVE','ACTIVE') AS STATUS
FROM reviewers
LEFT JOIN reviews
	ON reviewers.id = reviews.reviewer_id
GROUP BY reviewers.id;

Q7.다음과 같이 출력하라

+----------------------+--------+-----------------+
| title                | rating | reviewer        |
+----------------------+--------+-----------------+
| Archer               |    8.9 | Colt Steele     |
| Archer               |    8.5 | Kimbra Masters  |
| Archer               |    8.0 | Thomas Stoneman |
| Archer               |    7.7 | Domingo Cortes  |
| Archer               |    7.5 | Wyatt Skaggs    |
| Arrested Development |    9.9 | Colt Steele     |
| Arrested Development |    8.4 | Pinkie Petit    |
| Arrested Development |    8.1 | Thomas Stoneman |
| Arrested Development |    8.0 | Kimbra Masters  |
| Arrested Development |    6.0 | Domingo Cortes  |
| Bob's Burgers        |    8.0 | Domingo Cortes  |
| Bob's Burgers        |    8.0 | Colt Steele     |
| Bob's Burgers        |    7.5 | Pinkie Petit    |
| Bob's Burgers        |    7.1 | Kimbra Masters  |
| Bob's Burgers        |    7.0 | Thomas Stoneman |
+----------------------+--------+-----------------+

답 :

SELECT 
	series.title, 
    reviews.rating, 
    CONCAT(reviewers.first_name, ' ',reviewers.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, rating DESC;