zad 17
select CategoryName, ProductName, datepart(quarter, OrderDate) as kwartal,
sum(OD.UnitPrice*OD.Quantity) as [wartosc sprzedazy]
from Products P join Categories C on P.CategoryID=C.CategoryID
join [Order Details] OD on P.ProductID=OD.ProductID join Orders O on O.OrderID=OD.OrderID
where year(OrderDate)='1997' and CategoryName='Beverages' or CategoryName='Seafood'
group by CategoryName, ProductName, datepart(quarter, OrderDate)
order by datepart(quarter, OrderDate)
ZAD. 20. Znalezc zamowienia, ktorych calkowita wartosc jest > 4000
select OrderID, SUM(UnitPrice*Quantity)Wartosc
from [Order Details]
group by OrderID
having (SUM(UnitPrice*Quantity)) > 4000
order by Wartosc
ZAD. 20. Znalezc zamowienia, ktorych calkowita wartosc jest > 4000
select OrderID, SUM(UnitPrice*Quantity)Wartosc
from [Order Details]
group by OrderID
having (SUM(UnitPrice*Quantity)) > 4000
order by Wartosc
/* ZAD. 33. Przygotowac ranking pracownikow wg ilosci zrealizowanych zamowien (nazwisko sprzedawcy, ilosc zamowien).*/
select FirstName, LastName, count(OrderID)[Ilosc zamowien]
from Employees e join Orders o on e.EmployeeID=o.EmployeeID
group by LastName, FirstName
order by 3 DESC
zad.39
select FirstName, LastName, datename(month, OrderDate) as miesiac, count(OrderDate)[ilosc zamowien]
from Employees E join Orders O on E.EmployeeID=O.EmployeeID
where year(OrderDate)=1997 and LastName='Fuller'
group by FirstName, LastName, datename(month, OrderDate), datepart(month, OrderDate)
order by datepart(month, OrderDate)
/* ZAD. 41. wskazac pracownikow (nazwisko, imie), ktorzy zrealizowali przynajmniej 50 zamowien w 1998 roku.*/
select FirstName, LastName, count(OrderID)[Ilosc zamowien]
from Employees e join Orders o on e.EmployeeID=o.EmployeeID
where datepart(yyyy,OrderDate)='1998'
group by FirstName, LastName
having (count(OrderID)>=50)
1)
select year(orderdate), datepart(quarter, OrderDate) as kwartal, sum(quantity) IloscZamowien
from orders join Customers
on orders.CustomerID=Customers.CustomerID
join [order Details] OD
on orders.orderID=OD.orderID
join Products
on OD.ProductID = Products.ProductID
where ProductName = 'Chai'
group by year(OrderDate), datepart(quarter, OrderDate)
order by year(OrderDate), datepart(quarter, OrderDate)
2)
select em.LastName as NazwiskoPodwladnego, em.FirstName as ImiePodwladnego, e.LastName as Szef
from Employees e join Employees em on e.EmployeeID=em.ReportsTo
where e.LastName = 'Fuller'