Chapter 10 Exercises

Exercise 1

Write a query to return a list of products in order of the product name, the list price, and the highest price for which that product has sold. Use an in-line, scalar query to calculate the highest sales price for the product. Columns in the result set should be labeled ProductName, ProductListPrice, and MaxSalesPrice.

Answers

 SELECT Name AS ProductName , ListPrice AS ProductListPrice , (SELECT MAX(UnitPrice) AS MaxSalesPrice FROM SalesOrderDetail WHERE ProductID = Product.ProductID GROUP BY ProductID) AS MaxSalesPrice FROM Product ORDER BY Name 

Exercise 2

The following query returns the top 10 employee pay rates and employee names:

 SELECT TOP 10 FirstName + ' ' + LastName AS Name , BaseRate FROM Employee ORDER BY BaseRate DESC 

This query returns the average pay for all other employees as one summary row labeled '(other)' in place of the employee's name. A subquery is used to join all employee rows to the results of the top 10 expression and then to eliminate these from the aggregated group:

 SELECT ‘(Other)’ AS Name, AVG(Employee. LEFT OUTER JOIN  (SELECT TOP 10 EmployeeID, BaseRate FROM Employee ORDER BY BaseRate DESC) AS E ON Employee.EmployeeID = E.EmployeeID WHERE E.EmployeeID IS NULL 

These two queries need to be combined into a UNION query to return a single result set. However, simply adding the UNION statement between them raises errors due to restrictions of the UNION statement. You can work around these limitations by creating two alias tables similar to the one in the join statement of the second query, and selecting columns from them. Rewrite these statements so the final query has three alias table subqueries. Call them E1, E2, and E3. The existing subquery should be renamed from E to E2. Execute the query to verify that it returns 11 rows.

Answers

 SELECT Name, ListPrice FROM  (SELECT TOP 10 Name, ListPrice FROM Product ORDER BY ListPrice DESC) AS E1 UNION  SELECT Name, ListPrice FROM  ( SELECT ‘Other’ AS Name, AVG(Product. LEFT OUTER JOIN  (SELECT TOP 10 ProductID, ListPrice FROM Product ORDER BY ListPrice DESC) AS E2 ON Product.ProductID = E2.ProductID WHERE E2.ProductID IS NULL ) AS E3