Exercise #8 |
||
Find the makers producing PCs but not laptops. Let us to start with beginner's error
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:
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. |