loading..
Русский    English
09:41

Exercise #8

Find out the makers that sale PCs but not laptops.

Let us to start with beginner's error

Console
Execute
  1. SELECT DISTINCT maker
  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:

Console
Execute
  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.

T&S

To solve the problem on SQL-EX.RU

Bookmark and Share
Tags
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 CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100