IS3063 PLEASE WRITE THIS IN SQL QUERY

6.5s Group By Queries on full Redcat DB (MS SQL Server)

For each information request below, formulate a single SQL query to produce the required information. In each case, you should display only the columns requested. Be sure that your queries do not produce duplicate records unless otherwise directed.

1. For each category of product, what is the average list price for the products in that category. Name the column AveragePrice. Round the answer to two decimal places. Only show category and rounded average price.

2. For each category and color, what is the maximum list price of men's shoes in that category and color? Sort by Category. Name the column MaximumPrice. Only show category, color, and maximum price.

3. For each manufacturer in California, Oregon, or Washington, what is the minimum list price of a product from that manufacturer? Name column MinimumPrice. Only show Manufacturer name and the minimum price.

4. For each manufacturer in New York, what is the average list price of products from that manufacturer? Name the column AveragePrice. Round the answer to two decimal places for currency. Show manufacturer name and average price.

5. For each manufacturer in New York that supplies at least 10 products, what is the average list price of products from that manufacturer? Name column AveragePrice. Round to two decimal places. Show manufacturer name and rounded average price.

6. What are the largest amounts for a sale item (SalePrice * Quantity) made to each customer (first and last names) during November 2015. Name the column MaxAmount.

7. What are the largest amounts for a sale item (SalePrice * Quantity) made to each customer (first and last name). Include only customers who have bought a sale item over $225. Name the column MaxAmount.

8. What are the dollar amounts of each Sale (add all SaleItems amounts for each sale) made on Jan 1, 2015 for each customer? Include CustomerID, SaleID. Name the column SumSaleAmt.

9. What are the dollar amounts of each Sale (add all SaleItem amounts) for each customer? Include CustomerID, SaleID, but only inlude sales larger than $600. Name the column TotalAmt.

10. For each manufacturer (ID only) what is the highest price for any product in each category? Show ManufacturerID, category, and the highest price. Order it by Manufacturer. Name the column MaxPrice.

Manufacturer Manufacturer!D ManufacturerName Addressi Address2 City State PostalCode Phone Fax Contadt URL Customer Product 1ProductD CustomeriD FirstName ManutactureriD Composition Category Country Description Saleltem nventoryltem Purchaseltem Producto 1 *d ? Product ID ProductID SalelD Quantity SalePrice ItemSize PurchaselD Quantity PurchasePrice OtyOnHand ItemSize Sale SaleID Purchase PurchaselD CustomerlD PurchaseDate EmployeeID ExpectedDelveryDate ManufacturerD Shipping EmployeelD Shipping SalaryEmployee EmployeelD Salary City Pnone WageEmplayee EmployeelD Wage MaxHours ManageD SSN milAddress HireDate