Base de datos
select count(*) from Customers
Contar por pais
select Country, count(*) from Customers group by Country
Ordenarlos descendente
select Country,count(*) as 'Número'
from Customers
group by Country
order by count(*) desc
Que no esté Brasil
select Country, count(*) as 'Número'
from Customers
where Country'Brazil'
group by Countryorder by count(*) desc
sumar importes de la orden por cliente, id cliente, nombre cliente, descendentemente (los totales)
select o.CustomerID,
c.CompanyName,
sum(o.Freight)as 'Total'from Customers c , Orders o
where (o.CustomerID=c.CustomerID)
group by o.CustomerID, c.CompanyName
order by sum(o.Freight)desc
Eliminar aquellos cuya suma total sea menor a 1000 (excluir losmenores a 1000)
select o.CustomerID,
c.CompanyName,
sum(o.Freight)as 'Total'
from Customers c , Orders o
where (o.CustomerID=c.CustomerID)
group by o.CustomerID, c.CompanyNamehaving sum(o.Freight)>1000
order by sum(o.Freight)desc
El having se utiliza para eliminar o agregar cosas luego de que se agrupó por eso va después del group by porque primero tiene que agrupar ydespués sobre el grupo. Siempre va contra una función de agregación porque controla cuando ya está creado el grupo. El where va contra las variables.
Excluir aquellas ordenes cuyo precio sea menor a 20select o.CustomerID,
c.CompanyName,
sum(o.Freight)as 'Total'
from Customers c , Orders o
where (o.CustomerID=c.CustomerID) and o.Freight>20
group by o.CustomerID, c.CompanyNamehaving sum(o.Freight)>1000
order by sum(o.Freight)desc
Sacar totales por categoría de productos
select c.CategoryName,
sum((o.UnitPrice*o.Quantity))as 'Total'
from Categories c ,OrderDetails o, Products p
where (p.ProductID=o.ProductID) and (p.CategoryID=c.CategoryID)
group by c.CategoryName
order by 'Total' desc
Insertar datos
insert region values( 5, 'Mendoza')...
Regístrate para leer el documento completo.