Bases de datos

Ejercicio Nº1

Select ShipperID, CompanyName, Count(OrderID) As 'Total'
From Shippers
Inner Join Orders On ShipperID=ShipVia
Group By ShipperID, CompanyName
Having Count(OrderID) > 250Ejercicio Nº2

Select LastName, FirstName, (Case When Year(OrderDate)=1996 Then 5
When Year(OrderDate)=1997 Then 10
Else 15
End) As 'Puntaje'
Into #Temp
From Employees E
InnerJoin Orders O On E.EmployeeID=O.EmployeeID
Group By LastName, FirstName, OrderDate
Select LastName, FirstName, Sum(Puntaje) As 'Total'
From #Temp
Group By LastName, FirstName

Select Lastname ,(Select Sum(Case When Datepart(Year,Orderdate) = '1996' Then 5
When Datepart(Year,Orderdate) = '1997' Then 10
Else 15End)
From Orders O
Where E.EmployeeID=O.EmployeeID) As 'Puntos'
From Employees E

Ejercicio Nº3

Select Distinct C.CustomerID, CompanyName, ContactName
From Customers CInner Join Orders O On C.CustomerID=O.CustomerID
Inner Join [Order Details] OD On O.OrderID=OD.OrderID
Inner Join Products P On OD.ProductID=P.ProductID
Where P.ProductName='Queso Cabrales'
Order ByCompanyName

Ejercicio Nº4

Select ProductID, ProductName, CompanyName, ContactName, City, Phone, Fax, Sum (50-UnitsInStock) As 'Cantidad'
From Products P
Inner Join Suppliers S OnP.SupplierID=S.SupplierID
Where UnitsInStock < 15 And City 'Ravenna'
Group By ProductID, ProductName, CompanyName, ContactName, City, Phone, Fax
Union All
Select ProductID, ProductName, CompanyName,ContactName, City, Phone, Fax, Sum (50-UnitsInStock) As 'Cantidad'
From Products P
Inner Join Suppliers S On P.SupplierID=S.SupplierID
Where UnitsInStock < 15 And City = 'Ravenna'
Group By ProductID,ProductName, CompanyName, ContactName, City, Phone, Fax

Ejercicio Nº5

Select P.ProductID, ProductName, Sum(Case When Year(OrderDate)=1996 Then 5
When Year(OrderDate)=1997 Then 10...