kolosHD4K

--1
--z grupy pierwszej
SELECT c.CompanyName
FROM Customers c
WHERE c.CustomerID NOT IN (SELECT o.CustomerID FROM Orders o)

--z grupy drugiej
select C.CompanyName
from Customers C
where not exists
(select *
from Products P
where UnitPrice < 5
and not exists
(select * from
Orders O, [Order Details] D
where C.CustomerID = O.CustomerID
and O.OrderID = D.OrderID
and P.ProductID = D.ProductID))

--z grupy trzeciej
select P.ProductName
from Products P
where not exists
(select *
from Employees E
where not exists
(select * from
Orders O, [Order Details] D
where E.EmployeeID = O.EmployeeID
and O.OrderID = D.OrderID
and P.ProductID = D.ProductID))

--2
SELECT e.EmployeeID, ROUND(SUM(od.Quantity*od.UnitPrice*(1-od.Discount)),2) as 'Suma przychodow'
FROM Employees e JOIN Orders o ON e.EmployeeID=o.EmployeeID
JOIN [Order Details] od ON o.OrderID=od.OrderID
GROUP BY e.EmployeeID
HAVING COUNT(distinct od.ProductID) > 50
ORDER BY e.EmployeeID

--3
SELECT CASE GROUPING(YEAR(o.OrderDate)) WHEN 0 THEN CAST(YEAR(o.OrderDate) AS VARCHAR(10)) ELSE 0 END as Rok,
CASE GROUPING(MONTH(o.OrderDate)) WHEN 0 THEN CAST(MONTH(o.OrderDate) AS VARCHAR(10)) ELSE 0 END as Miesiac,
CASE GROUPING(DAY(o.OrderDate)) WHEN 0 THEN CAST(DAY(o.OrderDate) AS VARCHAR(10)) ELSE 0 END as Dzien,
SUM(od.UnitPrice*od.Quantity*(1-od.Discount)) as 'analiza kosztow'
FROM Employees e JOIN Orders o ON e.EmployeeID=o.EmployeeID
JOIN [Order Details] od ON o.OrderID=od.OrderID
WHERE e.LastName = 'Buchanan'
GROUP BY GROUPING SETS((YEAR(o.OrderDate)), (MONTH(o.OrderDate)), (DAY(o.OrderDate)))

SELECT od.ProductID,CASE GROUPING(YEAR(o.OrderDate)) WHEN 0 THEN CAST(YEAR(o.OrderDate) AS VARCHAR(10)) ELSE 0 END as Rok,
CASE GROUPING(MONTH(o.OrderDate)) WHEN 0 THEN CAST(MONTH(o.OrderDate) AS VARCHAR(10)) ELSE 0 END as Miesiac,
CASE GROUPING(DAY(o.OrderDate)) WHEN 0 THEN CAST(DAY(o.OrderDate) AS VARCHAR(10)) ELSE 0 END as Dzien,
SUM(od.UnitPrice*od.Quantity*(1-od.Discount)) as 'analiza kosztow'
FROM Employees e JOIN Orders o ON e.EmployeeID=o.EmployeeID
JOIN [Order Details] od ON o.OrderID=od.OrderID
WHERE e.LastName = 'Buchanan'
GROUP BY od.ProductID ,YEAR(o.OrderDate), MONTH(o.OrderDate), (DAY(o.OrderDate)) WITH ROLLUP

--4
SELECT LastName
FROM(
SELECT e.LastName, RANK() OVER (ORDER BY e.HireDate) as ranking
FROM Employees e
WHERE e.HireDate < all(SELECT e2.HireDate FROM Employees e2 WHERE e2.City='London')) as podzapytanie
WHERE podzapytanie.ranking BETWEEN 2 AND 3

--II_1
SELECT CustomerID, ROUND(AVG(UnitPrice*Quantity*(1-Discount)),2) as 'średnia kwota'
FROM(
SELECT c.CustomerID, o.OrderID, od.Quantity, od.UnitPrice, od.Discount,
DENSE_RANK() OVER (PARTITION BY c.CustomerID ORDER BY o.CustomerID, o.OrderDate DESC) as ranking
FROM Customers c JOIN Orders o ON c.CustomerID=o.CustomerID
JOIN [Order Details] od ON o.OrderID=od.OrderID
WHERE c.Country = 'Spain') as podzapytanie
WHERE podzapytanie.ranking < 6
GROUP BY CustomerID
ORDER BY CustomerID

--II_2
SELECT RANK(100) WITHIN GROUP (ORDER BY SUM(od.Quantity*od.UnitPrice*(1-od.Discount)))
FROM Customers c JOIN Orders o ON c.CustomerID=o.CustomerID
JOIN [Order Details] od ON o.OrderID=od.OrderID
WHERE YEAR(o.OrderDate) = 1997 AND c.City = 'Berlin'
GROUP BY o.OrderID