Common mistakes in solving SELECT statement exercises

Computer Firm Database

There is one more attempt to “change” the situation in the better way: 

Solution 1.15.2

Console

Execute

SELECT DISTINCT maker 
FROM Product a, PC b, Laptop c 
WHERE b.speed >= 750 AND 
 c.speed >= 750 AND 
 (a.model = b.model OR 
 a.model = c.model
 );

SELECT DISTINCT maker FROM Product a, PC b, Laptop c WHERE b.speed >= 750 AND c.speed >= 750 AND (a.model = b.model OR a.model = c.model );

    Scheme

Using an equality of predicates, 

x AND (y OR z) = (x AND y) OR (x AND z),

Console

Execute

SELECT DISTINCT maker 
FROM Product a, PC b, Laptop c 
WHERE ((b.speed >= 750 AND 
 c.speed >= 750
 ) AND 
 a.model = b.model
 ) OR 
 ((b.speed >= 750 AND 
 c.speed >= 750
 ) AND 
 a.model = c.model
 );

SELECT DISTINCT maker FROM Product a, PC b, Laptop c WHERE ((b.speed >= 750 AND c.speed >= 750 ) AND a.model = b.model ) OR ((b.speed >= 750 AND c.speed >= 750 ) AND a.model = c.model );

    Scheme

The rows that satisfy even one of predicates, connected with an OR operator, will appear in the result set. Let's consider, for example, a query with the first predicate: 

Console

Execute

SELECT DISTINCT maker 
FROM Product a, PC b, Laptop c 
WHERE ((b.speed >= 750 AND 
 c.speed >= 750
 ) AND 
 a.model = b.model
 );

SELECT DISTINCT maker FROM Product a, PC b, Laptop c WHERE ((b.speed >= 750 AND c.speed >= 750 ) AND a.model = b.model );

    Scheme

Let's rewrite it in a more convenient way from the syntax point of view: 

Console

Execute

SELECT DISTINCT maker 
FROM Product a JOIN 
 PC b ON a.model = b.model, 
 Laptop c 
WHERE (b.speed >= 750 AND 
 c.speed >= 750
 );

SELECT DISTINCT maker FROM Product a JOIN PC b ON a.model = b.model, Laptop c WHERE (b.speed >= 750 AND c.speed >= 750 );

    Scheme

Console

Execute

SELECT DISTINCT maker 
FROM (SELECT maker 
 FROM Product a JOIN 
 PC b ON a.model = b.model
 WHERE b.speed >= 750
 ) x, 
 (SELECT * 
 FROM Laptop c 
 WHERE c.speed >= 750
 ) y;

SELECT DISTINCT maker FROM (SELECT maker FROM Product a JOIN PC b ON a.model = b.model WHERE b.speed >= 750 ) x, (SELECT * FROM Laptop c WHERE c.speed >= 750 ) y;

    Scheme

Now we may analyze it. First subquery, which we marked as “x”, joins table PC with table Product on a foreign key, selecting the producers of PC with the speed >=750. Second subquery (“y”)  filters models of laptops with the speed >=750. 

The way of joining “x” and “y” is called Cartesian product. That is the producer of required PCs in the result set will match with EVERY model of laptop, even if it was produced by ANOTHER maker.

As a result, we will get again the producers that can make only something one. Some difference in comparison with the first solution is in that if NOONE produces laptops with a required speed, then we'll get the empty result set. The first example 1.15.1 doesn't give this partially correct result. 

The coincidence of results on the main database is completely accidental. It's turned out that those makers, who produce PC, required by task conditions, also produce required laptops. So, in spite of coincidence of results on “visible” database the query is wrong in any scheme-compatible state of DB.