Trifulcas

-- Si el case os permite evaluar varias opciones
-- El if solamente una condición y valor si cierto, valor si falso
-- Se parece mucho al operador ternario
-- if(condicion,valor_si_cierto,valor_si_falso)

select title, if(length>120,'Tostón','Entretenida') tipo
from film;

select first_name,last_name, 
if(right(first_name,1)=right(last_name,1),'SI','NO') final
from actor;

-- Tenemos dos funciones que nos permiten detectar nulos
-- Porque al concatenar con nulos se 'contagian'

select address,address2,postal_code,concat(address,address2,postal_code) dir
from address;

select 1+null+99;

-- Para solucionar esto tenemos
-- ifnull(valor, valor si nulo)

select address,address2,ifnull(postal_code,'NO POSTAL CODE') postal_code,concat(address,address2,ifnull(postal_code,'')) dir
from address;

select 1+ifnull(null,0)+99;

-- Podemos utilizar COALESCE
-- Es más genérico, le paso una lista de valores y me devuelve el primero que no es nulo

select address,address2,coalesce(postal_code,'NO POSTAL CODE') postal_code,concat(address,address2,coalesce(postal_code,'')) dir
from address;

select 1+coalesce(null,null,null,0)+99;

https://dev.mysql.com/doc/refman/8.0/en/flow-control-functions.html

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

-- Crear una consulta que me 
-- muestre las iniciales de los clientes en formato 'A.D.'

select concat_ws('. ',substring(first_name,1,1),substring(last_name,1,1),'') nombre
from customer;

select concat(substring(first_name,1,1),'. ',substring(last_name,1,1),'.') nombre
from customer;

select concat(left(first_name,1),'. ',left(last_name,1),'.') nombre
from customer;

-- Crear una consulta que me muestre los paises que tengan una ciudad que empiece por la misma letra que el país.

select distinct country from country
join city using(country_id)
where substring(country,1,1)=substring(city,1,1);

-- Crear una consulta que me muestre todos los nombres de actores que
-- contengan la letra z pero sin la letra z es decir si tengo un actor gomez me mostrará gome

select replace(first_name,'Z','') nombre, replace(last_name,'Z','') apellido from actor
where first_name like '%z%' or last_name like '%z%';

select replace(upper(first_name),'Z','') nombre, replace(upper(last_name),'Z','') apellido from actor
where first_name like '%z%' or last_name like '%z%';

-- Crear una consulta que me muestre el título de la película y 'barata' si su replacement_const es menor de 15,
-- 'cara' si es mayor de 25 y 'media' en cualquier otro caso.

select title,
 case
	when replacement_cost<15 then 'barata'
	when replacement_cost>25 then 'cara'
	else 'media'
	end tipo
 from film;
 
 -- Crear una consulta que me muestre el total de alquileres de las películas
 -- y me lo clasifique en 3 tramos 'alto', 'medio' y 'bajo' dependiendo de la
 -- cantidad de veces que se ha alquilado. Pensad bien que valores vamos a usar como límites
 
 select title, case
 when count(rental_id) <15 then 'bajo'
 when count(rental_id) <25 then 'medio'
 else 'alto'
 end tipo
 from film join inventory using(film_id)
 join rental using(inventory_id)
 group by film_id;
 
 -- Que pasa si queremos que sea dinámico, es decir, que si tengo más alquileres
 -- el rango cambie
 
 SELECT 
    COUNT(ren.`rental_id`) AS total_rental, 
    fil.`title`,
    CASE 
		WHEN COUNT(ren.`rental_id`) > (max_rental / 3) * 2 THEN 'Alto'
		WHEN COUNT(ren.`rental_id`) > (max_rental / 3) THEN 'Medio'
        ELSE 'Bajo'
    END AS type_rental,
    max_rental    
FROM `rental` ren
JOIN `inventory` inv ON ren.`inventory_id` = inv.`inventory_id`
JOIN `film` fil ON inv.`film_id` = fil.`film_id`
JOIN (
    SELECT 
        MAX(count_total) AS max_rental
    FROM (
        SELECT COUNT(ren.`rental_id`) AS count_total
        FROM `rental` ren
        JOIN `inventory` inv ON ren.`inventory_id` = inv.`inventory_id`
        JOIN `film` fil ON inv.`film_id` = fil.`film_id`
        GROUP BY inv.`film_id`
    ) AS total_rental_tmp
) AS max_rental_tmp
GROUP BY inv.`film_id`
ORDER BY total_rental DESC;

-- Otra manera de hacerlo

create view alquileres_por_pelicula as
 SELECT fil.film_id,title, COUNT(ren.`rental_id`) AS total
        FROM `rental` ren
        JOIN `inventory` inv ON ren.`inventory_id` = inv.`inventory_id`
        JOIN `film` fil ON inv.`film_id` = fil.`film_id`
        GROUP BY fil.`film_id`;
        
-- con esta vista creada la cosa se quedda así:
select title, CASE 
		WHEN total > ((select max(total) from alquileres_por_pelicula) / 3) * 2 THEN 'Alto'
		WHEN total > ((select max(total) from alquileres_por_pelicula) / 3) THEN 'Medio'
        ELSE 'Bajo'
    END AS type_rental
    from alquileres_por_pelicula;

-- Funciones de control de flujo
-- Tienen un comportamiento similar al if de programación
-- Sirven para devolver valores dependiendo de condiciones

-- CASE sirve para evaluar múltiples valores de un campo
-- O múltiples condiciones

-- CASE value WHEN compare_value THEN result 
-- [WHEN compare_value THEN result ...] [ELSE result] END

select case rating 
	when 'g' then 'Para todos los públicos'
	when 'r' then 'Para mayores de edad'
	when 'pg' then 'menores acompañados'
	else 'No lo se'
end clasificacion
from film;

-- CASE WHEN condition THEN result 
-- [WHEN condition THEN result ...] [ELSE result] END

select case 
	when length<90 then 'Corta'
	when length<120 then 'Media'
	else 'Larga'
end duracion
from film;

select first_name, case
	when first_name like '%a%' then 'A'
	when first_name like '%e%' then 'E'
	when first_name like '%o%' then 'O'
	else 'IU'
end letra
from actor;

select first_name
from actor
order by  case
	when first_name like '%a%' then 'A'
	when first_name like '%e%' then 'E'
	when first_name like '%o%' then 'O'
	else 'IU'
end;

select first_name
from actor
where  case
	when first_name like '%a%' then 'A'
	when first_name like '%e%' then 'E'
	when first_name like '%o%' then 'O'
	else 'IU'
end='A';

select title, case
	when rating='R' and length>120 then 'Adultos intelectuales'
    when rating<>'R' and length>120 then 'Todos los públicos con aguante'
    else 'Todos los públicos'
    end tipo
from film;

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