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

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

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 return to discussion of exercise #26

To solve a problem on SQL-EX.RU