Main page
Typical errors at solution of exercises.
Exercise 8
S. Moiseenko
Find out the makers that sale PCs but not laptops.
This is one of the wrong solutions that was passing the test database
some time ago:
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)
A lack of description of a subject matter that was
intentionally made short to fit
the page can be the reason of such solutions.
The solution above finds vendors that has models listed in the PC table and no models
listed in the Laptop table. Actually one will need to use only the
Product table which contains column “type” describing the type of product
(PC, Laptop, or Printer)
to solve this exercise.
The Product table is linked with the other tables by the types of products using
one-to-many relationships.
The latter means that the Product table can include those models that are absent from
the table of the corresponding product type. Thus, if any vendor has models
of laptops that are missing (at the moment) from the Laptop table, the query above
will report this vendor (if it has models listed in the PC
table). In my opinion
it should not.
I have added some data to the test database so this query will not pass anymore.
In conclusion let me give you 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.
Main page