SELECT * FROM actor
join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id;
-- El alias se utiliza exclusivamente para abreviar
SELECT * FROM actor a
join film_actor fa on a.actor_id=fa.actor_id
join film f on fa.film_id=f.film_id;
SELECT * FROM sakila.actor
join film_actor using(actor_id)
join film using(film_id);
-- Actores sin películas usando left y right
SELECT * FROM sakila.actor
left join film_actor using(actor_id)
left join film using(film_id)
where film_id is null;
select * from film
right join film_actor using(film_id)
right join actor using(actor_id)
where film_id is null;
-- Consultas agrupadas. Nos sirven para calcular totales por
-- algún campo. En general, de tablas relacionadas
-- count, sum, avg, max y min
-- Número de películas total
select count(*) from film;
-- Total de los pagos
select sum(amount) from payment;
-- Número de películas por rating
select rating, count(*) total from film
group by rating;
-- Número de películas por categoría
select name, count(film_id) total from category join film_category using(category_id)
group by category_id;
-- Número de películas por categoría de menor a mayor
select name, count(film_id) total from category join film_category using(category_id)
group by category_id
order by total;
-- Número de películas por categoría de mayor a menor
select name, count(film_id) total from category join film_category using(category_id)
group by category_id
order by total desc;
-- Las dos categorías con más películas
select name, count(film_id) total from category join film_category using(category_id)
group by category_id
order by total desc
limit 2;
-- Dos niveles de condiciones:
-- Sobre los datos de partida con where
-- Sobre los resultados con having
-- Total de películas por actores
select first_name, last_name, count(*) total
from actor join film_actor using(actor_id)
group by actor_id;
-- Total de películas por actores cuyo apellido tiene una 'w'
select first_name, last_name, count(*) total
from actor join film_actor using(actor_id)
where last_name like '%w%'
group by actor_id;
-- Total de películas por actores que tengan más de 30 películas
select first_name, last_name, count(*) total
from actor join film_actor using(actor_id)
group by actor_id
having total>=30;
-- Total de películas por actores cuyo apellido tiene una 'w'
-- con más de 30 películas
select first_name, last_name, count(*) total
from actor join film_actor using(actor_id)
where last_name like '%w%'
group by actor_id
having total>=30
order by first_name, last_name;
-- Total y media de pagos por país
select country, sum(amount) total, avg(amount) media from
country join city using(country_id)
join address using(city_id)
join customer using(address_id)
join payment using(customer_id)
group by country_id
order by total;
-- en programación: if y switch
-- en sql: if y case
-- Case es más flexible que switch. Tenemos el equivalente
-- a switch: evaluar un campo y elegir por valor
select title, rating,
case rating -- evalua el valor de rating y cuando sea alguno de los valores siguientes entra por ahí
when 'G' then 'Para todos los públicos'
when 'PG' then 'Menores acompañados'
when 'R' then 'Para adultos'
else 'Otros'
end tipo -- Alias para que no salga un churro
from film;
-- Otro formato de CASE es con condiciones, más flexible
select title, length,
case -- Cuando se cumpla alguna de las condiciones siguientes entra por ahí
when length<70 then 'Cortita'
when length<130 then 'Normal'
else 'Tostón'
end duracion
from film;
-- Ejemplo combinado
select title, length,
case -- Cuando se cumpla alguna de las condiciones siguientes entra por ahí
when rating='PG' and length<=130 and title like '%Academy%' then 'Academia para mayores tostón'
when length<70 then 'Cortita'
when length<130 then 'Normal'
else 'Tostón'
end duracion,
case rating -- evalua el valor de rating y cuando sea alguno de los valores siguientes entra por ahí
when 'G' then 'Para todos los públicos'
when 'PG' then 'Menores acompañados'
when 'R' then 'Para adultos'
else 'Otros'
end tipo
from film;
-- Si sólo queremos dos opciones true/false usamos IF
select title, if(length<150,'Corta','Larga') duracion
from film;
select round(4.9), round(4.2), round(4.5), -- Redondeo estándar
round(4.123,2), round(4.123,1),
floor(4.9), ceil(4.9),ceil(4.1),truncate(4.9,0),
round(-4.9), floor(-4.9), ceil(-4.9),truncate(-4.9,0),
sign(4.9),sign(-4.9),abs(-4.9);
update actor
set first_name=concat(ucase(left(first_name,1))-- La primera letra del nombre la paso a mayúsculas
,lcase(substr(first_name,2))), -- El resto del nombre lo paso a minúsculas
last_name=concat(ucase(left(last_name,1)),lcase(substr(last_name,2)))
where actor_id>=1
SELECT * FROM sakila.film where length(title)>20;
SELECT curdate() fecha_hoy, curtime() hora_actual,now() fecha_hora_actual,
day(curdate()) dia, month(curdate()) mes, year(curdate()) anyo;
select * from payment where month(payment_date)=5;
select sum(amount) from payment
where month(payment_date)=5 and day(payment_date)=28;
select payment_date, date_add(payment_date, interval 1 day) manyana,
date_add(payment_date, interval 15 minute) cuarto_hora,
date_add(payment_date, interval -7 day) semana_antes
from payment;
select * from payment where
payment_date between date_add('2005-5-25',interval -2 day) and date_add('2005-5-25',interval 2 day)
-- Subconsultas: Cuando lo que queremos averiguar depende
-- De los resultados de otra consulta.
-- Ejemplo sencillo:
-- películas cuya longitud está por encima de la media
select * from film
where length>(select avg(length) media
from film);
-- Las subconsultas se usan muchas veces para averiguar
-- que registros no cumplen una condición
-- Actores que NO han trabajado en una película de acción
-- Esto no funciona porque un actor que haya trabajado en una
-- película que no sea de acción puede haber trabajado en una de acción
select * from cat_film_actor
where name<>'Action';
-- Ir al revés: Buscar los que SI han trabajado y mostrar
-- los que no son esos
select actor_id from cat_film_actor
where name='Action';
-- Pues esos no
select * from actor where actor_id not in (
select actor_id from cat_film_actor
where name='Action'
)
