Mes: marzo 2016
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