zyd

--zadanie0
--zadanie1
SELECT * FROM Products
WHERE ProductName LIKE 'T%' and UnitPrice < 10;
--zadanie2
SELECT OrderID,
UnitPrice*Quantity*(1 - Discount) AS Wartosc
FROM [Order Details];
--zadanie3
SELECT OrderID, SUM(UnitPrice*Quantity*(1 - Discount)) AS Wartosc
FROM [Order Details]
GROUP BY OrderID;

--zadanie4
SELECT OrderID, COUNT(DISTINCT ProductID) AS Unikat, SUM(UnitPrice*Quantity*(1 - Discount)) AS Wartosc
FROM [Order Details]
GROUP BY OrderID;
--zadanie5
SELECT OrderID, COUNT(DISTINCT ProductID) AS Unikat, ROUND(SUM(UnitPrice*Quantity*(1 - Discount)),2) AS Wartosc
FROM [Order Details]
GROUP BY OrderID
ORDER BY Wartosc DESC;
--zadanie6
SELECT SUM(UnitsInStock*UnitPrice) as wartosc, SUM(UnitsInStock) as ilosc
from Products
where UnitsInStock>0
--zadanie7
SELECT ProductName FROM Products
WHERE Discontinued = 0 AND UnitsInStock = 0;
--zadanie8
SELECT SupplierID, AVG(UnitPrice) as srednia from Products
group by SupplierID
order by srednia asc
--zadanie9
SELECT CategoryID,
AVG(UnitPrice) as srednia FROM Products
GROUP BY CategoryID
HAVING COUNT(DISTINCT CategoryID) < 10;
--zadanie10
SELECT SupplierID,
ROUND(AVG(UnitPrice), 2) AS Srednia,
ROUND(MIN(UnitPrice), 2) AS Minimum,
ROUND(MAX(UnitPrice), 2) AS Maksimum,
COUNT(*) AS Ilosc
FROM Products
GROUP BY SupplierID
--zadanie11
SELECT 'POL_'+CompanyName AS Nazwa FROM Customers
WHERE Country Like 'Poland'
union
select CompanyName from Customers
where Country not like 'Poland'
ORDER BY Nazwa;
--zadanie12
SELECT  ProductName, UnitPrice FROM Products as P1
WHERE (UnitPrice  =(SELECT MIN(UnitPrice) FROM Products as P2 WHERE P2.SupplierID = P1.SupplierID));