Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 29 March 2024 17:41:32


www.sql-ex.ru
Skip Navigation Links  

 

Print  Print version

Main page

Typical errors at solution of exercises. Exercise 27

S. Moiseenko

Define the average size of the PC hard drive for each maker that also produces printers. Result set: maker, average capacity of HD.

The most popular error here concerns misunderstanding the specificity of the subject matter . I wrote somewhere about this. But I have to expound the Computers database treatment once more , because I receive letters with requests to explain the mistake in the task solution.

There is information about all the makers of the computer technology in the Product table. At least information is about those makers, who our invented firm is interacting with. All other tables ( PC , Laptop, and Printer ) contain available (e.g. in a store) models of PCs, laptops, and printers. Here the same model can be present more than one times, even with the similar characteristics, because these tables contain synthetic primary key ( code) . Relations “one-to-many” allow unavailability of this or that model. Though, it can be present in the Product table, because there is a maker, who produces this model.

So, when we talk for example about makers of the printers, as in this task, we talk about those legal persons from the Product table, who have value ‘ printer ' in the type field. Easier speaking ,

SELECT DISTINCT maker FROM Product WHERE type = 'printer'

The mistake here is in the fact that the makers of printers are defined as follows:

SELECT DISTINCT ( Product.maker ) FROM Product INNER JOIN Printer ON
Product.model = Printer.model

Thus, the number of the printer makers is limited to the number of makers, whose models (of printers) are available.

Similar mistake occurs sometimes when solving the Task #28, which is formulated as:

Define the average HD size (single value for all makers) of PCs produced by those makers that also produce printers. Result set: average size of HD.

» Given examples here can be done directly on the website by selecting the check box “Without checking” on the page with SELECT exercises.

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.