loading..
Русский    English
12:09

Exercise #26 (tips and solutions)

Below there is the pair of incorrect solutions which still contain mistake that is easy to be corrected. 

Solution 4.12.1

Console
Execute
  1. SELECT AVG(price)
  2. FROM (SELECT price
  3. FROM PC
  4. WHERE model IN (SELECT model
  5. FROM product
  6. WHERE maker='A' AND
  7. type='PC'
  8. )
  9. UNION
  10. SELECT price
  11. FROM Laptop
  12. WHERE model IN (SELECT model
  13. FROM product
  14. WHERE maker='A' AND
  15. type='Laptop'
  16. )
  17. ) AS prod;

Solution 4.12.2

Console
Execute
  1. SELECT AVG(price)
  2. FROM (SELECT price, model
  3. FROM pc
  4. WHERE model IN (SELECT model
  5. FROM product
  6. WHERE maker='A' AND
  7. type='PC'
  8. )
  9. UNION
  10. SELECT price, model
  11. FROM Laptop
  12. WHERE model IN (SELECT model
  13. FROM product
  14. WHERE maker='A' AND
  15. type='Laptop'
  16. )
  17. ) AS prod;

The first solution gives the result 772.5, the second - 773.0 while the correct value is 734.5454545454545.

In the query 4.12.1 the prices for all models of producer А are selected from PC table. Then they are united with the prices for all models of producer А from Laptop table. At last, average value is calculated. What's wrong here? The mistake is in that the prices are united. UNION operator excludes duplicates, therefore from the several identical prices (if those are available) the only one will stay. As result, the average value will be counted by incorrect amount.

In the query 4.12.2 not only the price is selected, but so the number of the model as well. I.e. the union is performed by the pair of the attributes. This solution would be correct if there aren't identical models with identical prices in the corresponding tables. The last would be guaranteed if the pair {price, model} be the primary key. However according to our scheme this is not so. At the same time such situation is not unreal. Let's imagine that one PC is replenished by a larger hard drive than that of another PC with the same model number, but the latter contains less memory. Then they quite will have the same prices.

As a result of the union, duplicates of pairs {price, model} will be excluded and, as consequence, the incorrect result will be given.

I hope, now it is quite obvious, how one should solve this problem.

To return to discussion of exercise #26

To solve a problem on SQL-EX.RU

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.