231206 SQL 계속(5)

-- 21. 
SELECT *
FROM film;
-- film_id 17:ALONE TRIP

SELECT actor_id
FROM film_actor
WHERE film_id = 17;

SELECT CONCAT(first_name, ' ', last_name) AS 'actor_name'
FROM actor
WHERE actor_id IN (SELECT film_actor.actor_id
                   FROM film_actor
                   WHERE film_id = (SELECT film.film_id
                                    FROM film
                                    WHERE title = 'ALONE TRIP'));

-- 22.
SELECT CONCAT(first_name, ' ', last_name) AS 'Staff Member', SUM(amount) AS 'Total Amount'
FROM payment p
         JOIN staff s ON p.staff_id = s.staff_id
GROUP BY s.staff_id;
-- staff_id, amount, payment_date

SELECT *
FROM staff;

-- 23.
SELECT *
FROM film;
-- film_id, length

SELECT AVG(film.length)
FROM film;

SELECT *
FROM film_category;
-- film_id >> category_id

SELECT *
FROM category;
-- category_id, name(category)

SELECT c.name, AVG(length)
FROM film f
         JOIN film_category fc ON f.film_id = fc.film_id
         JOIN category c ON fc.category_id = c.category_id
GROUP BY c.name
HAVING AVG(length) > (SELECT AVG(film.length) FROM film);

-- 24.

SELECT *
FROM rental;
-- category_id, name

SELECT *
FROM inventory;
-- inventory_id

SELECT *
FROM film_category;
-- inventory_id >> film_id

SELECT *
FROM category;
-- film_id >> category_id

SELECT c.name, AVG(TIMESTAMPDIFF(HOUR, rental_date, return_date))
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
GROUP BY 1;


-- 25.
CREATE VIEW top5_genres AS
SELECT c.name AS genre, SUM(amount) AS 'Total Sales'
FROM payment p
         JOIN rental r ON p.rental_id = r.rental_id
         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
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5;

SELECT c.name AS genre, SUM(amount) AS 'Total Sales'
FROM payment p
         JOIN rental r ON p.rental_id = r.rental_id
         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
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5;
# rental_id, amount

SELECT *
FROM rental;
# rental_id >> inventory_id

SELECT *
FROM inventory;
# inventory_id >> film_id

SELECT *
FROM film_category;
# film_id >> category_id

SELECT *
FROM category;
# category_id, name(category)

 

계속 사킬라만 두들겨댔더니 슬슬 지겨워지려고 한다. 그래서 제공되었던 강의 내의 다른 db를 다시 복습중이다. 여러번 강의를 들어서 그런지, 이제 조금씩 sql 전반에 걸쳐서 편한 느낌이 든다~!