Books and articles about SQL Сменить язык на: Русский 24 February 2024 16:49:05

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


 Usage of any materials of this site is possibleonly under condition of mandatory allocation of the direct link to a sitehttp://www.sqlbooks.ruon each page where used materials are placed. Main Articles Books