Querys base de datos norwind examen final

/* Juan Jhordanny Hernández Durán. 2010-1561 */ --1. Presentar el monto total ordenado por año. select YEAR (Orders.OrderDate) as Año, SUM (detail.UnitPrice * detail.Quantity) as [Monto Total] from[Order Details] detail, Orders where orders.OrderID=detail.OrderID group by YEAR (Orders.OrderDate) order by YEAR (Orders.OrderDate) --2. Presentar monto total ordenado por año y por categoría deprodcuto(presentar la descripcion de la categoria). select SUM (detai.UnitPrice * detai.Quantity) as [Monto Total], YEAR (Orders.OrderDate) as Año, Categories.CategoryName, SUBSTRING (description, 1, 250)[Descripción de la Categoria] from [Order Details] detai, Orders, Products, Categories where orders.OrderID=detai.OrderID and products.ProductID=detai.ProductID andProducts.CategoryID=Categories.CategoryID group by YEAR (Orders.OrderDate),Categories.CategoryName, SUBSTRING (description, 1, 250) order by YEAR (Orders.OrderDate),Categories.CategoryName --3. Presentar el cliente con mayor montoordenado. select top(1) Customers.ContactName, orders.CustomerID, SUM (unitprice*quantity)as [Mayor Monto Ordenado] from customers, Orders, [Order Details] where Orders.OrderID = [Order Details].OrderIDgroup by orders.customerid, contactName order by [Mayor Monto Ordenado] desc --4. Presentar la cantidad de productos por suplidor y categoria. select SupplierID, CategoryName, COUNT (ProductID) as[Cantidad de Productos] from Products as pro, Categories as cat where SupplierID IS NOT NULL AND pro.CategoryID = cat.CategoryID group by SupplierID, cat.CategoryName order by SupplierID --5. Presentarlas ordenes por region y el average de productos ordernados. select ShipRegion, AVG (Quantity) as [Average de Productos], orders.OrderID from [Order Details], Orders where Orders.OrderID = [OrderDetails].OrderID AND ShipRegion IS NOT NULL group by ShipRegion, orders.OrderID order by ShipRegion --6. Presentar la categoría y region con más productos ordenados.

select top 1 pr.CategoryID,...