Categoría: BD

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

-- Funciones de agregado: Calcular totales, medias,...

-- Contando el número de actores de la tabla actor (COUNT)
select count(*) total from actor;

-- Sumar valores (SUM)
select sum(amount) total from payment;

-- Obtener una media (AVG)
select avg(amount) media from payment;

-- Obtener máximo y mínimo (MIN,MAX)
select min(amount) minimo, max(amount) maximo from payment;

https://www.w3schools.com/sql/sql_aggregate_functions.asp

-- En qué paises se han alquilado películas de comedia
-- ¿En que tablas está la información?
-- Pais en country
-- comedia en category
-- Pues tengo que buscar el camino:
-- country--city--address--customer--rental--inventory--film--film_category--category

-- Formato estándar
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
where name='comedy'
order by country;

-- optimizado para mysql

select * from country join city using (country_id)
join address using (city_id)
join customer using (address_id)
join rental using (customer_id)
join inventory using (inventory_id)
join film using (film_id)
join film_category using (film_id)
join category using (category_id)
where name='comedy'
order by country;

-- Formato estándar usando alias

select distinct country from country c
join city ci on c.country_id=ci.country_id
join address a on ci.city_id=a.city_id
join customer cu on a.address_id=cu.address_id
join rental r on cu.customer_id=r.customer_id
join inventory i on r.inventory_id=i.inventory_id
join film f on i.film_id=f.film_id
join film_category fc on f.film_id=fc.film_id
join category ca on fc.category_id=ca.category_id
where name='comedy'
order by country;

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

select * from film where length >= 100 and length<=120
OR length between 50 and 70
order by length;

-- Buscar todos los clientes (customer) de paises que empiecen por ‘A’ (27)

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

-- Buscar todos los actores que hayan trabajado en películas de una longitud (length) mayor de 140 (200)

select distinct actor.* 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;


-- Categorías con películas de rating ‘R’ (solo el nombre) (16)

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


-- Películas para niños (children) o familiares (Family) (129)

select distinct title 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' or name='family';

-- Relacionamos los datos de diferentes tablas con JOIN
-- Lo que hacemos es seguir el camino de las relaciones
-- Que nos vienen del modelo ER
-- Estas relaciones se implementan con los ids
-- Por lo que para hacer una consulta que relacione varias tablas hacemos:
-- 1) Identificar en que tablas tenemos la información que queremos
-- 2) Ver el camino entre esas tablas
-- 3) Identificar los ids de esas relaciones
-- Ejemplo: Direcciones de España
-- 1) La info la tengo en country y address
-- 2) El camino es country--city--address
-- 3) country-city: country_id  city-address: city_id
select * from country join city on country.country_id=city.country_id
join address on city.city_id=address.city_id;

-- El JOIN es fundamental dentro de las consultas de las bases de datos
-- Porque muy pocas veces querremos información de solo una tabla

-- A los campos y tablas les puedo poner alias
select country pais, address direccion from country c join city ci on c.country_id=ci.country_id
join address a on ci.city_id=a.city_id;

-- Generalmente para modificar el nombre que vemos de los campos y en el caso
-- de las tablas simplificar los joins

-- JOIN me muestra los registros que están relacionados, lo que no tienen
-- relación no salen. Si hay paises que no tienen ciudades o actores que
-- no tienen películas no saldrán en el JOIN
-- Para eso tenemos que usar LEFT o RIGHT para obtener TODOS los registros
-- de la tabla que está a la derecha o a la izquierda aunque
-- no tenga registros relacionados

select * from country left join city on country.country_id=city.country_id;

-- En la consulta anterior salen todos los paises incluyendo aquellos
-- que no tengan ciudades

-- JOIN por defecto solo nos muestra valores que están relacionados
-- Si tenemos registros que no tienen elementos relacionados no salen
-- Si yo quiero que sí que salgan utilizo LEFT / RIGHT
-- Ejemplo: Todos los países que no tengan ciudades
SELECT country, city FROM country 
left join city on country.country_id=city.country_id
where city is null;

select country,city from city 
right join country on country.country_id=city.country_id;

-- Actores que no han trabajado en ninguna película
select * from actor left join film_actor on actor.actor_id=film_actor.actor_id
where actor.actor_id is null;

-- Los ejemplos anteriores solo funcionan si introducimos datos nuevos
-- Porque la BD de Sakila está cuadrada, no hay 'huecos'
INSERT INTO actor (first_name,last_name) values ('Ana','Pi')

https://www.w3schools.com/sql/sql_join_left.asp

-- Alias: a los campos y a las tablas les podemos poner alias

select first_name nombre, last_name apellidos from actor;

select first_name as nombre, last_name as apellidos from actor;

-- Se usa mucho en las tablas para simplificar los joins

select name categoria, title pelicula from
category c join film_category fc on c.category_id=fc.category_id
join film f on fc.film_id=f.film_id;

https://www.w3schools.com/sql/sql_alias.asp

-- La sintaxis es tabla1 JOIN tabla2 ON tabla1.id=tabla2.id

-- Un join de una relación 1 a N
select country,city from country JOIN city ON country.country_id=city.country_id;

-- Esta sintaxis no es standard del sql, solo sirve para mysql si
-- el nombre del id es igual en las dos tablas
select country,city from country JOIN city using(country_id);

-- Un join de una relación N a N
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;

-- Sintaxis alternativa
select name,title from category join film_category 
using(category_id) join film using(film_id);

-- Quiero ver los clientes de Spain
select * 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='spain';

-- Alternativa
select * from country join city using(country_id)
join address using(city_id)
join customer using(address_id)
where country='spain';

-- ¿En qué películas ha trabajado el actor de id 1?

select first_name,last_name,title from actor join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id
where actor.actor_id=1
order by title desc;

select first_name,last_name,title from film join film_actor on film.film_id=film_actor.film_id
join actor on film_actor.actor_id=actor.actor_id
where actor.actor_id=1;

-- Que actores han trabajado en películas de la categoría 'Comedy'
-- Uso DISTINCT para evitar que salgan valores repetidos
-- Si un actor ha trabajado en varias películas saldrá varias veces
-- En order by ordeno por dos campos, first_name y last_name
-- Para que si el nombre sea el mismo en diferentes actores
-- Se ordene por el apellido
select distinct name,first_name, last_name from category join film_category on category.category_id=film_category.category_id
join film on film_category.film_id=film.film_id
join film_actor on film.film_id=film_actor.film_id
join actor on film_actor.actor_id=actor.actor_id
where name='comedy'
order by first_name, last_name;