
    SELECT outside.Id AS "Supplier",
        (SELECT AVG(SUM(inside.Quantity * inside.Unit_Price))
           FROM Customer.Order_Item inside
           WHERE inside.Product_Id = outside.Id
           GROUP BY outside.Id) AS "Average Order Total"
    FROM  Customer.Product outside
    GROUP BY outside.Id;

    SELECT Order_Number
    FROM  Customer.Orders
    WHERE Customer_Id IN
        (SELECT Id from Customer.Customer WHERE Country IN ('Japan', 'Canada', 'Germany', 'UK'));

    SELECT Name, Country_Code
    FROM World.City
    WHERE Country_Code IN (SELECT Code FROM World.country
                       WHERE Name IN ('Japan', 'Brazil', 'Germany', 'United States'));

    SELECT ctr.Name
    FROM World.Country ctr
    WHERE ctr.Code NOT IN
      (SELECT Country_Code FROM World.Country_Organization WHERE Organization_Code = 'UN');

