-- zad 1
/*SELECT* FROM Customers
WHERE CompanyName LIKE 'C%'
AND LEN(Country)>6;*/

-- zad2
SELECT OrderId, 
(UnitPrice * Quantity * 1-Discount) as value 
from [Order Details];

--zad3
/*SELECT OrderID , sum(UnitPrice * Quantity * 1-Discount) as value  
from [Order Details] 
GROUP by OrderID;*/

--zad4
/*SELECT OrderID,
count(distinct productid)
sum(UnitPrice * Quantity * (1-Discount))  as value  
from [Order Details] 
GROUP by OrderID;*/

-- zad5
--select distinct OrderID, round (sum(UnitPrice * Quantity * 1-Discount),2) as value from [Order Details] GROUP by OrderID order by value desc;

-- zad6
--select SupplierID ,round(avg(UnitPrice),2) as value from Products GROUP BY SupplierID ORDER by value desc;
--select SupplierID ,round(avg(UnitPrice),2) as value from Products GROUP BY SupplierID ORDER by SupplierID;
--select SupplierID ,round(avg(UnitPrice),2) AvgPrice from Products GROUP BY SupplierID ORDER by AvgPrice ASC;

--zad7
--select ProductID, ProductName, UnitPrice from Products where Discontinued=0 and UnitPrice<12;

--zad8
--select sum(UnitPrice*UnitsInStock)from products;

--zad9
/*select avg(unitprice)avgPrice
from products
group by categoryid
having count(distinct ProductID)<10;*/

--zad10
/*select categoryid, 
round(avg(unitprice),2) as srednia, 
round(min(unitprice),2) as minimum, 
round(max(unitprice),2) as maksimum
from products 
group by CategoryID;*/

--zad11
/*select 'DE_' +companyname nazwa from Customers where country ='Germany' 
Union 
select 'PL_' +companyname from Customers where country = 'Poland'
union
select 'USA_' +companyname from Customers where country ='USA'

order by nazwa;*/

https://put.blinkiewicz.pl/courses/bd_lab