Ejemplos agregados

select first_name, last_name, sum(amount) total ,
 count(amount) numero , avg(amount) media,
 max(amount) maximo , min(amount) minimo
from customer join payment using (customer_id)
group by customer_id
order by numero desc
limit 5 
-- limit solo un número: devuelve las primeras n filas dos numeros: a partir del primer número n filas
-- limit 10: los 10 primeros. limit 10,10 a partir de la fila 10, 10 registros

select first_name, last_name, sum(amount) total ,
 count(amount) numero , avg(amount) media,
 max(amount) maximo , min(amount) minimo
from customer join payment using (customer_id)
where first_name like 'M%' -- Antes de calcular el total
group by customer_id
having total>150 -- Cuando ya se han calculado los totales

-- ¿Cuál es el país con menos ciudades?

select country, count(city_id) total 
from country join city using(country_id)
group by country_id
order by total asc
limit 1;

select country, count(customer_id) total from customer join address using(address_id)
join city using(city_id)
join country using(country_id)
group by country_id
order by total desc

-- Películas y categorías

select * from category join film_category using(category_id)
join film using (film_id);

-- Añado agrupación

select name,count(film_id) from category join film_category using(category_id)
join film using (film_id)
group by category_id;

-- Añado restricción sobre los campos
-- ¿Cuantas películas de cada categoría tienen rating 'G'

select name,count(film_id) total from category join film_category using(category_id)
join film using (film_id)
where rating='G'
group by category_id;

-- Añado restricción sobre el total
-- ¿Cuantas categorías tienen más de 10 películas con rating 'G'?

select name,count(film_id) total from category join film_category using(category_id)
join film using (film_id)
where rating='G'
group by category_id
having total>10

Agregados en Mysql

-- Cuantos hay de algo, cuanto suma algo...
-- ¿Cuantas películas hay por categoría?
-- funciones de agregado: count, sum, min, max, avg...
-- La opción de agrupar por algo GROUP BY

-- Cuantas películas hay por categoría
select name, count(film_id) from category join film_category using(category_id)
join film using (film_id)
group by name;

-- Cuantos actores tienen el mismo nombre
select first_name, count(last_name) total from actor
group by first_name;

-- ¿Cuantos actores trabajan en cada película?

select title, count(actor_id) num_actores from actor join film_actor using(actor_id)
join film using(film_id)
group by title ;

-- ¿Cual es la película con más actores?

select title, count(actor_id) num_actores from actor join film_actor using(actor_id)
join film using(film_id)
group by title
order by num_actores desc 
limit 1;

-- Total de pagos por cliente

select first_name, last_name, sum(amount) total 
from customer join payment using (customer_id)
group by customer_id

Categorías y países

select name,country from category join film_category using (category_id)
join film using (film_id)
join inventory using (film_id)
join rental using (inventory_id)
join customer using (customer_id)
join address using (address_id)
join city using (city_id)
join country using (country_id)
where name='Action' and country='Argentina'

left y right join

-- Relacionar los registros que aparecen en las dos tablas
-- Si mi registro no tiene registros relacionados en la otra tabla
-- NO APARECE

select country,city
from country inner join city using(country_id)
where city like 'T%';

-- ¿Cómo puedo hacer que aparezcan?
-- Cambiar el tipo de join: left, right

select country,city
from country left join city using(country_id)
where country like 'A%';


select country,city
from country right join city using(country_id)
where city like 'T%';

select country,city
from country left join city using(country_id)
where city is null;


select country,city
from country right join city using(country_id)
where country is null

Ejercicios Join

1.- Titulos de películas de acción
2.- Nombres de actores que hayan trabajado en películas de rating ‘G’ o ‘PG’
3.- Nombres de clientes de Argentina
4.- Nombres de actores que hayan trabajado en categoría ‘Children’ o ‘Family’

Ejemplos join

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

Nuestra base de datos es relacional, así que relacionamos las tablas mediante el join:


SELECT country, city, city_id
FROM country join city on country.country_id=city.country_id;

select country,city
from country join city using(country_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;

SELECT name, title FROM category join film_category
using(category_id)
join film using(film_id);

SELECT name, title FROM category join film_category
using(category_id)
join film using(film_id)
where name in ('Family','Children') and title like '%strange%'
and rating='G'

-- ¿Qué actores han trabajado en documentales?

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='Documentary'
order by name, first_name, last_name

Ejercicios operadores básicos

Seleccionar todos los actores cuyo nombre empieze por ‘A’ y el apellido acabe por ‘N’
Seleccionar todos los actores cuyo id esté entre 20 y 70 y el apellido empiece por ‘A’
Seleccionar todas las películas (tabla film) cuyo rental_duration sea 3
Seleccionar todas las películas (tabla film) cuyo rental_duration sea 3 y el title empiece por ‘A’
Seleccionar todas las películas (tabla film) cuyo rental_duration sea 3 y el title empiece por ‘A’ o el rating sea ‘G’
Seleccionar todas las películas cuyo rating sea ‘G’, ‘PG’ o ‘R’

Operador SQL

between: valores en un rango

select * from actor
where actor_id between 20 and 25

in: Valores en una lista

select * from actor

where first_name in (‘ED’,’TED’)

Like: Valores siguiendo un patrón


-- Caracteres comodin: % cualquier cadena _ cualquier caracter
-- Empiecen por
select * from actor
where first_name like 'EL%';
-- Acaben por
select * from actor
where first_name like '%EN';
-- Empiecen y acaben por
select * from actor
where first_name like 'E%N';
-- Contengan
select * from actor
where first_name like '%AN%';

-- El guión bajo encaja con un sólo carácter</pre>
select * from actor
where first_name like '_AN';

select * from actor
where first_name like '%AN';

select * from actor
where first_name like '_E_';

select * from actor
where first_name like 'T_M';