Soluciones ejercicios division y vistas

-- Actores con más películas que el actor de id 1

select first_name, last_name, count(film_actor.film_id) total
 from actor join film_actor on actor.actor_id=film_actor.actor_id
 group by actor.actor_id
 having total>(select count(film_actor.film_id) total
 from actor join film_actor on actor.actor_id=film_actor.actor_id
 where actor.actor_id=1
 group by actor.actor_id);

-- con vistas

create view totalfilmsbyactor as
select actor.actor_id, first_name, last_name, count(film_actor.film_id) total
 from actor join film_actor on actor.actor_id=film_actor.actor_id
 group by actor.actor_id;

select first_name, last_name, total from totalfilmsbyactor
where total>(select total from totalfilmsbyactor where actor_id=1);
 
 -- Media global del total de películas por categoría. Si tengo dos categorías,
 -- una con 100 películas y otra con 50 la media sería 75
 
 select avg(total) total from (
 select name, count(film_category.film_id) total from category join
 film_category on category.category_id=film_category.category_id
 group by category.category_id) as temp;
 
 -- Saber las categorías que tienen peliculas por enciima de la media
 
select name, count(film_category.film_id) total from category join
 film_category on category.category_id=film_category.category_id
 group by category.category_id
 having total>( select avg(total) total from (
 select name, count(film_category.film_id) total from category join
 film_category on category.category_id=film_category.category_id
 group by category.category_id) as temp);
 
 -- COn vistas
 
 create view filmsbycategory as
  select category.category_id, name, count(film_category.film_id) total from category join
 film_category on category.category_id=film_category.category_id
 group by category.category_id;
 
 select name, total from filmsbycategory
 where total>(select avg(total) from filmsbycategory);
 
 
-- Clientes que no han alquilado  películas de rating ‘R’
-- Primero busco los clientes que si hayan alquilado
-- películas de rating r
-- Y después busco los que no estén en esa lista

select first_name, last_name from customer
where customer_id not in(
select customer.customer_id from film join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
join customer on rental.customer_id=customer.customer_id
where rating='r');

-- Clientes que no han alquilado películas del actor con id 1
-- Pasar a positivo y luego los que no
-- Clientes que si han alquilado películas del actor 1
select first_name, last_name from customer
where customer_id not in (
select customer.customer_id from customer join rental on customer.customer_id=rental.customer_id
join inventory on rental.inventory_id=inventory.inventory_id
join film on inventory.film_id=film.film_id
join film_actor on film.film_id=film_actor.film_id
where film_actor.actor_id=1);

-- Actores que no hayan trabajado en películas de robots
-- ni en películas de cocodrilos
-- Actores que sí

select * from actor where actor_id not in (
select actor.actor_id from actor join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id
where description like '%robot%' or description like '%crocodile%');

-- Crear una vista 'customer_info' que para cada cliente me muestre el
-- total de alquileres y el importe total de los mismos

create view customer_info as
select customer.*, count(rental.rental_id) total, sum(amount) importe from customer join rental on customer.customer_id=rental.customer_id
join payment on rental.rental_id=payment.rental_id
group  by customer.customer_id;

-- Ahora ciertas consultas son más sencillas

SELECT sum(total) total FROM customer_info join address
on address.address_id=customer_info.address_id
where district='ontario';

-- Crear una vista 'customer_films que para cada cliente me muestre 
-- en un campo los títulos de las películas que ha alquilado


create view customer_films as
select customer.*, group_concat(title) films from 
customer join rental on customer.customer_id=rental.customer_id
join inventory on rental.inventory_id=inventory.inventory_id
join film on inventory.film_id=film.film_id
group by customer.customer_id;

-- Hay consultas que se simplifican

SELECT * FROM sakila.customer_films
where films like '%dinosaur%';