Trifulcas

-- ¿Qué actores no han trabajo en películas de rating 'R'?

-- Los actores que SI han trabajado en películas de rating R
-- Mostrar los que no son esos

select distinct actor_id from actor join film_actor using(actor_id)
join film using(film_id)
where rating ='R';

-- Pues ahora los actores que no están en la anterior consulta

select * from actor
where actor_id not in (
	select distinct actor_id from actor join film_actor using(actor_id)
	join film using(film_id)
	where rating ='R'
)

-- ¿Qué actores no han trabajo en la categoría 'Música'?

-- Los actores que SI han trabajado en películas de categoría Música
-- Mostrar los que no son esos

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

-- Pues ahora los actores que no están en la anterior consulta

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

Películas de la categoría con menos películas

-- Categoría con menos películas

select category_id from category join film_category using(category_id)
group by category_id
order by count(film_id)
limit 1;

-- Películas con categoría

select title,name from film join film_category using(film_id)
join category using(category_id)
where category_id=(
	select category_id from category join film_category using(category_id)
	group by category_id
	order by count(film_id)
	limit 1
);

-- Quiero saber las ciudades del país con más clientes

-- ¿Cual es el país con más clientes?

select country from country join city using(country_id)
		join address using(city_id) join customer using(address_id)
		group by country_id
		order by count(customer_id) desc
		limit 1;
        
-- Entonces buscamos las ciudades cuyo país sea el resultado de la consulta anterior
        
select country,city,count(customer_id) total from country join city using(country_id)
	join address using(city_id) join customer using(address_id)
    where country=(
		select country from country join city using(country_id)
		join address using(city_id) join customer using(address_id)
		group by country_id
		order by count(customer_id) desc
		limit 1
)
	group by country_id, city_id;

-- Que películas están por encima de la media en total de alquileres

create view total_peliculas as
select title, sum(amount) total from
film join inventory using(film_id)
join rental using(inventory_id)
join payment using(rental_id)
group by title;

select avg(total) from total_peliculas;

select * from total_peliculas where total>(
select avg(total) from total_peliculas
);

http://www.nachocabanes.com/sql/curso/sql08.php

http://www.maestrosdelweb.com/tutsql7/

https://www.w3resource.com/mysql/subqueries/index.php

https://guru99.es/sub-queries/

http://www.maestrosdelweb.com/tutsql7/

http://www.techonthenet.com/mysql/triggers/before_insert.php

http://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx

http://www.w3resource.com/mysql/mysql-triggers.php

http://www.sitepoint.com/how-to-create-mysql-triggers/

Ejemplo de desencadenador:

CREATE TABLE `customer_log` (
 `idcustomer_log` int(11) NOT NULL AUTO_INCREMENT,
 `customer_id` int(11) DEFAULT NULL,
 `user` varchar(45) DEFAULT NULL,
 `fecha` datetime DEFAULT NULL,
 `nombre` varchar(45) DEFAULT NULL,
 `nombre_ant` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`idcustomer_log`)
);

DELIMITER //
CREATE TRIGGER cliente_actualizado BEFORE UPDATE ON customer
FOR EACH ROW BEGIN

 DECLARE vUser varchar(50);

 -- Find username of person performing the INSERT into table
 SELECT USER() INTO vUser;

 -- Insert record into audit table
 INSERT INTO customer_log
 ( customer_id,
 fecha,
 user,nombre,nombre_ant)
 VALUES
 ( NEW.customer_id,
 SYSDATE(),
 vUser,NEW.first_name, OLD.first_name );

END;//

DELIMITER ;

CREATE TRIGGER `sakila`.`actor_BEFORE_DELETE` BEFORE DELETE ON `actor` FOR EACH ROW
BEGIN

if OLD.actor_id between 1 and 20 then
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No puedes borrar';
else
 insert into actor_log(first_name, last_name) values (OLD.first_name, OLD.last_name);
end if;
END

CREATE DEFINER=`root`@`localhost` TRIGGER `sakila`.`payment_BEFORE_INSERT` BEFORE INSERT ON `payment` FOR EACH ROW
BEGIN
if NEW.amount>=10 then
 set NEW.amount=9.99;
end if;
END

CREATE DEFINER=`root`@`localhost` TRIGGER `sakila`.`payment_BEFORE_UPDATE` BEFORE UPDATE ON `payment` FOR EACH ROW
BEGIN

if NEW.amount > OLD.amount then
 set NEW.amount=OLD.amount;
end if;

END

Una consulta que me muestre el título de las películas y las categorías a las que pertenece.

insert into film_category values (2,2,null),(1,1,null),(1,2,null);

select title, group_concat(name) from film join film_category using (film_id) join category using(category_id)
group by film_id

https://www.mysqltutorial.org/mysql-group_concat/

Sintaxis:

GROUP_CONCAT(
DISTINCT expression
ORDER BY expression
SEPARATOR sep
);

SELECT first_name,last_name,
group_concat(title order by title separator ' \\ ') films,
count(film.film_id) total
FROM actor join film_actor using(actor_id)
join film using(film_id)
group by actor.actor_id

SELECT title,
group_concat(first_name,' ', last_name
order by first_name,last_name separator ' \\ ') actors,
count(film.film_id) total
FROM actor join film_actor using(actor_id)
join film using(film_id)
group by film_id

SELECT title,
group_concat(concat(first_name,' ', last_name)
order by concat(first_name,' ', last_name) separator ' \\ ') actors,
count(film.film_id) total
FROM actor join film_actor using(actor_id)
join film using(film_id)
group by film_id

select title, group_concat(distinct rental_date order by rental_date desc separator ' - ')
from pagos_peliculas
where month(rental_date)=8
group by film_id;

Vamos a crear una vista pagos_peliculas que relacione la tabla películas con los alquileres y los pagos.

De la tabla películas quiero todos los campos
De la tabla rental la rental_date
De la tabla payment payment_date y amount

Una vez creada esta vista vamos a realizar las siguientes consultas sobre ella:

Total y cantidad de pagos por película
Título de peliculas alquiladas en mayo del 2005
Películas con rating ‘R’ total de pagos.

Si yo me creo una consulta que me agrupa diferentes tablas de mi modelo de datos la tengo disponible para todo tipo de consultas sobre esa relación.
Ejemplo, sin en Sakila creo una vista que una categorías, películas y actores la tengo disponible para usarla sin repetir joins:

create  or replace view cat_film_actor as
select category_id, name,film.*,actor_id,first_name,last_name from category join film_category using(category_id)
join film using(film_id) join film_actor using(film_id) join actor using(actor_id)

A partir de aquí puedo consultar datos de esta vista de una manera mucho más cómoda:

select * from cat_film_actor
where name='Action' and first_name='PENELOPE';

select distinct name from cat_film_actor
where actor_id=1;

select distinct title from cat_film_actor
where name='Action';

Crear una vista de categorías y películas en las que solo salga el nombre de la categoría y el título de la película.
Y otra más total_por_categoria en el que me salga cada categoría y el total de películas de cada una.

create view categorias_peliculas as
select name, title from category join film_category using(category_id)
join film using (film_id);

create view peliculas_por_categoria as
select name, count(*) total from category join film_category using(category_id)
join film using (film_id)
group by category_id