-- CSCI 5333 DBMS HW #3 to 5 Spring 2020
-- Note that CLASSPATH should have .;./*;...
-- java edu.gsu.cs.ra.RA sakila


-- Q1. List the titles of all films with a PG rating and a length of 180 or more.

SELECT DISTINCT title 
FROM film 
WHERE rating = 'PG'
AND length >=180;

-- Q2. List the titles of all films with the actor with id #148.

SELECT DISTINCT f.title
FROM film f JOIN film_actor fa 
    ON (f.film_id = fa.film_id)
WHERE fa.actor_id = 148;

-- Testing
SELECT actor_id, COUNT(film_id) AS num_films
FROM film_actor
GROUP BY actor_id
ORDER BY num_films ASC
LIMIT 5;

-- Q3. List the titles of all films with a length of 180 or more and in the category 'Comedy'.

SELECT DISTINCT f.title
FROM film f INNER JOIN film_category fc ON (f.film_id = fc.film_id)
    INNER JOIN category c ON (fc.category_id = c.category_id)
WHERE c.name = 'Comedy'
AND f.length >=180;


-- Q4. List the titles of films with an actor with id 1 or 17.

SELECT DISTINCT f.title
FROM film f INNER JOIN film_actor fa ON (f.film_id = fa.film_id)
WHERE fa.actor_id = 1
OR fa.actor_id = 17;


-- Q5. List the titles of films that have not been rented. 
--    (Include films that are not in the inventory.)

SELECT DISTINCT f.title
FROM film f
WHERE film_id NOT IN 
	(SELECT DISTINCT i.film_id
	 FROM rental r INNER JOIN inventory i ON (r.inventory_id = i.inventory_id))
ORDER BY f.title;


-- Q6. List all customer names who have rented 40 or more times
--     in the following format.
    
SELECT DISTINCT CONCAT(c.first_name, ' ', c.last_name) AS customer,
    COUNT(r.rental_id) AS `Number of rental`
FROM customer c INNER JOIN rental r ON (c.customer_id = r.customer_id)
GROUP BY customer
HAVING `Number of rental` >= 40
ORDER BY `Number of rental` DESC;

-- Q7. For each film, lists the id, film title, the number of copies in the
-- inventory, the number of times the film has been rented. Only list the film
-- rented for 32 or more times.

SELECT f.film_id, f.title,
    COUNT(DISTINCT i.inventory_id) AS num_copies,
    COUNT(DISTINCT r.rental_id) AS num_rented
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, f.title
HAVING num_rented >= 32
ORDER BY num_rented DESC;


-- Q8. List the numbers of films that have been rented n times in ascending
-- order of n.

WITH num_rentals AS
(   SELECT f.film_id,
        COUNT(DISTINCT r.rental_id) AS num_rented
    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  nr.num_rented AS `Times rented`, 
    COUNT(nr.film_id) AS `num of films`
FROM num_rentals AS nr
GROUP BY nr.num_rented
ORDER BY nr.num_rented ASC;