Mini ejercicio sub consultas

1.- Películas con más actores de la media

Pistas:

¿Cual es la media de actores por película? Por un lado necesitamos obtener el total de actores por película, que no lo sabemos. Una consulta entre film, film_actor y actor con un count de actores y un group by de películas.

Con ese total podemos averiguar la media de dos maneras:
a) Creando una vista: Si creo una vista total_actores_pelicula solo tengo que lanzar un avg sobre el total de los actores.
b) Creando una subconsulta con tabla temporal. Si no quiero crear una vista utilizo la consulta anterior como tabla temporal y calculo el avg sobre el total.

Una vez tenemos la media volvemos a la consulta original del total de actores por película. SI la tenemos en una vista no nos hace falta repetirla, en caso contrario la volvemos a hacer. Y ponemos como condición que el total de actores sea mayor que la media.

-- Total de actores por película

select title, count(actor_id) from film join film_actor using(film_id)
group by film_id;

-- ¿Media?
-- Creando una vista
create view total_actores_por_pelicula as
select title, count(actor_id) total from film join film_actor using(film_id)
group by film_id;

select avg(total) from total_actores_por_pelicula;

-- Sin vista, con subtabla o tabla derivada

select avg(total) from (
select title, count(actor_id) total from film join film_actor using(film_id)
group by film_id
) temporal;

-- Si tengo la consulta la puedo usar

select title, count(actor_id) total from film join film_actor using(film_id)
group by film_id
having total>(
select avg(total) from total_actores_por_pelicula
);

-- Si no repito la subconsulta

select title, count(actor_id) total from film join film_actor using(film_id)
group by film_id
having total>(
select avg(total) from (
select title, count(actor_id) total from film join film_actor using(film_id)
group by film_id
) temporal
);

2.- Clientes con películas alquiladas por encima de la media

select avg(total) from (
select first_name,last_name, count(rental_id) total
from customer join rental using(customer_id)
group by customer_id) temporal;

select first_name,last_name, count(rental_id) total
from customer join rental using(customer_id)
group by customer_id
having total>(select avg(total) from (
select first_name,last_name, count(rental_id) total
from customer join rental using(customer_id)
group by customer_id) temporal)

3.- Clientes que no hayan alquilado películas de acción

-- Los que sí han alquilado acción
select first_name, last_name, name
from customer join rental using(customer_id)
join inventory using(inventory_id)
join film using(film_id)
join film_category using(film_id)
join category using(category_id)
where name='Action';

-- Los que no estén en la lista anterior

select first_name, last_name from customer
where customer_id not in (
select customer_id
from customer join rental using(customer_id)
join inventory using(inventory_id)
join film using(film_id)
join film_category using(film_id)
join category using(category_id)
where name='Action'
)