Soluciones ejercicios

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