-- actores_por_peliculas:
-- Todos los datos de la película y el total de actores que han trabajado
-- drop view if exists actores_por_peliculas;
-- en el caso de eliminar puedo usar el 'IF EXISTS' para asegurarme de que existe
-- En caso contrario no hace nada
-- En el caso de las vistas puedo usar OR REPLACE para que si existe
-- La reemplace por la nueva definición
create or replace view actores_por_peliculas as
select film.*, count(actor_id) actores
from `film` join film_actor on film.film_id=film_actor.film_id
group by film_id;
-- estadisticas_peliculas: el título de la película, la cantidad de veces que se ha alquilado,
-- el importe total de pagos, la media de los pagos
create or replace view estadisticas_peliculas as
select title, count(rental_id) alquileres, sum(amount) total_pagos,avg(amount) media_pagos
from film left join inventory using(film_id)
left join rental using(inventory_id)
left join payment using(rental_id)
group by film_id;
-- estadisticas_pais: El nombre del país, la cantidad de clientes que tiene,
-- la cantidad de alquileres y la suma total de los pagos
-- Usamos DISTINCT para que no nos cuenta varias veces el mismo cliente
-- Usamos LEFT JOIN para que nos salgan los paises que no tengan clientes
-- O que tengan clientes que no han alquilado nada
create or replace view estadisticas_pais as
select country, count(distinct customer.customer_id) clientes, count(rental_id) alquileres, sum(amount) pagos
from country left join city using(country_id)
left join address using(city_id)
left join customer using(address_id)
left join rental using(customer_id)
left join payment using(rental_id)
group by country_id;
-- Cread una tabla 'peliculas_sin_alquilar' con
-- toda la información de las películas que no se han alquilado ninguna vez
-- En este caso mejor elimino la que existe para sustituir
-- O la creo solo si no existe
drop table if exists peliculas_sin_alquilar;
create table if not exists peliculas_sin_alquilar
select film.* from film left join inventory on film.film_id=inventory.film_id
left join rental on inventory.inventory_id=rental.inventory_id
where rental_id is null;
-- Cread una tabla 'actores_comedia' con todos los actores que han trabajado en comedia (sin duplicados)
drop table if exists actores_comedia;
create table if not exists actores_comedia
select actor.* from actor join film_actor using (actor_id)
join film using (film_id)
join film_category using (film_id)
join category using (category_id)
where name='comedy';
