Exercise #16

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:

Solution 1.12.1

  1. SELECT MAX(model) AS 'model', MIN(model) AS 'model', speed, ram
  2. FROM PC
  3. GROUP BY speed, ram
  4. 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. Extremal characteristics are mentioned for uniqueness, i.e. to get the pairs 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

Below is presented almost correct solution, though also the too bulky.

Solution 1.12.2

  1. SELECT P.model, L.model, P.speed, P.ram
  3. (SELECT speed, ram
  4. FROM PC
  5. GROUP BY speed, ram
  6. HAVING SUM(speed)/speed = 2 AND
  7. SUM(ram)/ram = 2
  8. ) S ON P.speed = S.speed AND
  9. P.ram = S.ram JOIN
  10. PC L ON L.speed = S.speed AND
  11. L.ram = S.ram AND
  12. L.model < P.model;

Here in subrequest S unique pairs of characteristics (speed, memory) are selected, which coincide for two computers (SUM (speed)/speed = 2) - the sum of identical values divided by this value gives us quantity of the PCs. Though with the same success it was possible to write the following HAVING clause:

  1. HAVING COUNT(*) = 2

The subquery joined with the table PC on this characteristics pair twice. In so doing, the second join is only carried out to order the models (L.model <P.model).

The mistake of the given decision consists that the number of the personal computers with identical characteristics may be more than two. In this situation any of such models will not get in result set of the presented solution.

In spite of the fact that the solution is easy to correct, better to rewrite it in not a such superfluous form.

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.


To solve the problem on SQL-EX.RU

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100