-- 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;