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