Autor: Juan Pablo Fuentes

-- Funciones de conversión de tipos
-- CAST es estándar y nos permite convertir un tipo en otro
-- Los nombres de los tipos cuando convertimos no son los mismos
-- que cuando los creamos
-- BINARY,CHAR,DATE,DATETIME,TIME,DECIMAL,SIGNED,UNSIGNED
-- Todos los tipos los tenéis en https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html
select cast('1.23' as DECIMAL(8,2)) n;

-- Para qué convertir: si tengo valores de cadena que yo sé que
-- son numéricos o de fecha para operar y extraer información
-- Y al revés, si tengo datos numéricos para concatenar o extraer información
-- por posición

select convert('123',SIGNED) n;

-- Usando CAST
SELECT CAST('2023-01-01' AS DATE) fecha;

-- Usando CONVERT
SELECT CONVERT('2023-01-01', DATE) fecha;

-- Funciones específicas de Mysql
-- Funciones de cadena, de fecha, aritméticas,....

-- Funciones de cadena

-- CONCAT para concatenar cadenas

select CONCAT(first_name,' ',last_name) nombre from actor;

-- Cuidado porque al concatenar un valor nulo con valor válidos el nulo 'se contagia'
-- es decir, concat(cualquier-valor,null)-->null
select  CONCAT(address,' - ',address2,' (',postal_code,')') direccion from address;

-- Subcadenas SUBSTRING(posicion, longitud)
-- Ojo, las cadenas empiezan en 1
select substring(first_name,2,3) from actor;

-- Podemos usar valores negativos
select substring(first_name,-3,1) from actor;

-- Por ejemplo la primera letra:
select substring(first_name,1,1) from actor;

-- La última
select substring(first_name,-1,1) from actor;

-- Las funciones las pudo utilizar en el select o en otras partes de la consulta
-- Por ejemplo: actores cuyo nombre acaba en 'E'
-- El substring lo utilizo en el where
select * from actor
where substring(first_name,-1,1)='e';

-- Actores ordenador por la última letra del nombre
select * from actor
order by substring(first_name,-1,1);

-- longitud de ua cadena

select length(first_name) longitud from actor;

-- Paises ordenados por la longitud de su nombre
select * from country
order by length(country);

-- Pasar a mayúsculas o minúsculas
select upper(country) mayusculas,lower(country) minusculas from country;

-- Puedo reemplazar una cadena por otra

select replace(country,'a','@') pais from country;

-- trim nos elimina espacios del principio y del fin
-- Esto es super útil en la vida real porque ni os imagináis la cantidad de
-- espacios basura que hay en las bases de datos

select trim('    asdasd    ') foo;

-- Yo muchas veces hago cosas como esta
-- Esto me limpia de espacios ese campo
update actor set first_name=trim(first_name);

-- Concatena con el reparador que yo le ponga
select concat_ws(' ',first_name,last_name) nombre from actor;

-- Es muy útil cuando quiero juntar varios campos usando el mismo separador
select concat_ws(' ',address,address2,district,postal_code) direccion from address;

-- Lo índices nos sirven para acelerar búsquedas sobre un campo
-- Lo que hace la base de datos es crear una copia ordenada
-- de los valores de ese campo
-- por lo que al buscar hace la busqueda binaria que es mucho más rápida

-- Crear un índice tiene pROS y CONTRAS
-- PRO: mayor velocidad en la búsqueda
-- CONTRA: Ocupa más espacio y las modificaciones e inserciones tardan más
-- COn la consideración de que hoy en día no vamos cortos de espacio
-- Que el retraso en la inserción es inapreciable
-- Utilizar índices es bastante recomendable

-- ¿Usamos índices para todo? NO
-- Un poco de cabeza:
-- Si tengo consultas que tardan mucho tengo que evaluar usar índices
-- Si yo sé que se realizan muchas consultas sobre ciertos campos
-- Puedo curarme en salud y usar índices
-- Los campos clave son siempre índices
-- Las claves extrangeras son siempre índices
-- Cuando nunca usar índices
-- Cuando tenemos campos con valores que se repiten mucho

-- Podemos tener índices combinados de varios campos
-- No son frecuentes, pero se usan cuando la búsqueda se hace por varios
-- campos a la vez y a lo mejor individualmente, como he dicho antes
-- se repiten mucho

-- Cuando yo creo un índice por defecto se entiende que los valores se pueden repetir
-- Pero puedo crear índices únicos con valores que no se repiten

-- Resumiendo: los índices se crean para acelerar búsquedas, usamos el prefijo
-- ix_ o idx_ tenemos la posibilidad de que sean únicos

https://www.w3schools.com/sql/sql_create_index.asp

-- 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';

-- Duplicar tablas o copiar datos en una tabla

-- Con esta sentencia creamos una tabla nueva con la misma estructura que otra
-- Copiando los datos
CREATE TABLE nueva_tabla SELECT * FROM actor;

-- Con esta sentencia copiamos los datos de una tabla en otra ya existente
insert into nueva_tabla (first_name, last_name)
select first_name,last_name from actor;

-- A veces no creamos una vista sino una tabla porque queremos 'congelar'
-- Un instante de tiempo o si el cálculo es muy costoso y quiero
-- realizar consultas sobre ese resultado me sale a cuenta hacer
-- el cálculo una vez y consultar muchas

create table total_pagos
select customer.*, sum(amount) pagos from customer
join payment on customer.customer_id=payment.customer_id
group by customer_id;

-- Consultas de union
-- Nos permiten mezclar datos de tablas diferentes
-- Si hay elementos repetidos los elimina
-- Si quiero incluir los repetidos uso UNION ALL
select first_name, last_name, 'actor' type from actor
union 
select first_name, last_name, 'customer' type from customer
union 

-- Puedo incluir cualquier valor siempre y cuando tengamos el mismo número de campos
-- Pero que pueda no quiere decir que deba, esto no tiene ningún sentido
select district, phone, 'address' type from address
order by first_name,last_name;


select title,first_name,last_name from film left join film_actor on film.film_id=film_actor.film_id
left join actor on film_actor.actor_id=actor.actor_id
union 
select title,first_name,last_name from film right join film_actor on film.film_id=film_actor.film_id
right join actor on film_actor.actor_id=actor.actor_id;

-- Clientes de España o Argentina

select customer.* from country join city on country.country_id=city.country_id
join address on city.city_id=address.city_id
join customer on address.address_id=customer.address_id
where country in ('spain','argentina');

-- Ventas totales por empleado

select staff.*, sum(amount) total from staff 
join payment on staff.staff_id=payment.staff_id
group by staff.staff_id;

-- Películas en las que han trabajado más de 10 actores

select film.*, count(actor_id) actores from film 
join film_actor on film.film_id=film_actor.film_id
group by film_id
having actores>10;

-- El título de la película que más se ha alquilado (en número de alquileres)

-- Solución decente pero no correcta
-- ¿Por qué? Porque si hay varias películas que tengan el mismo número
-- de alquileres solo saldrá una
select title, count(rental_id) alquileres from film 
join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
group by film.film_id
order by alquileres desc
limit 1;

-- ¿Cual sería la solución correcta?
-- Buscar el máximo de alquileres
-- Selecciona las películas que tengan ese número de alquileres

select title, count(rental_id) alquileres from film 
join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
group by film.film_id
having alquileres=(
	select count(rental_id) total from film 
	join inventory on film.film_id=inventory.film_id
	join rental on inventory.inventory_id=rental.inventory_id
	group by film.film_id
	order by total desc
	limit 1);
    
-- El título de la película que más dinero ha dado (en suma de importe)

-- Solución decente pero no del todo correcta
select title, sum(amount) importe from film 
join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
join payment on rental.rental_id=payment.rental_id
group by film.film_id
order by importe desc
limit 1;

-- SOlución teniendo en cuenta empates

select title, sum(amount) importe from film 
join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
join payment on rental.rental_id=payment.rental_id
group by film.film_id
having importe=(
	select sum(amount) importe from film 
	join inventory on film.film_id=inventory.film_id
	join rental on inventory.inventory_id=rental.inventory_id
	join payment on rental.rental_id=payment.rental_id
	group by film.film_id
	order by importe desc
	limit 1);
    
-- Los 5 actores que han trabajado en menos películas

select actor.*, count(film_id) peliculas from actor 
join film_actor on actor.actor_id=film_actor.actor_id
group by actor_id
order by peliculas
limit 5;

-- Posible otra solución también imperfecta pero quizá más correcta

select actor.*, count(film_id) peliculas from actor 
join film_actor on actor.actor_id=film_actor.actor_id
group by actor.actor_id
having peliculas<=(
	select max(peliculas) from (
		select count(film_id) peliculas from actor 
		join film_actor on actor.actor_id=film_actor.actor_id
		group by actor.actor_id
		order by peliculas
		limit 5) as temp
        );
        
-- Encontrar los clientes que hayan gastado más de 100 dólares

select customer.*, sum(amount) total from customer 
join payment on customer.customer_id=payment.customer_id
group by customer_id
having total>100;

-- Actores que no hayan trabajado en películas para niños o familiares

-- Consulta de división: primero miro los que sí y luego los que no
select * from actor
where actor_id not in (
	select actor_id from category
	join film_category on category.category_id=film_category.category_id
	join film on film_category.film_id=film.film_id
	join film_actor on film.film_id=film_actor.film_id
	where name in ('children','family'));

-- Actores que hayan trabajado en películas que no se hayan alquilado nunca

select distinct actor.* from actor join film_actor on actor.actor_id=film_actor.actor_id
where film_id not in (
select film.film_id from film join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id);

select distinct actor.* from actor 
join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id
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;

-- Clientes que han hecho un total de pagos por encima de la media.
-- Divide y vencerás
-- Calculo el total de pagos po cliente
select customer.*, sum(amount) pagos from customer
join payment on customer.customer_id=payment.customer_id
group by customer_id;
-- calculo la media sobre esta consulta
select avg(pagos) media from (
select customer.*, sum(amount) pagos from customer
join payment on customer.customer_id=payment.customer_id
group by customer_id) as temp;
-- filtro los clientes cuyo total esté por encima de la media
select customer.*, sum(amount) pagos from customer
join payment on customer.customer_id=payment.customer_id
group by customer_id
having pagos>(select avg(pagos) media from (
	select customer.*, sum(amount) pagos from customer
	join payment on customer.customer_id=payment.customer_id
	group by customer_id) as temp);

-- con vistas
create view pagos_por_cliente as
select customer.*, sum(amount) pagos from customer
join payment on customer.customer_id=payment.customer_id
group by customer_id;

select * from pagos_por_cliente
where pagos>(select avg(pagos) media from pagos_por_cliente);

-- Clientes que no han alquilado documentales (‘documentary’)
-- Primero los que alquilan

select * 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='documentary';

-- Pues esos no, los otros
select * 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='documentary');

-- Vistas: Es una manera de dar nombre a una consulta
-- Y nos permite utilizarla como si fuera una tabla más

select film.*, name categoria from
category join film_category on category.category_id=film_category.category_id
join film on film_category.film_id=film.film_id;

-- Utilizo mucho la consulta anterior porque hago consultas del tipo
-- Películas de una categoría, categorías con películas de una duración
-- Categorías de películas con un rating

select film.*, name categoria from
category join film_category on category.category_id=film_category.category_id
join film on film_category.film_id=film.film_id
where name='children';

-- Lo que nos proporciona el SQL son las vistas
-- Creo una vista llamada 'peliculas' que incorpore lo anterior
-- CREATE VIEW

select * from peliculas
where categoria='children';

select categoria,count(film_id) total
from peliculas
group by categoria;

-- Actores que han trabajado en la categoría children
select distinct first_name, last_name
from peliculas
where name='children';

-- Películas por actor
select first_name, last_name, count(film_id) total
from peliculas
group by first_name, last_name;

select * from actor
where actor_id not in (select actor_id from
peliculas where name='children');

-- Con SQL

create view alquileres as 
select distinct country from country
join city on country.country_id=city.country_id
join address on city.city_id=address.city_id
join customer on address.address_id=customer.address_id
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_category on film.film_id=film_category.film_id
join category on film_category.category_id=category.category_id;

-- Consultas de división
-- Son consultas en las que tenemos que buscar información
-- en 'negativo'. Es decir, registros de una tabla que no
-- tengan relación con registros de otra tabla

-- ¿Hay algún actor que no haya trabajado en películas de comedia?

-- La consulta siguiente NO nos responde la pregunta
-- Nos dice los actores que han trabajado en películas que no son comedia
select first_name,last_name,name from actor
join film_actor using(actor_id)
join film using(film_id)
join film_category using(film_id)
join category using(category_id)
order by actor.actor_id;
-- where name<>'comedy';

-- ¿Cual es el enfoque correcto?
-- Darle la vuelta a la pregunta

-- QUé actores sí que han trabajado en comedia
select first_name,last_name,name 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';

-- Uso la consulta como subconsulta y digo todos los actores que no están
-- en la consulta anterior

select * from actor
where actor_id not in (select actor_id 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');

-- ¿Hay actores que no han trabajado en películas de rating 'R'

-- Esta consulta, igual que antes, no nos responde la pregunta
-- Nos dice qué actores han trabajado en películas que no son de rating 'R'
-- Eso no es lo que nos están preguntando

select * from actor
join film_actor using(actor_id)
join film using(film_id)
where rating<>'R';

-- Le damos la vuelta a la pregunta ¿Qué actores SÍ que han trabajado
-- en películas de rating 'R'
select * from actor
join film_actor using(actor_id)
join film using(film_id)
where rating='R';

-- Una vez yo sé qué actores han trabajado en películas de rating R
-- Los descarto, esos no
select * from actor
where actor_id not in (
select actor_id from actor
join film_actor using(actor_id)
join film using(film_id)
where rating='R'
);