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

Publicado por

Avatar del usuario

Juan Pablo Fuentes

Formador de programación y bases de datos