These are queries that use the full Red Cat Database as shown in Figure 3.1. To do these queries you cannot use the SimplifiedSales database. You must use the full Red Cat tables of Customer, Sale, SaleItem, Product, Manufacturer, and Employee tables.

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 manufacturer in New York state 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, to 2 decimal places.

2. 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.

3. 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.

4. Make a list of products that were sold during February 2015. Show ProductID and ProductName. Also include in your result table the maximum and the minimum amount of a saleItem (quantity * SalePrice). Name these fields MaxPrice and MinPrice. Only include those products that have a maximum > minimum. (This means that there were multiple sales during that time period, else the maximum would equal the minimum.) Sort the result by ProductID.

Customer Sale Saleltem Product CustomerlD SaleID SaleID 0, ProductID FirstName SaleDate 0 CustomeriD Manufacturer ManufacturerID O ManufacturerName Address1 Address2 City 0 ProductID Item Size Quantity Sale Price Tax ProductName ManufacturerID Composition ListPrice Gender Category LastName StreetAddress City State Postal Code Country Shipping State PostalCode 0 Phone Color Employee Employeeld FirstName LastName Phone Fax Description Contact URL Address SalaryEmployee Employeeld Salary City State ZIP Phone ManagerID SSN WageEmployee Employeeld Wage MaxHours EmailAddress HlreDate