-- wyswietl nazwe produktu i jego kategorii

select ProductName, CategoryName
from Products join Categories
on Products.CategoryID = Categories.CategoryID;

-- wyswietl nazwe klienta oraz jego zamowienia oraz
-- nazwisko pracownika ktory to zamowienie realizowal

select CompanyName, OrderID, (LastName + ' ' + FirstName) as Employee
from (Customers join Orders on Customers.CustomerID = Orders.CustomerID)
	join Employees on Orders.EmployeeID = Employees.EmployeeID
order by CompanyName;

-- ile produktow jest w kazdej ktagorii?
-- podaj numer kategorii
-- podaj nazwe kategorii

select CategoryID, count(ProductID) as Products
from Products
group by CategoryID;

select CategoryName, count(ProductID) as Products
from Products join Categories on Products.CategoryID = Categories.CategoryID
group by CategoryName;

-- podaj sume wartosci kazdej kategorii z produktow znajdujacych
-- sie na magazynie

select CategoryName, sum(UnitPrice * UnitsInStock) as Suma
from Products join Categories on Products.CategoryID = Categories.CategoryID
group by CategoryName;

-- ile towarow sprowadzamy od poszczegolnych dostawcow?
-- jaka jest wartosc towarow od poszcegolnych dostawcow?

select CompanyName, count(ProductID) as Products
from Products p join Suppliers s on p.SupplierID = s.SupplierID
group by CompanyName
order by Products desc;

select CompanyName, sum(UnitPrice * UnitsInStock) as Suma
from Products p join Suppliers s on p.SupplierID = s.SupplierID
group by CompanyName
order by Suma desc;

select CompanyName, count(ProductID) as Products,
	sum(UnitPrice * UnitsInStock) as Suma
from Products p join Suppliers s on p.SupplierID = s.SupplierID
group by CompanyName
order by Products desc;

-- ile jest towarow sprowadzanych od dostawcow ameryki polnocnej?

select CompanyName, Country, count(ProductID) as Products
from Products p join Suppliers s on p.SupplierID = s.SupplierID
where Country in ('USA', 'Canada')
group by CompanyName, Country
order by Products desc;

-- podaj wszystkich dostawcow produktow z kategorii Beverages

select ProductName, CategoryName, CompanyName
from (Suppliers s join Products p on s.SupplierID = p.SupplierID)
	join Categories c on p.CategoryID = c.CategoryID
where CategoryName = 'Beverages';

-- podaj ilu jest dostawcow w poszczegolnych kategoriach produktow

select CategoryName, count(CompanyName), count(distinct CompanyName)
from (Suppliers s join Products p on s.SupplierID = p.SupplierID)
	join Categories c on p.CategoryID = c.CategoryID
where CategoryName = 'Beverages'
group by CategoryName;