SQL I5 3351003 - 2022 Sumr 5.35 Correlated subqueries 3 These are queries That use the full Red Cat Database as shown in Figure 3.1. To do these queries you cannot use The SimpliedSales database. You must use the full Red Cat tables of Customer, Sale, Salel'tem, 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. 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, Saleltem, 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. ManufacturerID Of OT SaleID OT SaleID OT ProductID OT CustomerID SaleDate ProductID ProductName ManufacturerName FirstName OT CustomerID ItemSize Of ManufacturerID Address1 LastName Composition Address2 StreetAddress Tax Quantity City ListPrice City Shipping SalePrice State Gender State PostalCode Category PostalCode Employee Phone EmployeeID Color Country Fax Phone FirstName Description Contact LastName SalaryEmployee URL Address EmployeeID OT City Salary State ZIP WageEmployee Phone EmployeeID ManagerID Wage SSN MaxHours EmailAddress HireDateStarted on Aug 01 at 21:13 v Your Submission: 1 2 3 5 6 7 8 1 List Employees (first and last names) and their salaries of employees who have a salary higher than their managers. Use a correlated subquery in the WHERE clause. 1 Save Answer Test Query Open in Advanced EditorV Your Submission: Started on Aug 01 at 21:13 1 2 3 4 5 6 7 8 2 Show employee information for the most recently hired employee in each city. (Most recent is Maximum hiredate). Use a correlated subquery in the WHERE clause. Sequence the data by city. 1 Save Answer Test Query Open in Advanced Editor Prev Nextv Your Submission: Started on Aug 01 at 21 :15 Give a list of Manufacturer names and [Us for manufacturers which only provide products valued over $30, i.e. whose cheapest products list price is over $30. (Hint: Use a correlated subquerj.r in WHERE clause] 1 | V Your Submission: Started on Aug 01 at 21:13 3 5 6 8 4 Give a list of customers (IDs and Names) whose purchases during the month of January 2014 were always the same color. (Hint: Use a single correlated subquery with joins, and count distinct colors of purchased products in the WHERE clause.) 1 Open in Advanced Editor Save Answer Test Query Prev NextV Your Submission: Started on Aug 01 at 21:13 5 6 8 5 Give a list of customers (IDs and names) who have purchased multiple items, but all of which were the same color. Multiple items means more than one sale items. Use what you learned in a previous problem about counting colors. Use subqueries in the WHERE clause. 1 Open in Advanced Editor Save Answer Test Query Prev NextV Your Submission: Started on Aug 01 at 21:13 6 7 8 6 For each manufacturer (ManufacturerID) show the maximum list price of its products. Show only those manufacturers whose products have an average list price below the average list price of products from manufacturers in their state, and put the result in order by state. Use a correlated subquery in the HAVING clause. Show ManufacturerID, State, and MaxListPrice. 1 Open in Advanced Editor Save Answer Test QueryV Your Submission: Started on Aug 01 at 21:13 3 4 5 6 7 8 7 For each ManufacturerID and manufacturer name show the maximum list price of its products. Do this using a correlated subquery in the A SELECT clause. Name the new column "MaxListPrice". 1 Open in Advanced Editor Save Answer Test Query Prev NextV Your Submission: Started on Aug 01 at 21:13 1 2 3 5 6 8 8 For each sale with non-zero tax, list the saleID and saledate, tax, and shipping, as well as the total of the sale. This sale total includes the quantity * saleprice of all the sale items on the sale plus the tax and shipping. Do this with a correlated subquery in the SELECT clause. Show data for February 2015. Name the new column "TotalSale". 1 Open in Advanced Editor Save Answer Test Query