-- CSCI 5333 DBMS Spring 2020 HW #7

-- Q1

DROP VIEW IF EXISTS s20v1;

CREATE or replace VIEW s20v1 AS
SELECT DISTINCT fa.actor_id AS actor_id,
       CONCAT(a.first_name, ' ', a.last_name) AS actor,
       c.name AS category,
	   f.title AS film
FROM actor a INNER JOIN film_actor fa ON (a.actor_id = fa.actor_id)
    INNER JOIN film f ON (fa.film_id = f.film_id)
    INNER JOIN film_category fc ON (f.film_id = fc.film_id)
    INNER JOIN category c ON (fc.category_id = c.category_id);

-- Testing.
SELECT *
FROM  s20v1
LIMIT 10;

SELECT *
FROM s20v1
WHERE actor_id = 11;

-- Q2.
SELECT film,
	   CONCAT('[' , COUNT(actor_id) , ' actors with ids]: ' , 
            GROUP_CONCAT(actor_id separator '; '), ',') AS actors
FROM s20v1
GROUP BY film
HAVING COUNT(*) >= 13;

-- testing
SELECT film_id, COUNT(actor_id) AS numActors
FROM film_actor
GROUP BY film_id
ORDER BY numActors ASC
LIMIT 10;

-- Q3.

DROP FUNCTION IF EXISTS s20f1;

delimiter //
	  
CREATE FUNCTION s20f1(
    actorId_1 INT,
	actorId_2 INT) RETURNS INT
READS SQL DATA
BEGIN
    DECLARE RESULT INT DEFAULT 0;
    WITH t1 AS
        (SELECT fc.category_id, COUNT(*) AS count
          FROM film_actor fa INNER JOIN film_category fc ON (fa.film_id = fc.film_id)
          WHERE fa.actor_id = actorId_1
          GROUP BY fc.category_id),
    t2 AS
        (SELECT fc.category_id, COUNT(*) AS count
          FROM film_actor fa INNER JOIN film_category fc ON (fa.film_id = fc.film_id)
          WHERE fa.actor_id = actorId_2
          GROUP BY fc.category_id)      
    SELECT sum(t1.count * t2.count) INTO result
    FROM t1 INNER JOIN t2 ON (t1.category_id = t2.category_id);	

    RETURN result;
END //

delimiter ;

-- testing

SELECT s20f1(1,2);
SELECT s20f1(1,1);
SELECT s20f1(2,2);

WITH t1 AS
(SELECT fc.category_id,
    COUNT(fc.film_id) AS num_films
 FROM film_category fc INNER JOIN film_actor fa ON (fc.film_id = fa.film_id)
 WHERE fa.actor_id = 1
 GROUP BY fc.category_id)
SELECT 1 AS actor_id, c.category_id, 
    IF (t1.num_films IS NULL, 0, t1.num_films) AS num_films
FROM category c LEFT JOIN t1 ON (c.category_Id = t1.category_id);


WITH t1 AS
(SELECT fc.category_id,
    COUNT(fc.film_id) AS num_films
 FROM film_category fc INNER JOIN film_actor fa ON (fc.film_id = fa.film_id)
 WHERE fa.actor_id = 2
 GROUP BY fc.category_id)
SELECT 2 AS actor_id, c.category_id, 
    IF (t1.num_films IS NULL, 0, t1.num_films) AS num_films
FROM category c LEFT JOIN t1 ON (c.category_Id = t1.category_id);

-- Q4
DROP FUNCTION IF EXISTS s20f2;

delimiter //
	  
CREATE FUNCTION s20f2(
    actorId_1 INT,
	actorId_2 INT
) RETURNS FLOAT
READS SQL DATA
BEGIN
    DECLARE result FLOAT DEFAULT 0;
	
	SELECT CAST(s20f1(actorId_1, actorId_2) AS decimal)
		/ (SQRT(CAST(s20f1(actorId_1, actorId_1) AS decimal)) 
		   * SQRT(CAST(s20f1(actorId_2, actorId_2) AS decimal)))
	INTO result;
    RETURN result;
END //

delimiter ;

SELECT s20f2(1,2);

SELECT s20f2(1,3);