SELECT CategoryName,
       ProductName,
       UnitPrice,
       (SELECT AVG(UnitPrice) FROM Products as InnP WHERE InnP.CategoryID = OutP.CategoryID)             as CategoryAverage,
       UnitPrice - (SELECT AVG(UnitPrice) FROM Products as InnP WHERE InnP.CategoryID = OutP.CategoryID) as Difference,
       cast((SELECT ISNULL(SUM(UnitPrice * Quantity * (1 - Discount)), 0)
             FROM [Order Details]
                    JOIN Orders
                         ON Orders.OrderID = [Order Details].OrderID AND YEAR(OrderDate) = 1997 AND MONTH(OrderDate) = 3
             WHERE [Order Details].ProductID = OutP.ProductID) as numeric(10, 2))                        as SalesMarch1997
FROM Products as OutP
       JOIN Categories
            ON OutP.CategoryID = Categories.CategoryID

Zad 2:

SELECT superior.FirstName,
       superior.LastName,
       (SELECT SUM(UnitPrice * Quantity * (1 - Discount))
        FROM [Order Details]
               JOIN Orders ON Orders.OrderID = [Order Details].OrderID
        WHERE Orders.EmployeeID = superior.EmployeeID) +
       (SELECT SUM(Freight) FROM Orders WHERE Orders.EmployeeID = superior.EmployeeID) as totalSales
FROM Employees as superior
WHERE EXISTS(SELECT * FROM Employees as inferior WHERE inferior.ReportsTo = superior.EmployeeID)

Zad 3:

--JOIN
SELECT CompanyName,
       ContactName,
       ContactTitle,
       Address,
       City,
       Region,
       PostalCode,
       Country
FROM Customers
       LEFT JOIN Orders
                 ON Orders.CustomerID = Customers.CustomerID AND YEAR(OrderDate) = 1997
WHERE OrderDate IS NULL

--IN
SELECT CompanyName,
       ContactName,
       ContactTitle,
       Address,
       City,
       Region,
       PostalCode,
       Country
FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE YEAR(OrderDate) = 1997)

--EXIST
SELECT CompanyName,
       ContactName,
       ContactTitle,
       Address,
       City,
       Region,
       PostalCode,
       Country
FROM Customers
WHERE NOT EXISTS(SELECT * FROM Orders WHERE Orders.CustomerID = Customers.CustomerID AND YEAR(OrderDate) = 1997)

Zad 4:

SELECT firstname,
       lastname,
       (SELECT COUNT(*)
        FROM loanhist
        WHERE YEAR(in_date) = 2001
          AND loanhist.member_no = adult.member_no
           OR loanhist.member_no IN (SELECT member_no FROM juvenile WHERE adult_member_no = adult.member_no)
       ) as booksReadIn2001
FROM member
       JOIN adult
            ON adult.member_no = member.member_no
WHERE (state = 'AZ' AND (SELECT COUNT(*) FROM juvenile WHERE adult_member_no = adult.member_no) > 2)
   OR (state = 'CA' AND (SELECT COUNT(*) FROM juvenile WHERE adult_member_no = adult.member_no) > 3)