Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 13 December 2017 00:58:01


www.sql-ex.ru
Skip Navigation Links  

 

Print  Print version

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

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.