Main page
Typical errors at solution of exercises.
Exercise 16
S. Moiseenko
Find the pairs of PC models having similar speeds and RAM. As a result, each
resulting pair is shown only once, i.e. (i, j) but not (j, i). Result set:
model with high number, model with low number, speed, and RAM.
Here is the frequently proposed solution:
SELECT MAX(model) as 'model', MIN(model) as 'model', speed, ram
FROM PC
GROUP BY speed, ram
HAVING MAX(model) > MIN(model)
I really don't know, what was the cause to decide outputting only models with
maximum and minimum numbers for each coinciding pair of values {speed, ram}.
It is possible that the result of inaccurate query on available database is
misleading.
In this task, it is needed to order all the models, but not only maximum and
minimum of these. Extrem al characteristic s are mentioned for uniqueness, i.e.
to get the pa i rs of models once , for example:
1122 1121
but not
1121 1122
So, if three models 1122, 1121, and 1135 have alike features, the result will be
as follows:
1135 1122
1135 1121
1122 1121
There is one more mistake which is often made in this exercise. It is due to the
fact that the PC table may include some computers with same model value. Therefore
it is necessary to exclude possible duplicates of required pairs.
» 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