use Northwind

--Zad 1.1

Select B.CompanyName, B.Phone
From Orders as A
	Inner Join Customers as B
		on A.CustomerID = B.CustomerID
	Inner Join Shippers as C
		on A.ShipVia = C.ShipperID
Where Year(A.ShippedDate)  = '1997'
AND C.CompanyName = 'United Package'
Group By B.CompanyName, B.Phone

Select B.CompanyName, B.Phone
From Customers as B
Where
Exists ( Select *
		from Orders as A
		Where Exists (Select *
					  From Shippers as C
					  Where	C.CompanyName = 'United Package'
					  AND C.ShipperID = A.ShipVia)
		AND Year(A.ShippedDate)  = '1997'
		AND B.CustomerID = A.CustomerID)

--Zad 1.3

Select B.CompanyName, B.Phone
From Customers as B
Where  Not Exists( Select *
				  From Orders as A
				  Where A.CustomerID = B.CustomerID
				  AND EXISTS ( SELECT *
							   FROM [Order Details] as C
							   Where C.OrderID = A.OrderID
							   AND EXISTS (SELECT *
										   FROM Products as D
										   WHERE
										   D.ProductID = C.ProductID
										   AND EXISTS (Select *
													   FROM Categories as E
													   Where E.CategoryID = D.CategoryID
													   AND E.CategoryName = 'Confections'))))

Select B.CompanyName , B.Phone
From Orders as A
	INNER JOIN [Order Details] as C
		On C.OrderID = A.OrderID
	INNER JOIN Products as D
		ON D.ProductID = C.ProductID
	INNER JOIN Categories as E
		ON E.CategoryID = D.CategoryID AND E.CategoryName = 'Confections'
	RIGHT JOIN Customers as B
		On B.CustomerID = A.CustomerID
WHERE A.Freight IS NULL
GROUP BY B.CompanyName, B.Phone
 --2.1
SELECT A.ProductName, (SELECT  MAX(B.Quantity)
		 FROM [Order Details] as B
		 WHERE A.ProductID = B.ProductID)
FROM Products A

SELECT A.ProductName, MAX(B.Quantity)
FROM Products A
	INNER JOIN [Order Details] as B
		ON A.ProductID = B.ProductID
GROUP BY A.ProductName

--2.2

SELECT A.ProductName
FROM Products as A
WHERE
A.UnitPrice > (Select Avg(B.UnitPrice)
			   FROM Products as B)
-- 3.1
SELECT A.ProductName
FROM Products as A
WHERE
A.UnitPrice > (Select Avg(B.UnitPrice)
			   FROM Products as B
			   WHERE A.CategoryID = B.CategoryID)

SELECT A.ProductName, A.UnitPrice,
			   (Select Avg(B.UnitPrice)
			   FROM Products as B) as AVERAGE,
			   A.UnitPrice - (Select Avg(B.UnitPrice)
			   FROM Products as B)
FROM Products as A