Common mistakes in solving SELECT statement exercises

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

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;

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;

    Scheme

Solution 4.12.2

Console

Execute

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;

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;

    Scheme

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