Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 23 October 2017 07:02:48


www.sql-ex.ru
Skip Navigation Links  

 

Print  Print version

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

Print  Print version


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.