use Northwind
--11
select lastname, SUM(quantity)
from Employees e join Orders o on
e.EmployeeID=o.EmployeeID join [Order Details]
od on o.OrderID=od.OrderID join Products p
on od.ProductID=p.ProductID
where ProductName='chai' and
YEAR(OrderDate)=1997
group by LastName

select MONTH(orderdate), SUM(od.unitprice*quantity)
from orders o join [Order Details] od on
o.OrderID=od.OrderID join Products p on
od.ProductID=p.ProductID join Categories c on
p.CategoryID=c.CategoryID
where YEAR(OrderDate)=1997 and
CategoryName='Seafood'
group by MONTH(OrderDate)

select country, SUM(unitprice*quantity)
from Customers c join Orders o on
c.CustomerID=o.CustomerID join [Order Details]
od on o.OrderID=od.OrderID
where YEAR(OrderDate)=1997
group by Country

select o.*
from Orders o join [Order Details] od on
o.OrderID=od.OrderID join Products p on
od.ProductID=p.ProductID join Suppliers s on
p.SupplierID=s.SupplierID
where YEAR(OrderDate)=1996 and s.CompanyName
like '%ltd.%'

--15
--16
select AVG(od.unitprice)
from [Order Details] od join orders o
on od.orderid=o.orderid
where OrderDate between '1996-01-01' and '1996-03-31'

select MIN(unitprice)
from Products p join Suppliers s on
p.SupplierID=s.SupplierID
where Country='usa'

select COUNT(*)
from [Order Details] od join Products p on
od.ProductID=p.ProductID
where ProductName='chef anton''s gumbo mix'

select AVG(od.unitprice)
from [Order Details] od join orders o
on od.OrderID=o.OrderID
where OrderDate between '1997-04-01' and '1997-06-30'

--20
select productname, companyname, categoryname
from Categories c join Products p
on c.CategoryID=p.CategoryID join Suppliers s
on p.SupplierID=s.SupplierID
where CompanyName like 'Pavlova%'

select c.*, customertypeid
from Customers c join CustomerCustomerDemo ccd
on c.CustomerID=ccd.CustomerID

select productname
from products p join Suppliers s on
p.SupplierID=s.SupplierID
where Country='Japan'

select distinct c.city
from Customers c join Orders o on
c.CustomerID=o.CustomerID join Employees e on
o.EmployeeID=e.EmployeeID
where LastName='King'

select productname, p.unitprice as 'cena aktualna', od.unitprice as 'cena na zamowieniu'
from Categories c join Products p on c.CategoryID=p.CategoryID join [Order Details] od on p.ProductID=od.ProductID
where CategoryName='beverages'
order by 3 desc

select distinct productname, p.unitprice as 'cena aktualna', od.unitprice as 'cena na zamowieniu'
from Categories c join Products p on c.CategoryID=p.CategoryID join [Order Details] od on p.ProductID=od.ProductID
where CategoryName='beverages'
and p.UnitPrice<>od.UnitPrice
order by 3 desc

select companyname, orderdate, requireddate
as 'czas wymagany'
from customers c join orders o on
c.customerid=o.CustomerID
where ShippedDate<=RequiredDate

select companyname, orderdate,
CAST(shippeddate-requireddate as int), lastname
from Customers c join Orders o on
c.CustomerID=o.CustomerID join Employees e on
o.EmployeeID=e.EmployeeID
where ShippedDate>RequiredDate