Trifulcas

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

insert into actor (first_name, last_name)
values ('juan','perez');

insert into actor (first_name, last_name)
values ('rosa','pi');

update actor set first_name='PEPE'
where actor_id=10;

update actor set first_name=concat('Actor',actor_id)
where actor_id>200;

delete from actor where actor_id>200;

select country, 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 like 'A%';

select country.country, customer.* from customer join address on customer.address_id=address.address_id
join city on address.city_id=city.city_id
join country on city.country_id=country.country_id
where country like 'a%';

select distinct first_name,last_name from actor join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id
where length>140;

select distinct name from category join film_category on category.category_id=film_category.category_id
join film on film_category.film_id=film.film_id
where 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
group by film.film_id
having total>20;

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

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

select title, name 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 in ('children','family');

select first_name, last_name from actor
where first_name like '%x%' or last_name like '%x%';

select * from address where district='california' and phone like '%274%';

-- El total de ciudades: contar 

select country,count(city) total
from country join city on country.country_id=city.country_id
group by country;

-- Total de importe de clientes

select first_name, last_name, sum(amount) total, avg(amount) media, count(amount) numero,
max(amount) maximo, min(amount) minimo 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;

-- en la consulta anterior los clientes que han gastado más de 100 dolares

select first_name, last_name, sum(amount) total, avg(amount) media, count(amount) numero,
max(amount) maximo, min(amount) minimo 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
having total>100;

-- Los clientes cuyo nombre empieza por 'a' que han gastado más de 100 dolares

select first_name, last_name, sum(amount) total, avg(amount) media, count(amount) numero,
max(amount) maximo, min(amount) minimo from customer join rental on customer.customer_id=rental.customer_id
join payment on rental.rental_id=payment.rental_id
where first_name like 'a%'
group by customer.customer_id
having total>100 and media>4;

-- ¿Cuantos países tienen más de 50 clientes?
-- Nombre del país y número de clientes (count)

select country, count(customer_id) total 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
group by country.country_id
having total>50

-- Para cada país sus ciudades
-- join me está diciendo que relaciono las dos tablas
-- on me dirá mediante que campos están relacionados
select country,city from country join city on country.country_id=city.country_id;

select country,city from country co join city ci on co.country_id=ci.country_id;

-- Solo vale para mysql y el campo se tiene que llamar igual
select country,city from country join city using(country_id);

select country,city,first_name,last_name 
from country join city on country.country_id=city.city_id
join address on city.city_id=address.city_id
join customer on address.address_id=customer.address_id;

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

-- left join nos dice que devolvamos los valores de la tabla de la izquierda
-- Aunque no tengamos valores relacionados

select name,title from category left join film_category 
on category.category_id=film_category.category_id
left join film on film_category.film_id=film.film_id;

-- right join nos dice que devolvamos los valores de la tabla de la derecha
-- Aunque no tengamos valores relacionados

select name,title from category right join film_category 
on category.category_id=film_category.category_id
right join film on film_category.film_id=film.film_id

sakila

INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Nature’, ‘1234-5678’, 1, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Science’, ‘5678-1234’, 2, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘The New England Journal of Medicine’, ‘8765-4321’, 3, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘The Lancet’, ‘4321-8765’, 4, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Cell’, ‘9876-5432’, 5, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Nature Reviews Immunology’, ‘5432-9876’, 6, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Journal of the American Medical Association’, ‘2345-6789’, 7, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Nature Genetics’, ‘6789-2345’, 8, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘The Astrophysical Journal’, ‘3456-7890’, 9, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘IEEE Transactions on Neural Networks and Learning Systems’, ‘7890-3456’, 10, 2023);

DROP TABLE IF EXISTS `revista` ;

CREATE TABLE IF NOT EXISTS `revista` (
`idrevista` INT NOT NULL AUTO_INCREMENT,
`titulo` VARCHAR(100) NOT NULL,
`issn` CHAR(9) NOT NULL,
`numero` INT NOT NULL,
`anyo` YEAR NOT NULL,
PRIMARY KEY (`idrevista`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `articulo` ;

CREATE TABLE IF NOT EXISTS `articulo` (
`idarticulo` INT NOT NULL AUTO_INCREMENT,
`titulo` VARCHAR(100) NOT NULL,
`inicio` INT NOT NULL,
`final` INT NOT NULL,
`idrevista` INT NOT NULL,
PRIMARY KEY (`idarticulo`),
CONSTRAINT `idrevista`
FOREIGN KEY (`idrevista`)
REFERENCES `revista` (`idrevista`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `idrevista_idx` ON `articulo` (`idrevista` ASC) VISIBLE;

DROP TABLE IF EXISTS `autor` ;

CREATE TABLE IF NOT EXISTS `autor` (
`idautor` INT NOT NULL AUTO_INCREMENT,
`nombre` VARCHAR(100) NOT NULL,
`email` VARCHAR(100) NOT NULL,
`pais` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idautor`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `articulo_autor` ;

CREATE TABLE IF NOT EXISTS `articulo_autor` (
`idarticulo_autor` INT NOT NULL AUTO_INCREMENT,
`idautor` INT NOT NULL,
`idarticulo` INT NOT NULL,
`posicion` INT NOT NULL,
PRIMARY KEY (`idarticulo_autor`),
CONSTRAINT `idarticulo`
FOREIGN KEY (`idarticulo`)
REFERENCES `articulo` (`idarticulo`),
CONSTRAINT `idautor`
FOREIGN KEY (`idautor`)
REFERENCES `autor` (`idautor`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `idarticulo_idx` ON `articulo_autor` (`idarticulo` ASC) VISIBLE;

CREATE INDEX `idautor_idx` ON `articulo_autor` (`idautor` ASC) VISIBLE;