Typical mistakes at the solution of SELECT statement exercises

Database "Computer firm"

Find out the makers that sale PCs but not laptops.

Let us to start with beginner's error

Console

Execute

SELECT DISTINCT maker 
FROM Product 
WHERE type = 'PC' AND 
 NOT (type = 'laptop');

SELECT DISTINCT maker FROM Product WHERE type = 'PC' AND NOT (type = 'laptop');

    Scheme

A predicate in a WHERE clause is to be checked for each row formed by FROM clause, i.e. for each row in the Product table in our case. A row in this table presents a model, which can be only something one - either PC or  laptop or printer. Because of this if the first predicate (type= 'PC') is evaluated as true,  the second one will be true automatically - NOT(type = 'laptop'). In other words, the second predicate is unnecessary here. But we need to be convinced of absence of row that has type of laptop for the same maker if there is a row where type is PC.

The second solution, which is logically correct, is based on the incorrect treatment of subject domain that have been discussed earlier:

Console

Execute

SELECT DISTINCT p.maker 
FROM Product p INNER JOIN 
 PC ON p.model = PC.model
WHERE p.maker NOT IN (SELECT ip.maker 
 FROM Laptop il INNER JOIN 
 Product ip ON il.model = ip.model
 );

SELECT DISTINCT p.maker FROM Product p INNER JOIN PC ON p.model = PC.model WHERE p.maker NOT IN (SELECT ip.maker FROM Laptop il INNER JOIN Product ip ON il.model = ip.model );

    Scheme

Here the existence of PC model in the PC table and absence of laptop model of the same producer have been checked. Mistake is that we can receive extra makers (if current state of the Laptop table has not models of a maker, although they exist in the Product table)  and can miss needed ones also (if no models of a maker, which does not produce laptops, are in current state of the database).

In conclusion let me give you once more the following treatment of the subject matter. Product table contains information about models and vendors supplying them.  However other tables (PC, Laptop, Printer) contain particular models for e.g. available for sale in a shop.

T&S

To solve the problem on SQL-EX.RU