Typical mistakes at the solution of SELECT statement exercises

Database "Computer firm"

It is quite easy to introduce corrections into the query, it is sufficient to substitute the predicate of the simple comparison («=») with the predicate of being among a set of values (IN):

Console

Execute

SELECT model, price 
FROM PC 
WHERE model IN (SELECT model 
 FROM Product 
 WHERE maker = 'B' AND 
 type = 'PC'
 )
UNION
SELECT model, price 
FROM Laptop 
WHERE model IN (SELECT model 
 FROM Product 
 WHERE maker = 'B' AND 
 type = 'Laptop'
 )
UNION
SELECT model, price 
FROM Printer 
WHERE model IN (SELECT model 
 FROM Product 
 WHERE maker = 'B' AND 
 type = 'Printer'
);

SELECT model, price FROM PC WHERE model IN (SELECT model FROM Product WHERE maker = 'B' AND type = 'PC' ) UNION SELECT model, price FROM Laptop WHERE model IN (SELECT model FROM Product WHERE maker = 'B' AND type = 'Laptop' ) UNION SELECT model, price FROM Printer WHERE model IN (SELECT model FROM Product WHERE maker = 'B' AND type = 'Printer' );

    Scheme

Notice that possible pairs of values {model, price} repeating here will be eliminated by UNION operator.

However, redundancy is quite evident in this case: within each of the united queries the models of Producer B are selected. We can eliminate the aforementioned drawback, first completing the union, and then making selection in accordance with the type of producer:

Console

Execute

SELECT * FROM (SELECT model, price 
 FROM PC
 UNION
 SELECT model, price 
 FROM Laptop
 UNION
 SELECT model, price 
 FROM Printer
 ) AS a
WHERE a.model IN (SELECT model 
 FROM Product 
 WHERE maker = 'B'
 );

SELECT * FROM (SELECT model, price FROM PC UNION SELECT model, price FROM Laptop UNION SELECT model, price FROM Printer ) AS a WHERE a.model IN (SELECT model FROM Product WHERE maker = 'B' );

    Scheme

Thereby in this case there can be no selection in accordance with the type, and there is no need in one as the model number is unique in Product table, i. e. one and the same number cannot refer to the products of different types. As a result we will make the procedure plan, comprising 8 operations instead of 12, which was the case for the first option of the solution. Accordingly the time of execution of the last query will be less.

IN predicate will be verified for each entry of the union. Therefore the efficiency of execution of such query will depend on how far in the list the target model is situated. It is necessary to scan the whole list in search of the excluded models. Ultimately the longer is the list (that is the more models Producer B offers) the more will be the time of processing of such queries.