Base De Datos 2-- Cursores
NOMBRE: FRANK FERNANDEZ IMAN
CICLO: V
1- TOTAL FACTURADO AGRUPADO POR CATEGORIAS PARA EL AÑO 1996.
SELECT CT.CategoryName AS [NOMBRE CATEGORIA],(SelectSum(Freight)FROM Orders
WHEREYEAR(OrderDate)= 1996)
as [AÑO 1996]
FROM Categories CT
GROUPBY CT.CategoryName
2- COMPARACION DEL TOTAL FACTURADO AGRUPADO POR CATEGORIAS PARA LOS AÑOS 1996 Y 1998:
SELECTCT.CategoryName AS [NOMBRE CATEGORIA],
(SelectSum(Freight)FROM Orders
WHEREYEAR(OrderDate)= 1996)
as [AÑO 1996],
(SelectSum(Freight)FROM Orders
WHEREYEAR(OrderDate)= 1998)
as [AÑO 1998]FROM Categories CT
GROUPBY CT.CategoryName
3- LISTE EL TOTAL FACTURADO POR CLIENTE Y POR AÑO:
SELECT C.CompanyName AS [CLIENTE],
YEAR(O.OrderDate)AS [AÑO],
Sum(Freight)
as [TOTALFACTURADO]
FROM Customers C innerjoin Orders O
ON C.CustomerID = O.CustomerID
GROUPBY C.CompanyName, O.OrderDate
4- LISTE TOTAL FACTURADO POR PAIS DE CLIENTE AGRUPADO POR AÑO:
SELECT C.Country AS[PAIS],
YEAR(O.OrderDate)AS [AÑO],
Sum(Freight)
as [TOTAL FACTURADO]
FROM Customers C innerjoin Orders O
ON C.CustomerID = O.CustomerID
GROUPBY C.Country, O.OrderDate
5- LISTE TODOS LOSPRODUCTOS CUYO PRECIO SEA MAYOR QUE EL PRECIO PROMEDIO:
SELECT ProductName, UnitPrice,
(SELECTAVG(UnitPrice)FROM Products)
AS [PRECIO PROMEDIO]
FROM Products
WHERE UnitPrice>(SELECTAVG(UnitPrice)FROM Products)
6- LISTE LOS CLIENTES( COntacName, CustomerDesc) QUE NO HAYAN REALIZADO NINGUNA COMPRA:
SELECT C.ContactName,(SELECT CustomerDesc FROM CustomerDemographics)
FROM Customers CWHERENOTEXISTS(SELECT O.CustomerID FROM Orders O
WHERE O.CustomerID = C.CustomerID)
7- LISTE EL NOMBRE DE LOS PROVEEDORES CUYO PRODUCTOS NUNCA SE HAYA VENDIDO
SELECT S.CompanyName
FROMSuppliers S innerjoin Products P
ON S.SupplierID = P.SupplierID
WHERENOTEXISTS(SELECT OD.ProductID FROM [Order_Details] OD
WHERE OD.ProductID = P.ProductID )
8- OBTENER EL PRECIO MEDIO DE LOS...
Regístrate para leer el documento completo.