zadania, Studia Politechnika Poznańska, Semestr V, Bazy Danych, bd nowsze, lab6 18.11.2013
[ Pobierz całość w formacie PDF ]
zad 1select CategoryName,( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p whereo.OrderID=od.OrderId and year(o.OrderDate)=1996 and month(OrderDate)=9 and p.ProductID=od.ProductID andp.CategoryID=c.categoryID ) as "1996",( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p whereo.OrderID=od.OrderId and year(o.OrderDate)=1997 and p.ProductID=od.ProductID andp.CategoryID=c.categoryID ) as "1997",( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p whereo.OrderID=od.OrderId and year(o.OrderDate)=1998 and p.ProductID=od.ProductID andp.CategoryID=c.categoryID ) as "1998 "fromCategories czad 2select CategoryName,( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p whereo.OrderID=od.OrderId and year(o.OrderDate)=1996 and month(OrderDate)=6 and p.ProductID=od.ProductID andp.CategoryID=c.categoryID ) as "Czerwiec 1996",( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p whereo.OrderID=od.OrderId and year(o.OrderDate)=1996 and month(OrderDate)=7 and p.ProductID=od.ProductID andp.CategoryID=c.categoryID ) as "Lipiecc 1996",( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p whereo.OrderID=od.OrderId and year(o.OrderDate)=1996 and month(OrderDate)=8 and p.ProductID=od.ProductID andp.CategoryID=c.categoryID ) as "Sierpien 1996",( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p whereo.OrderID=od.OrderId and year(o.OrderDate)=1996 and month(OrderDate)=9 and p.ProductID=od.ProductID andp.CategoryID=c.categoryID ) as "Wrzesien 1996",( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p whereo.OrderID=od.OrderId and year(o.OrderDate)=1996 and month(OrderDate)=10 and p.ProductID=od.ProductID andp.CategoryID=c.categoryID ) as "Pazdziernik 1996",( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p whereo.OrderID=od.OrderId and year(o.OrderDate)=1996 and month(OrderDate)=11 and p.ProductID=od.ProductID andp.CategoryID=c.categoryID ) as "Listopad 1996",( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p whereo.OrderID=od.OrderId and year(o.OrderDate)=1996 and month(OrderDate)=12 and p.ProductID=od.ProductID andp.CategoryID=c.categoryID ) as "Grudzien 1996",( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p whereo.OrderID=od.OrderId and year(o.OrderDate)=1997 and month(OrderDate)=1 and p.ProductID=od.ProductID andp.CategoryID=c.categoryID ) as "styczen 1997",( select Sum(Quantity*od.UnitPrice) from [Order Details] od,Orders o,Products p whereo.OrderID=od.OrderId and year(o.OrderDate)=1998 and p.ProductID=od.ProductID andp.CategoryID=c.categoryID ) as "1998 "fromCategories czad 3select year(o.OrderDate),(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p whereo1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) andp.CategoryID='1') as "Beverages",(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p whereo1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) andp.CategoryID='2') as "Condiments",(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p whereo1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) andp.CategoryID='3') as "Confections",(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p whereo1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) andp.CategoryID='4') as "Dairy Products",(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p whereo1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) andp.CategoryID='5') as "Grains/Cereals",(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p whereo1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) andp.CategoryID='6') as "Meat/Poultry",(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p whereo1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) andp.CategoryID='7') as "Produce",(select Sum(Quantity*od.UnitPrice)from [Order Details] od, Orders o1, Products p whereo1.OrderID=od.OrderId and p.ProductID=od.ProductID and year(o.OrderDate)=year(o1.OrderDate) andp.CategoryID='8') as "Seafood"fromOrders ogroup by year(o.OrderDate)zad 4select * from customersselect * from Employeesselect distinct country from Customersselect distinct Region.RegionDescription from Employeesjoin EmployeeTerritories on EmployeeTerritories.EmployeeID=Employees.EmployeeIDjoin Territories on Territories.TerritoryID=EmployeeTerritories.TerritoryIDjoin Region on Region.RegionID=Territories.RegionIDselect sum(quantity*UnitPrice), customers.country, RegionDescriptionfrom [Order Details] join orders on Orders.OrderID=[Order Details].OrderIDjoin Customers on customers.CustomerID=Orders.CustomerIDjoin Employees on Employees.EmployeeID=Orders.EmployeeIDjoin EmployeeTerritories on EmployeeTerritories.EmployeeID=Employees.EmployeeIDjoin Territories on Territories.TerritoryID=EmployeeTerritories.TerritoryIDjoin Region on Region.RegionID=Territories.RegionIDgroup byCustomers.Country,RegionDescriptionzad 5select distinct Country from Customersselect distinct Region.RegionDescription from Employeesjoin EmployeeTerritories on EmployeeTerritories.EmployeeID=Employees.EmployeeIDjoin Territories on Territories.TerritoryID=EmployeeTerritories.TerritoryIDjoin Region on Region.RegionID=Territories.RegionIDselect sum(Quantity*UnitPrice),Customers.Country,RegionDescription from [Order Details] join Orders on Orders.OrderID =[Order Details].OrderIDjoin Customers on Customers.CustomerID=Orders.CustomerIDjoin Employees on Employees.EmployeeID=Orders.EmployeeIDjoin EmployeeTerritories on EmployeeTerritories.EmployeeID=Employees.EmployeeIDjoin Territories on Territories.TerritoryID=EmployeeTerritories.TerritoryIDjoin Region on Region.RegionID=Territories.RegionIDgroup by Customers.Country,RegionDescriptionzad 7b)select S.CITY,J.CITY,sum(QTY) from S,J,SPJwhere S.S#=SPJ.S# and J.J#=SPJ.J#group by S.CITY,J.CITY
[ Pobierz całość w formacie PDF ]