Exercise #16

Get pairs of PC models with identical speeds and the same RAM capacity. Each resulting pair should be displayed only once, i.e. (i, j) but not (j, i).Result set: model with the bigger number, model with the smaller number, speed, and RAM.

Here is a solution that occurs quite often:

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 have no idea why some users output just the maximum and minimum model for each matching pair of speed and ram values. Maybe the correct result returned by such a query for the main database is misleading.

In this task, all models have to be ordered, not just the maximum and the minimum one. Extreme values are mentioned just for the sake of unambiguity, to make it clear a pair of models has to be displayed once, e. g.

1122 1121
but not

1121 1122

Say, if three models 1122, 1121, and 1135 have identical characteristics, the result should look as follows:

1135 1122
1135 1121
1122 1121

The solution presented below is almost correct, albeit somewhat cumbersome.

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, the subquery S gets the unique pairs of characteristics (speed, memory) coinciding for two computers (SUM (speed)/speed = 2) - the sum of identical values divided by this value yields the number of PCs. However, one could just as well use the following HAVING clause in this case:

  1. HAVING COUNT(*) = 2

The subquery is joined to the PC table PC using this pair of characteristics twice. Thereby, the second join is performed just to order the models (L.model <P.model).

The mistake of this solution is, there may be more than two PCs with identical characteristics. In this case, the query under consideration wont output any of such models.

In spite of the fact this solution can be corrected easily, its preferable to rewrite it in a less redundant form.

One more mistake typical for this exercise is caused by the possible presence of identical computer models in the PC table. Thus, when displaying pairs of PCs you need to remove duplicates.


Solve this task at 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 DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions 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.