-- Suggested Solution to Fall 2021 CSCI 5333.1 DBMS HW #7

-- Q1.
CREATE OR REPLACE VIEW f21v1 AS
WITH t1 AS
(SELECT f.film_id, COUNT(fa.actor_id) AS numActors
 FROM film f LEFT JOIN film_actor fa ON (f.film_id = fa.film_id)
 GROUP BY f.film_id),
t2 AS
(SELECT f.film_id, COUNT(i.inventory_id) AS numCopies
 FROM film f LEFT JOIN inventory i ON (f.film_id = i.film_id)
 GROUP BY f.film_id),
t3 AS
(SELECT f.film_id, COUNT(r.rental_id) AS numRentals
 FROM film f LEFT JOIN inventory i ON (f.film_id = i.film_id)
	LEFT JOIN rental r ON (i.inventory_id = r.inventory_id)
 GROUP BY f.film_id)
SELECT * 
FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;

-- testing
SELECT *
FROM f21v1
WHERE film_id < 7
ORDER BY film_id ASC;

-- Q2.
SELECT fc.category_id,
	c.name AS category,
	MAX(f.numActors) AS `max numActors`,
	MAX(f.numCopies) AS `max numCopies`,
	MAX(numRentals) AS `max numRentals`
FROM category AS C LEFT JOIN film_category AS fc ON (c.category_id = fc.category_id)
	INNER JOIN f21v1 AS f ON (fc.film_id = f.film_id)
GROUP BY fc.category_id, category;


-- Q3.

DROP FUNCTION IF EXISTS f21f1;

delimiter //
	  
CREATE FUNCTION f21f1 (
    category_id INT) RETURNS DOUBLE
BEGIN
	DECLARE RESULT DOUBLE DEFAULT 0.0;

    SELECT SUM(f.numRentals) / SUM(f.numCopies) INTO RESULT
    FROM film_category AS fc LEFT JOIN f21v1 AS f ON (fc.film_id = f.film_id)
	WHERE fc.category_id = category_id;
	
    RETURN RESULT;
END //

DELIMITER ;

-- testing

SELECT f21f1(1);
SELECT f21f1(2);
SELECT f21f1(3);