231131 sql 계속(2)

-- 6.
SELECT name, COUNT(*) AS film_count
FROM category
         JOIN film_category ON film_category.category_id = category.category_id
GROUP BY category.category_id
HAVING film_count >= 70;

-- 7.
SELECT name, COUNT(*)
FROM (SELECT film_category.film_id, category_id
      FROM (SELECT film_id
            FROM rental
                     JOIN inventory ON rental.inventory_id = inventory.inventory_id) AS a
               JOIN film_category ON a.film_id = film_category.film_id) AS b
         JOIN category ON b.category_id = category.category_id
GROUP BY name;

SELECT c.name, COUNT(*)
FROM rental AS r
         JOIN inventory AS i ON r.inventory_id = i.inventory_id
         JOIN film_category AS fc ON fc.film_id = i.film_id
         JOIN category AS c ON fc.category_id = c.category_id
GROUP BY c.name;

-- 강사님꺼
SELECT c.name, COUNT(*)
FROM rental r
         JOIN inventory i ON i.inventory_id = r.inventory_id
         JOIN film_category f ON i.film_id = f.film_id
         JOIN category c ON f.category_id = c.category_id
GROUP BY c.name;

-- 8.
SELECT name, COUNT(*)
FROM (SELECT film_category.film_id, category_id
      FROM (SELECT film_id
            FROM rental
                     JOIN inventory ON rental.inventory_id = inventory.inventory_id) AS a
               JOIN film_category ON a.film_id = film_category.film_id) AS b
         JOIN category ON b.category_id = category.category_id
WHERE name IN ('Comedy', 'Sports', 'Family')
GROUP BY name;

-- 강사님 버전 활용
SELECT c.name, COUNT(*)
FROM rental r
         JOIN inventory i ON r.inventory_id = i.inventory_id
         JOIN film_category fc ON fc.film_id = i.film_id
         JOIN category c ON fc.category_id = c.category_id
WHERE c.name IN ('Comedy', 'Sports', 'Family')
GROUP BY c.name;

-- 9.
SELECT COUNT(*)
FROM rental
WHERE inventory_id IN (SELECT inventory_id
                       FROM inventory
                       WHERE film_id IN (SELECT film_id
                                         FROM film_category
                                         WHERE category_id = (SELECT category_id
                                                              FROM category
                                                              WHERE name = 'Comedy')));

SELECT category_id
FROM category
WHERE name = 'Comedy';
-- category_id: 5


SELECT inventory_id
FROM inventory
WHERE film_id IN (SELECT film_id FROM film_category WHERE category_id = 5);
-- inventory_id > film_id

SELECT film_id
FROM film_category
WHERE category_id = 5;
-- film_id > category_id;

-- 10.
SELECT COUNT(*)
FROM film_category
         JOIN category ON film_category.category_id = category.category_id
WHERE category.name = 'Comedy';

 

이제 확실히 뭔가 문제를 풀어내는 느낌은 있다, 조금 힘들긴하지만, 차분하게..!