Main page
Typical errors at solution of exercises.
Exercise 25
S. Moiseenko
Find the printer makers which also produce PCs with the lowest RAM and the highest-speed processor
among PCs with the lowest RAM. Result set: maker.
The phrase “ which are having least RAM-capacity”- it is the tag line. It is not
redundant, as it could be seemed at first sight. It is not enough to define all
the models with the fastest processor amongst all the PC s , which have least
RAM capacity .
I am explaining all that I have said above for demonstration of the wrong
decisions. The amount of them has been grown very much :-). Here is the first
example:
SELECT c.maker FROM Product c,
(SELECT b.model , MAX( b.speed ) speed
FROM Pc b
WHERE b.ram IN (SELECT MIN( a.ram ) FROM
Pc a)
GROUP BY b.model ) t
WHERE c.model = t.model AND EXISTS
(SELECT d.model FROM Printer d, Product e
WHERE d.model = e.model
AND e.maker = c.maker )
-
Error in the subquery
(SELECT b.model , MAX( b.speed ) speed
FROM Pc b
WHERE b.ram IN (SELECT MIN( a.ram ) FROM Pc a)
GROUP BY b.model ) t
This example selects PC models, which have least RAM capacity, and for each of
these models is defined PC with the fastest processor. The m istake is that the
fastest processor is needed to define for all PCs with least RAM capacity, but
not for each model. Moreover, if the maker will have 2 PC models with least RAM
capacity, he will be caught twice in the result set, whereas the redundancy
reduction in query is absent from the solution (DISTINCT, for example).
-
Error in defining of the makers of printers
EXISTS (SELECT d.model FROM Printer d, Product e
WHERE d.model = e.model AND e.maker = c.maker )
In this example the availability of the printers among the makers of PC models
is defining in the Printer table. The task says that the required maker also
produces printers, but nothing told about availability of the models in the
table Printer. I.e., if the table Product contains the maker of printers,
models of which are absent from the table Printer, then the considered query
will ignore it, although it are agreeable to the task condition.
But we have not yet detected the main error of the decision. We will be
exploring this problem, having removed the previous errors.
SELECT DISTINCT maker
FROM Product
WHERE type = 'printer' AND maker IN
( SELECT maker FROM Product WHERE model IN (
SELECT model FROM
Pc
WHERE speed = (SELECT MAX( speed) FROM
(SELECT speed FROM Pc WHERE
ram = (SELECT MIN(ram) FROM Pc)) AS z4)
)
)
Duplicates are removed in this solution, the makers of printers are defined
correctly, and the overall maximum of the fastest processor among models with
the least RAM capacity is defined:
WHERE speed = (SELECT MAX( speed) FROM
(SELECT speed FROM Pc WHERE
ram = (SELECT MIN(ram) FROM Pc)) AS z4)
What is not yet considered? We shall recall the phrase of the above in the
beginning of the chapter: “which are having least RAM capacity”. In this
solution, we define only models, which have the processor speed coinciding with
the maximal processor speed for models with the least RAM capacity. I explain
in terms of an example. If the least RAM capacity for PC in the database is 64
and there is such PC models:
speed
|
ram
|
600
|
64
|
600
|
128
|
450
|
64
|
The code used for definition of the needed processor speed
SELECT MAX( speed) FROM
(SELECT speed FROM Pc WHERE
ram = (SELECT MIN(ram) FROM Pc)) AS z4
give us 600. Really, it is the maximal processor speed for models with the least
RAM capacity (64). Then we select the models with this speed, among these is
the model {600, 128}, although it does not conform with the task conditions. If
the maker of this model is also producing printers (and he is producing!), and
he is not the maker of the model {600, 64}, we'll get the “Incorrect” when
checking the query on the site. Of course the right selection is only the model
{600, 64} . I h ope it isn't difficult to solve this task now :-).
» 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