------------------------------------------------------------------------
-- John David Ayers ----------------------------------------------------
-- November 10, 2009 ---------------------------------------------------
-- Subquery ------------------------------------------------------------
------------------------------------------------------------------------

SELECT	Orders.OrderNumber, Orders.OrderDate, Orders.ShipDate,
(
	SELECT Customers.CustLastName
	FROM Customers
	WHERE Customers.CustomerID = Orders.CustomerID
)
FROM Orders
WHERE Orders.ShipDate = '10-03-2007';

SELECT	CustFirstName + ' ' + CustLastName AS [Customer Name],
(
	SELECT COUNT(*)
	FROM Orders
	WHERE Orders.CustomerID = Customers.CustomerID
) AS [Number of Orders Placed]
FROM Customers;

SELECT CustFirstName + ' ' + CustLastName AS [Customer Name],
(
	SELECT MAX(QuotedPrice)
	FROM Orders INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber
	WHERE Orders.CustomerID = Customers.CustomerID
) AS [Largest Order Placed]
FROM Customers;

SELECT CustFirstName + ' ' + CustLastName AS [Customers],
(
	SELECT AVG(DATEDIFF(dd, OrderDate, ShipDate))
	FROM Orders
	WHERE Orders.CustomerID = Customers.CustomerID
) AS [Average Days To Ship]
FROM Customers;
