   14:10

# 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
`SELECT AVG(price) FROM (SELECT price  FROM PC  WHERE model IN (SELECT model  FROM product  WHERE maker='A' AND  type='PC' ) UNION  SELECT price  FROM Laptop  WHERE model IN (SELECT model  FROM product  WHERE maker='A' AND  type='Laptop' ) ) AS prod;`

Solution 4.12.2  Console
`SELECT AVG(price) FROM (SELECT price, model  FROM pc  WHERE model IN (SELECT model  FROM product  WHERE maker='A' AND  type='PC' ) UNION  SELECT price, model  FROM Laptop  WHERE model IN (SELECT model  FROM product  WHERE maker='A' AND  type='Laptop' ) ) 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 solve a problem on SQL-EX.RU  