Русский    English

Exercise #8

Find out the makers that sale PCs but not laptops.

Let us to start with beginner's error

  2. FROM Product
  3. WHERE type = 'PC' AND
  4. NOT (type = 'laptop');

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:

  1. SELECT DISTINCT p.maker
  2. FROM Product p INNER JOIN
  3. PC ON p.model = PC.model
  4. WHERE p.maker NOT IN (SELECT ip.maker
  5. FROM Laptop il INNER JOIN
  6. Product ip ON il.model = ip.model
  7. );

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.


To solve the problem on SQL-EX.RU

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.