use Northwind

Select A.CompanyName, Year(D.OrderDate), MONTH(D.OrderDate),Sum(C.UnitPrice * C.Quantity * (1-C.Discount)) as 'Przychod'
From Suppliers as A
	INNER JOIN Products as B
		ON A.SupplierID = B.SupplierID
	INNER JOIN [Order Details] as C
		ON B.ProductID = C.ProductID
	INNER JOIN ORDERS as D
		ON D.OrderID = C.OrderID
GROUP BY A.CompanyName, Year(D.OrderDate) ,MONTH(D.OrderDate)
HAVING Sum(C.UnitPrice * C.Quantity * (1-C.Discount)) > (
		SELECT SUM(E.UnitPrice * E.Quantity * (1-E.Discount)) / (Select Count(A1.CompanyName)
																 From Suppliers as A1
																	INNER JOIN Products as B1
																		ON A1.SupplierID = B1.SupplierID
																	INNER JOIN [Order Details] as C1
																		ON B1.ProductID = C1.ProductID
																	INNER JOIN ORDERS as D1
																		ON D1.OrderID = C1.OrderID
																	WHERE
																	Year(D.OrderDate) = YEAR(D1.OrderDate)
																	AND MONTH(D.OrderDate) = MONTH(D1.OrderDate)
																	)
		FROM [Order Details] as E
			INNER JOIN Orders as F
				ON F.OrderID = E.OrderID
		WHERE
			MONTH(F.OrderDate) = MONTH(D.OrderDate)
			AND YEAR(F.OrderDate) = YEAR(D.OrderDate)
			)

USE library

SELECT A.firstname, A.lastname, B.street +' '+B.city + ' '+B.state,
'Adult', NULL as 'Guardian', SUM(ISNULL(D1.fine_paid,0))
FROM MEMBER as A
	INNER JOIN adult as B
		ON A.member_no = B.member_no
	INNER JOIN LOAN as B1
		ON A.member_no = B1.member_no
	INNER JOIN copy as C1
		ON B1.isbn = C1.isbn and B1.copy_no = C1.copy_no
	INNER JOIN loanhist as D1
		ON D1.copy_no = C1.copy_no and D1.isbn = C1.isbn
Group By A.firstname, A.lastname, B.street +' '+B.city + ' '+B.state
UNION
SELECT A.firstname, A.lastname, C.street +' '+C.city + ' '+C.state,
'Child', D.firstname + '' + D.lastname as 'Guardian', SUM(ISNULL(D1.fine_paid,0))
FROM MEMBER as A
	INNER JOIN juvenile as B
		ON A.member_no = B.member_no
	INNER JOIN adult as C
		ON B.adult_member_no = C.member_no
	INNER JOIN member as D
		on D.member_no = C.member_no
		INNER JOIN LOAN as B1
		ON A.member_no = B1.member_no
	INNER JOIN copy as C1
		ON B1.isbn = C1.isbn and B1.copy_no = C1.copy_no
	INNER JOIN loanhist as D1
		ON D1.copy_no = C1.copy_no and D1.isbn = C1.isbn
Group By A.firstname, A.lastname, C.street +' '+C.city + ' '+C.state,
 D.firstname + '' + D.lastname

 USE Northwind


 SELECT *
 FROM Customers as A
	INNER JOIN Orders as B
		ON A.CustomerID = B.CustomerID
	INNER JOIN Employees as C
		ON C.EmployeeID = B.EmployeeID
	LEFT JOIN Employees as D
		ON C.EmployeeID = D.ReportsTo
WHERE YEAR(OrderDate) = 1997
AND MONTH(B.OrderDate) = 5
AND Day(B.OrderDate) = 23
AND D.EmployeeID is NULL