SQL Chapter 5 How to code summary queries

4. Write a SELECT statement that returns one row for each customer that has orders with these columns: The EmailAddress column from the Customers table A count of the number of orders The total amount for those orders (Hint: First, subtract the discount amount from the price. Then, multiply by the quantity.) Return only those rows where the customer has more than 1 order. Sort the result set in descending sequence by the sum of the line item amounts.

5. Modify the solution to exercise 4 (above) so it only counts and totals line items that have an ItemPrice value thats greater than 400.

6. Write a SELECT statement that answers this question: What is the total amount ordered for each product? Return these columns: The product name from the Products table The total amount for each product in the OrderItems table (Hint: You can calculate the total amount by subtracting the discount amount from the item price and then multiplying it by the quantity) Use the ROLLUP operator to include a row that gives the grand total.

7. Write a SELECT statement that answers this question: Which customers have ordered more than one product? Return these columns: The email address from the Customers table The count of distinct products from the customers orders

dbo.Addresses Columns mo AddressID (PK, int, not null) Qu CustomerlD (FK, int, null) Line1 (varchar(60), not null) Line2 (varchar (60), null) City (varchar(40), not null) State (varchar(2), not null) ZipCode (varchar(10), not n Phone (varchar(12), not null Disabled (int, not null) Keys Constraints Triggers Indexes Statistics dbo.Administrators Columns \%o AdminID (PK, int, not null) EmailAddress (varchar(255), Password (varchar(255), not FirstName (varchar(255), no LastName (varchar(255), no Keys Constraints Triggers Indexes Statistics dbo.Categories Columns mo CategorylD (PK, int, not null CategoryName (varchar(25: Keys Constraints Triggers Indexes Statistics dbo.Customers Columns o CustomerID (PK, int, not nu EmailAddress (varchar(255), Password (varchar (60), not FirstName (varchar (60), not LastName (varchar(60), not ShippingAddressID (int, null BillingAddressID (int, null) dbo.Orderltems Columns o ItemID (PK, int, not null) Q OrderlD (FK, int, null) ProductID (FK, int, null) ItemPrice (money, not null) DiscountAmount (money, r Quantity (int, not null) Keys Constraints Triggers Indexes Statistics dbo.Orders Columns mo OrderID (PK, int, not null) Q C CustomerlD (FK, int, null) OrderDate (datetime, not nu ShipAmount (money, not n TaxAmount (money, not nu ShipDate (datetime, null) ShipAddressID (int, not null] CardType (varchar(50), not i Card CardType (varchar(50), not Carconpmes (crantrinnorma BillingAddressID (int, not nu Keys Constraints Triggers Indexes Statistics dbo.Products Columns o ProductID (PK, int, not null) Q CategorylD (FK, int, null) ProductCode (varchar(10), r ProductName (varchar(255) Description (text, not null) ListPrice (money, not null) DiscountPercent (money, n DateAdded (datetime, null) Keys Constraints Triggers Indexes Statistics