loading..
Русский    English
14:40
go

Exercise #8

Find the makers producing 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 evaluated for each record in the source specified by the FROM clause, i.e. for each row in the Product table in our case. A row in this table represents a model that can be of a single type only – either a PC, or a laptop, or a printer. Thus, if the first condition (type= 'PC') is true, the second one – NOT (type = 'laptop') will always be true as well. In other words, this second condition is just redundant. What we need is to make sure there is no record of type laptop for a manufacturer having records of type PC.

The second solution, while logically correct, misinterprets the subject area – we discussed this mistake earlier in this book:

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, for each maker is checked whether he has models in the PC table and have no models in the Laptop table. The mistake consists both in including makers in the result set that don’t belong there (in case all laptop models by a PC maker are missing from the Laptop table while present in the Product table) and in excluding those to be displayed (if there are no models in the PC table by a maker not manufacturing any laptops for the current database state).

In conclusion let me give you once more the following treatment of the subject matter. The 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

Solve this task at SQL-EX.RU
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 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.