-- CSCI 5333 DBMS HW #5 Fall 2021

-- Q1. List the names of all cities in the country with id 12.

SELECT DISTINCT c.city
FROM city AS c
WHERE country_id = 12;

-- Q2. List the names of all cities in Bangladesh.

SELECT DISTINCT c.city
FROM city AS c INNER JOIN country AS co USING (country_id)
WHERE co.country = 'Bangladesh';

-- Q3. List the addresses of customers living in Bangladesh.

SELECT DISTINCT a.address
FROM address AS A INNER JOIN city AS c USING (city_id)
	INNER JOIN country AS co USING (country_id)
WHERE co.country = 'Bangladesh';

-- Q4. List the names of customers who have rented a film with actor id 1.

SELECT DISTINCT CONCAT(c.first_name, ' ', c.last_name) AS customer
FROM customer AS c INNER JOIN rental AS r ON (c.customer_id = r.customer_id)
	INNER JOIN inventory AS i ON (r.inventory_id = i.inventory_id)
    INNER JOIN film_actor AS fa ON (i.film_id = fa.film_id)
WHERE fa.actor_id = 1;

-- Q5. List the names of customers who have rented a film with actor id 1
-- and a film with actor id 2.

SELECT DISTINCT CONCAT(c.first_name, ' ', c.last_name) AS customer
FROM customer AS c INNER JOIN rental AS r1 ON (c.customer_id = r1.customer_id)
	INNER JOIN inventory AS i1 ON (r1.inventory_id = i1.inventory_id)
    INNER JOIN film_actor AS fa1 ON (i1.film_id = fa1.film_id)
	INNER JOIN rental AS r2 ON (c.customer_id = r2.customer_id)
	INNER JOIN inventory AS i2 ON (r2.inventory_id = i2.inventory_id)
    INNER JOIN film_actor AS fa2 ON (i2.film_id = fa2.film_id)
WHERE fa1.actor_id = 1 AND fa2.actor_id = 2;

-- using CTE
WITH c1 AS
(SELECT r.customer_id
 FROM rental AS r
	INNER JOIN inventory AS i ON (r.inventory_id = i.inventory_id)
    INNER JOIN film_actor AS fa ON (i.film_id = fa.film_id)
 WHERE fa.actor_id = 1
),
c2 AS
(SELECT r.customer_id
 FROM rental AS r
	INNER JOIN inventory AS i ON (r.inventory_id = i.inventory_id)
    INNER JOIN film_actor AS fa ON (i.film_id = fa.film_id)
 WHERE fa.actor_id = 2
)
SELECT DISTINCT CONCAT(c.first_name, ' ', c.last_name) AS customer
FROM customer AS c INNER JOIN c1 USING (customer_id)
	INNER JOIN c2 USING (customer_id);

-- Q6. List the names of customers who have rented a film with actor id 1
-- but not a film with actor id 2.

WITH c1 AS
(SELECT r.customer_id
 FROM rental AS r
	INNER JOIN inventory AS i ON (r.inventory_id = i.inventory_id)
    INNER JOIN film_actor AS fa ON (i.film_id = fa.film_id)
 WHERE fa.actor_id = 1
),
c2 AS
(SELECT r.customer_id
 FROM rental AS r
	INNER JOIN inventory AS i ON (r.inventory_id = i.inventory_id)
    INNER JOIN film_actor AS fa ON (i.film_id = fa.film_id)
 WHERE fa.actor_id = 2
)
SELECT DISTINCT CONCAT(c.first_name, ' ', c.last_name) AS customer
FROM customer AS c INNER JOIN c1 USING (customer_id)
WHERE c.customer_id NOT IN (SELECT customer_id FROM c2);

-- Q7. List the titles of films with no recorded actors.

SELECT title
FROM film 
WHERE film_id NOT IN (SELECT DISTINCT film_id FROM film_actor);

-- Q8. List the film_id and the number of actors in the film. List
-- only those films with 13 to 15 actors in the following manner.

SELECT fa.film_id,
	COUNT(actor_id) AS numActors
FROM film_actor AS fa
GROUP BY fa.film_id
HAVING numActors BETWEEN 13 AND 15
ORDER BY numActors DESC;


-- Q9. List the titles of the top ten films with the most number of actors.
-- in the following formats. Note that in this case, a total of 13 films
-- are listed as the tenth ranked film has 12 actors and all six films with
-- with 12 actors are listed.

WITH t AS
(SELECT film_id, COUNT(actor_id) AS numActors
 FROM film_actor
 GROUP BY film_id)
SELECT f.title, t.numActors AS `number of actors`
FROM film AS f INNER JOIN t ON (f.film_id = t.film_id)
WHERE t.numActors >= 
	(SELECT numActors 
	 FROM t 
	 ORDER BY numActors DESC
 	 LIMIT 1 OFFSET 9)
ORDER BY `number of actors` DESC;