Categoría: Sin categoría

Sql views

create or replace view cliente_gaston as
select customer.customer_id first_name, last_name, sum(amount) as total
from customer join payment using(customer_id)
group by first_name, last_name
order by total desc
limit 0,5
update customer set active=5
where customer_id in (select customer_id from cliente_gaston)

Más SQL

Países con tiendas:

select country, count(store_id) as total
from country left join city using (country_id)
left join address using (city_id)
left join store using (address_id)
group by country
having total>0

Cliente que más gasta:

select first_name, last_name, sum(amount) as total
from customer join payment using(customer_id)
group by first_name, last_name
order by total desc
limit 0,1

Ejemplos de CASE e IF:

SELECT *,
case when amount<1 then 'barato'
when amount between 1 and 3 then 'medio'
else 'caro' end precio,
if (amount<3,'barato','caro') precio2
FROM sakila.payment;

Con funciones de agregado:

select first_name, last_name, sum(amount) as total,
if (sum(amount)>100,'gastador','rácano') tipo
from customer join payment using(customer_id)
group by customer_id

Pagos formateados:

SELECT lpad(format(amount,3),10,' ') from payment

Nombre formateado y ordenar por longitud de apellido:

select ucase(first_name), lcase(last_name),
concat(
ucase(substring(first_name,1,1)),
lcase(substring(first_name,2))
) cliente,
length(last_name) longitud
from customer
order by longitud desc

Sentencias sql

Clientes que se llaman igual que actores de películas

SELECT distinct concat(c.first_name, " ", c.last_name) cliente, 
concat(a.first_name, " ", a.last_name) as actor
FROM actor a join
film_actor using (actor_id)
join film using (film_id)
join inventory using (film_id)
join rental using (inventory_id)
join customer c using (customer_id)
where a.first_name=c.first_name
order by cliente

Películas por categoría:

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

Clientes por país:

SELECT country, count(customer_id) FROM sakila.country
left join city using (country_id)
left join address using (city_id)
left join customer using (address_id)
group by country;

Clientes que se apellidan como el empleado que los atiende:

select distinct concat(c.first_name, " ", c.last_name) cliente, 
concat(s.first_name, " ", s.last_name) as empleado
from customer c join rental using (customer_id)
join staff s using (staff_id)
where c.last_name=s.last_name

Clientes que viven en el mismo código postal que los empleados:

select distinct concat(c.first_name, " ", c.last_name) cliente, 
concat(s.first_name, " ", s.last_name) as empleado
from customer c join address a1 on c.address_id=a1.address_id
join address a2 on a1.postal_code=a2.postal_code
join staff s on a2.address_id=s.address_id

Películas con importe total de alquiler menor que 2:

select title, avg(ifnull(amount,0)) total
from film left join inventory using (film_id)
left join rental using (inventory_id)
left join payment using (rental_id)
group by title
having total<2
order by 2

Total de clientes por países

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