/* Query 4. This query requires you to write two SELECT statements described below.

Write a SELECT statement that returns three columns: EmailAddress, OrderID, and the order total for each customer. To do this, you can group the result set by the EmailAddress and OrderID columns; otherwise, there are many customers, each has many orders and each order contains many items. In addition, you must calculate the order total from the columns in the OrderItems table. Sort the results by the EmailAddress column in ascending sequence so you can see easily customers with multiple orders. Hint 1: an order's total is the sum of (ItemPrice - DiscountAmount) * Quantity of all items of the order. Hint 2: 41 rows Hint 3: a few customers have multiple orders like david.goldstein@hotmail.com, which has three orders with ID 5, 9, and 29.

Write a second SELECT statement that uses the first SELECT statement in its FROM clause. Because ORDER BY is not allowed in a subquery, you must remove it from the first SELECT statement. The main query (i.e., the outer SELECT statement) should return two columns: the customers email address and MaxOrderTotal for the largest order for that customer. To do this, you need to aggregate or group the result set of the subquery by its EmailAddress column. Sort the results by the MaxOrderTotal column in ascending sequence so you can see easily who has the smallest MaxOrderTotal. Hint 1: MAX Hint 2: 35 rows Hint 3: erinv email has the smallest MaxOrderTotal */

+ o - External Tables dbo.Addresses o Columns AddressID (PK, int, not null) CCustomerID (FK, int, null) Line1 (varchar(60), not null) Line2 (varchar(60), null) City (varchar(40), not null) State (varchar(2), not null) Zip Code (varchar(10), not null) Phone (varchar(12), not null) Disabled (int, not null) Keys + Constraints # Triggers # Indexes Statistics dbo.Administrators Columns AdminlD (PK, int, not null) EmailAddress (varchar(255), not null) Password (varchar(255), not null) FirstName (varchar(255), not null) LastName (varchar(255), not null) # Keys + Constraints # Triggers Indexes Statistics dbo.Categories o Columns CategoryID (PK, int, not null) CategoryName (varchar(255), not null) # Keys + Constraints # Triggers Indexes Statistics dbo.Customers Columns CustomerlD (PK, int, not null) EmailAddress (varchar(255), not null) Password (varchar(60), not null) FirstName (varchar(60), not null) LastName (varchar(60), not null) ShippingAddressID (int, null) BillingAddressID (int, null) # # - dbo.Orderltems Columns ItemID (PK, int, not null) - OrderID (FK, int, null) - ProductID (FK, int, null) ItemPrice (money, not null) DiscountAmount (money, not null) Quantity (int, not null) Keys Constraints Triggers Indexes Statistics dbo.Orders Columns OrderID (PK, int, not null) 6 CustomerlD (FK, int, null) OrderDate (datetime, not null) ShipAmount ( money, not null) TaxAmount (money, not null) ShipDate (datetime, null) ShipAddressID (int, not null) CardType (varchar(50), not null) CardNumber(char(16), not null) CardExpires (char(7), not null) BillingAddressID (int, not null) Keys Constraints Triggers Indexes Statistics dbo.Products Columns To ProductID (PK, int, not null) - CategoryID (FK, int, null) Product Code (varchar(10), not null) ProductName (varchar(255), not null) Description (text, not null) ListPrice (money, not null) DiscountPercent (money, not null) DateAdded (datetime, null) Keys Constraints Triggers Indexes Statistics # #