Mes: junio 2024

-- Vistas: Es una manera de dar nombre a una consulta
-- Y nos permite utilizarla como si fuera una tabla más

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

-- Utilizo mucho la consulta anterior porque hago consultas del tipo
-- Películas de una categoría, categorías con películas de una duración
-- Categorías de películas con un rating

select film.*, name categoria 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='children';

-- Lo que nos proporciona el SQL son las vistas
-- Creo una vista llamada 'peliculas' que incorpore lo anterior
-- CREATE VIEW

select * from peliculas
where categoria='children';

select categoria,count(film_id) total
from peliculas
group by categoria;

-- Actores que han trabajado en la categoría children
select distinct first_name, last_name
from peliculas
where name='children';

-- Películas por actor
select first_name, last_name, count(film_id) total
from peliculas
group by first_name, last_name;

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

-- Con SQL

create view alquileres as 
select distinct country 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
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_category on film.film_id=film_category.film_id
join category on film_category.category_id=category.category_id;

-- Consultas de división
-- Son consultas en las que tenemos que buscar información
-- en 'negativo'. Es decir, registros de una tabla que no
-- tengan relación con registros de otra tabla

-- ¿Hay algún actor que no haya trabajado en películas de comedia?

-- La consulta siguiente NO nos responde la pregunta
-- Nos dice los actores que han trabajado en películas que no son comedia
select first_name,last_name,name from actor
join film_actor using(actor_id)
join film using(film_id)
join film_category using(film_id)
join category using(category_id)
order by actor.actor_id;
-- where name<>'comedy';

-- ¿Cual es el enfoque correcto?
-- Darle la vuelta a la pregunta

-- QUé actores sí que han trabajado en comedia
select first_name,last_name,name 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';

-- Uso la consulta como subconsulta y digo todos los actores que no están
-- en la consulta anterior

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

-- ¿Hay actores que no han trabajado en películas de rating 'R'

-- Esta consulta, igual que antes, no nos responde la pregunta
-- Nos dice qué actores han trabajado en películas que no son de rating 'R'
-- Eso no es lo que nos están preguntando

select * from actor
join film_actor using(actor_id)
join film using(film_id)
where rating<>'R';

-- Le damos la vuelta a la pregunta ¿Qué actores SÍ que han trabajado
-- en películas de rating 'R'
select * from actor
join film_actor using(actor_id)
join film using(film_id)
where rating='R';

-- Una vez yo sé qué actores han trabajado en películas de rating R
-- Los descarto, esos no
select * from actor
where actor_id not in (
select actor_id from actor
join film_actor using(actor_id)
join film using(film_id)
where rating='R'
);

-- SUBCONSULTAS

-- Poner consultas detro de otras consultas
-- Se pueden poner en el WHERE, en el FROM y como campos

-- El importe máximo de un pago en mi tabla de pagos
select max(amount) from payment;

-- Quiero saber los clientes que han hecho pagos por la cantidad máxima
-- ¿Como lo puedo hacer? Usando una subconsulta
-- En el where (y también puede ser en el having)
-- No utilizo un valor fijo sino que uso otra consulta

select distinct first_name, last_name from customer
join payment on customer.customer_id=payment.customer_id
where amount=(select max(amount) from payment);

-- Puedo usar una subconsulta como un campo
select distinct first_name, last_name, 
(select count(*) from rental where customer.customer_id=rental.customer_id) alquileres
from customer;

-- Puedo usar una subconsulta como 'tabla virtual'
-- Es decir, yo creo una consulta y puedo seleccionar datos dentro de esa consulta
-- Por ejemplo ¿Cual es la media de alquileres por cliente?

-- Primero hago la consulta que me da el total de alquileres por cliente
select first_name,last_name, count(rental_id)  alquileres from customer join rental on customer.customer_id=rental.customer_id
group by customer.customer_id;

-- Después uso esa consulta como una subconsulta (subquery)

select avg(alquileres) from 
(select  count(rental_id)  alquileres from customer join rental on customer.customer_id=rental.customer_id
group by customer.customer_id)  temp;

-- ¿Qué clientes tienen alquileres por encima de la media?

select first_name,last_name, count(rental_id)  alquileres from customer join rental on customer.customer_id=rental.customer_id
group by customer.customer_id
having alquileres>(select avg(alquileres) from 
(select  count(rental_id)  alquileres from customer join rental on customer.customer_id=rental.customer_id
group by customer.customer_id)  temp);

-- Que clientes han pagado por encima de la media

-- Calcular el total de pago por cliente
select first_name, last_name, sum(amount) total
from customer
join payment on customer.customer_id=payment.customer_id
group by customer.customer_id;

-- COn esto puedo calcular la media
select avg(total) media from (
select sum(amount) total
from customer
join payment on customer.customer_id=payment.customer_id
group by customer.customer_id) temp;

-- después obtengo los clientes que superan esa media
select first_name, last_name, sum(amount) total
from customer
join payment on customer.customer_id=payment.customer_id
group by customer.customer_id
having total>(select avg(total) media from (
select sum(amount) total
from customer
join payment on customer.customer_id=payment.customer_id
group by customer.customer_id) temp);

-- Concatenar valores de cadena
-- GROUP_CONCAT

select first_name,last_name,group_concat(title) peliculas
from actor
join film_actor on actor.actor_id=film_actor.actor_id
join film on film.film_id=film_actor.film_id
group by actor.actor_id;

-- Puedo ordenar los valores
select first_name,last_name,group_concat(title ORDER BY title) peliculas
from actor
join film_actor on actor.actor_id=film_actor.actor_id
join film on film.film_id=film_actor.film_id
group by actor.actor_id;

-- Puedo cambiar el separador
select first_name,last_name,group_concat(title ORDER BY title SEPARATOR ' | ') peliculas
from actor
join film_actor on actor.actor_id=film_actor.actor_id
join film on film.film_id=film_actor.film_id
group by actor.actor_id;

-- El cliente que más gasta

select customer.*, sum(amount) gasto 
from customer 
join payment on customer.customer_id=payment.customer_id
group by customer.customer_id
order by gasto desc
limit 1; 

-- el segundo que más gasta

select customer.*, sum(amount) gasto 
from customer 
join payment on customer.customer_id=payment.customer_id
group by customer.customer_id
order by gasto desc
limit 1,1; 

-- Paginación: limit 0,10   limit 10,10     limit 20,10
-- Se suele hacer desde el programa

select customer.*, sum(amount) gasto 
from customer 
join payment on customer.customer_id=payment.customer_id
group by customer.customer_id
order by gasto desc
limit 0,10; 

select customer.*, sum(amount) gasto 
from customer 
join payment on customer.customer_id=payment.customer_id
group by customer.customer_id
order by gasto desc
limit 10,10; 

select customer.*, sum(amount) gasto 
from customer 
join payment on customer.customer_id=payment.customer_id
group by customer.customer_id
order by gasto desc
limit 20,10; 

-- Mostrar los clientes ordenados por gasto total descendente
-- Total de gasto por cliente

select customer.*, sum(amount) gasto 
from customer 
join payment on customer.customer_id=payment.customer_id
group by customer.customer_id
order by gasto desc;

-- Mostrar los países que tengan menos de 10 clientes (94)
-- country - city - address - customer
-- contar
-- agrupado por pais
select country, count(customer_id) clientes 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 clientes<10;

-- Mostrar las películas que tengan 3 categorías o más
-- film - film_category
-- contar
-- agrupo por film
select film.*,count(category_id) categorias from film
join film_category on film.film_id=film_category.film_id
group by film.film_id
having categorias>=3;

-- Mostrar los actores que han trabajado en más de 20 películas (181)
-- actor - film_actor
-- contar
-- agrupar por actor
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>20;

-- Mostrar los actores que han trabajado en 5 o más películas de acción (5)
-- actor - film_actor - film - film_category - category
-- contar
-- agrupar por actor
select actor.*, count(film.film_id) peliculas 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='Action'
group by actor.actor_id
having peliculas>=5;

-- Los clientes que han gastado más de 150 dolares
-- En una consulta normal para filtrar uso WHERE
-- En una consulta agrupada uso HAVING

select first_name,last_name,sum(amount) total, avg(amount) media, min(amount) minimo,max(amount) maximo
 from customer join payment on customer.customer_id=payment.customer_id
 group by customer.customer_id
 HAVING total>=150 -- Ponemos una condición sobre los totales
 order by first_name,last_name;
 
 select first_name,last_name,sum(amount) total, avg(amount) media, min(amount) minimo,max(amount) maximo
 from customer join payment on customer.customer_id=payment.customer_id
 group by customer.customer_id
 HAVING total>=150 and media>5 -- Condición compuesta
 order by first_name,last_name;
 
 -- Resumiendo, HAVING nos permite poner condiciones sobre los totales
 -- de las funciones de agregado
 
 -- ¡OJO! HAVING es como un WHERE para los totales
 -- No podemos usar WHERE para los totales ni HAVING para los campos
 
  select first_name,last_name,sum(amount) total, avg(amount) media, min(amount) minimo,max(amount) maximo
 from customer join payment on customer.customer_id=payment.customer_id
 WHERE total>=150 -- Da error columna no encontrada
 group by customer.customer_id
 order by first_name,last_name;
 
  select first_name,last_name,sum(amount) total, avg(amount) media, min(amount) minimo,max(amount) maximo
 from customer join payment on customer.customer_id=payment.customer_id
 group by customer.customer_id
 HAVING amount<9 -- Da el mismo tipo de error 
 order by first_name,last_name;
 
 -- Esto no quiere decir que no podamos tener condiciones compuestas
 -- que usen WHERE y HAVING
 
 -- Quiero saber el total, media, etc de los clientes cuyo nombre
 -- empieza por 'A' y el total es mayor de 150
 -- Como construyo esto: pongo el select group by
 
select first_name,last_name,sum(amount) total, avg(amount) media, min(amount) minimo,max(amount) maximo
 from customer join payment on customer.customer_id=payment.customer_id
  group by customer.customer_id
 order by first_name,last_name;
 
 -- La condiciones de las columnas no calculadas con WHERE
 
  select first_name,last_name,sum(amount) total, avg(amount) media, min(amount) minimo,max(amount) maximo
 from customer join payment on customer.customer_id=payment.customer_id
 WHERE first_name like 'a%'
 group by customer.customer_id
 order by first_name,last_name;
 
 -- La condiciones de las columnas  calculadas con HAVING
 
  select first_name,last_name,sum(amount) total, avg(amount) media, min(amount) minimo,max(amount) maximo
 from customer join payment on customer.customer_id=payment.customer_id
 WHERE first_name like 'a%'
 group by customer.customer_id
 HAVING total>150
 order by first_name,last_name;
 

-- Actores con mayor número de películas
-- Total de películas por actor

-- Primero me hago mi sentencia sql normal
select * from actor join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id;

-- Segundo paso: ¿Qué tengo que hacer, sumar,contar...?
-- En este caso voy a contar (COUNT)
select first_name,last_name, count(film.film_id) total from actor join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id;

-- Tercer paso: ¿Por qué valor agrupo?
-- Agrupamos por actor que es de quien queremos saber el número de películas
select first_name,last_name, count(film.film_id) total from actor join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id
group by actor.actor_id;

-- Cuarto paso: ¿Necesito ordenar por algo?
-- ordeno por cantidad de películas descendente
select first_name,last_name, count(film.film_id) total from actor join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id
group by actor.actor_id -- Siempre que tenemos joins el campo id puede estar en varias tablas, necesitamos indicarle la tabla que queremos
order by total desc;

-- Las funciones de agregado tienen sentido cuando agrupamos
-- los valores: GROUP BY
-- La sintaxis es select valor, agregado(..) from tabla1  join tabla2 GROUP BY valor

-- Cuantas ciudades tiene cada pais
select country,count(city) ciudades
from country join city on country.country_id=city.country_id
group by country;

-- Total de pagos por cliente

select first_name,last_name,sum(amount) total, avg(amount) media, min(amount) minimo,max(amount) maximo
 from customer join payment on customer.customer_id=payment.customer_id
 group by customer.customer_id
 order by first_name,last_name;
 
 -- Total de películas por categoría
 select name,count(film_id) total from category 
 join film_category on category.category_id=film_category.category_id
 group by category.category_id
 order by total desc;