Autor: Juan Pablo Fuentes

SELECT concat(SUBSTRING(first_name,1,2),SUBSTRING(last_name,1,2)) ref from actor;
SELECT LENGTH('MySQL') AS longitud;
SELECT UPPER('mysql') AS mayusculas, LOWER('MySQL') AS minusculas;
select concat(upper(substring(first_name,1,1)),
lower(substring(first_name,2))) nombre from actor;
SELECT REPLACE('Aprender SQL', 'SQL', 'MySQL') AS resultado;

update film set description= replace(description,'Crocodile','Cocodrilo') ;

SELECT TRIM('   Hola    que   ') AS resultado;

SELECT CONCAT_WS('-', '2023', '10', '26') AS fecha;

select * from actor
where length(first_name)>8;

select count(*) from actor;

select concat('hola',5), '5'+3 foo;

select cast('2021-1-1' as date) fecha;
SELECT STR_TO_DATE('1/1/2023', '%d/%m/%Y');

-- SImilar al if... elseif... else
select first_name,
 CASE
        WHEN length(first_name)<5 THEN 'Nombre corto'
        WHEN length(first_name)<8 THEN 'Nombre medio'
        ELSE 'Nombre largo'
    
END tipo
    from actor;

-- Similar al switch
select rating,
case rating
when 'r' then 'Para adultos'
else  'Todos los publicos'
end tipo
from film;

-- Similar al operador ternario
select if(length(first_name)<5,'corto','largo') longitud from actor;

select coalesce(first_name,'') from actor;

select concat(coalesce(first_name,'.'),coalesce(last_name,''))nombre from actor;

select now(),CURDATE() AS fecha_actual, CURTIME() AS hora_actual;

SELECT EXTRACT(YEAR FROM NOW()) AS año_actual, EXTRACT(MONTH FROM NOW()) AS mes_actual;

-- Imprescindible
select month(now()),year(now()),day(now());

select month(payment_date) from payment;

select * from payment
where month(payment_date) =5;

select year(payment_date) anyo, month(payment_date) mes,sum(amount) total from payment
group by anyo,mes;

select month('1-1-2023');


SELECT month(STR_TO_DATE('1/1/2023', '%d/%m/%Y'));

SELECT DATE_FORMAT(NOW(), '%d/%m/%Y %H:%i %w') AS fecha_formateada;

-- Alquileres en fin de semana

select count(rental_id) from rental
where date_format(rental_date,'%w') in (0,6);

SELECT ADDDATE(NOW(), 7) AS fecha_en_7_dias, SUBDATE(NOW(), 3) p;

SELECT DATEDIFF(NOW(), '2023-01-01') AS dias_desde_inicio_de_ano;

SELECT ROUND(3.14159) AS redondeo_entero, ROUND(3.14159, 3) AS redondeo_dos_decimales;

select * from actor order by rand() limit 10;

SELECT SCHEMA() AS nombre_de_base_de_datos;

SELECT concat(address,coalesce(address2,'')) dir FROM sakila.address;

insert into user (name,tarjeta)
values ('tarjeta',AES_ENCRYPT('1112222333434', 'mi_clave_secreta') )

SELECT *, cast(AES_deCRYPT(tarjeta, 'mi_clave_secreta') as char) tarjeta FROM sakila.user

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

-- Películas ‘Épicas’ (Epic) o ‘Brillantes’ (brilliant) (description)
-- que duren más de 180 minutos (length)

SELECT * FROM sakila.film
where (description like '%epic%' or description like '%brilliant%')
and `length`>180;

-- Películas que duren entre 100 y 120 minutos o entre 50 y 70 minutos

SELECT * FROM sakila.film
where length between 100 and 120 or length between 50 and 70;

-- Películas que cuesten 0.99, 2.99 y tengan un rating ‘g’ o ‘r’
-- y que hablen de cocodrilos (crocodile)

SELECT * FROM film
where rental_rate between 0.99 and 2.99 and rating in ('g','r')
and description like '%crocodile%';

-- Direcciones de ontario o de punjab o que su código postal acabe en 5
--  o que su teléfono acabe en 5

SELECT * FROM sakila.address
where district in ('ontario', 'punjab') or postal_code like '%5'
or phone like '%5'
order by district;

-- Ventas totales por empleado

select first_name, last_name, count(payment_id) total, sum(amount) importe
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 title, count(actor.actor_id) total, group_concat(concat(first_name,' ',last_name)) actores from film join film_actor on film.film_id=film_actor.film_id
join actor on actor.actor_id=film_actor.actor_id
group by film.film_id
having total>10
order by title;

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

select title, 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 se ha alquilado de rating 'r'

select title, count(rental_id) total from film join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
where rating='r'
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)

select title, sum(amount) total 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 total desc
limit 1;

-- Los 5 actores que han trabajado en menos películas

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
 order by total
 limit 5;
 
 -- Los 5 actores que han trabajado en menos películas de rating 'r'

select first_name, last_name, count(film_actor.film_id) total
 from actor join film_actor on actor.actor_id=film_actor.actor_id
 join film on film.film_id=film_actor.film_id
 where rating='r'
 group by actor.actor_id
 order by total
 limit 5;
 
 

-- Quiero los actores que NO hayan trabajado en una película de rating 'R'
-- Con subconsultas
-- Cambio la pregunta a positivo: Los que Sí han trabajado en 'R'
-- Con una subconsulta seleccion los que no están en esa lista

select first_name, last_name from actor
where actor_id not in (
select actor.actor_id from actor left join film_actor on actor.actor_id=film_actor.actor_id
left join film on film_actor.film_id=film.film_id
where rating='R');

-- películas que no sean de la categoría 'children'
-- Cambio a positivo: Que si sean de children
select title from film
where film_id not in
(select film.film_id from film join film_category on film.film_id=film_category.film_id
join category on film_category.category_id=category.category_id
where name='children');

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

-- actores que no hayan trabajado en children (con vistas)

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

create view customer_over_avg as
SELECT first_name, last_name, COUNT(*) AS rental_count
FROM rental join customer on rental.customer_id=customer.customer_id
GROUP BY customer.customer_id
HAVING rental_count > (SELECT AVG(rental_count) FROM rental_client);

create view filmografia as
select name, film.*,first_name, last_name from category join film_category on category.category_id=film_category.category_id
join film on film.film_id=film_category.film_id
join film_actor on film.film_id=film_actor.film_id
join actor on film_actor.actor_id=actor.actor_id