loading..
Ðóññêèé    English
13:26

Exercise #25 page 1

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.

Solution 1.17.1

Console
Execute
  1. SELECT c.maker
  2. FROM Product c,
  3.      (SELECT b.model, MAX(b.speed) speed
  4.       FROM PC b
  5.       WHERE b.ram IN (SELECT MIN(a.ram)
  6.                       FROM PC a
  7.                       )
  8.       GROUP BY b.model
  9.       ) t
  10. WHERE c.model = t.model AND
  11.       EXISTS (SELECT d.model
  12.               FROM Printer d, Product e
  13.               WHERE d.model = e.model AND
  14.                     e.maker = c.maker
  15.               );

1. Error in the subquery

  1. (SELECT b.model, MAX(b.speed) speed
  2. FROM PC b
  3. WHERE b.ram IN (SELECT MIN(a.ram)
  4.                 FROM PC a
  5.                 )
  6. GROUP BY b.model
  7. ) 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).

2. Error in defining of the makers of printers

  1. AND EXISTS (SELECT d.model
  2.             FROM Printer d, Product e
  3.             WHERE d.model=e.model AND
  4.                   e.maker = c.maker
  5.             )

We already discussed this question (item 1.2).

3. But we have not yet detected the main error of the decision. We will be exploring this problem, having removed the previous errors.  Duplicates are removed in the below 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.

Solution 1.17.2

Console
Execute
  1. SELECT DISTINCT maker
  2. FROM Product
  3. WHERE type = 'printer' AND
  4.       maker IN(SELECT maker
  5.                FROM Product
  6.                WHERE model IN(SELECT model
  7.                               FROM PC
  8.                               WHERE speed = (SELECT MAX(speed)
  9.                                              FROM (SELECT speed
  10.                                                    FROM PC
  11.                                                    WHERE ram=(SELECT MIN(ram)
  12.                                                               FROM PC
  13.                                                               )
  14.                                                    ) AS z4
  15.                                              )
  16.                               )
  17.                )

Here is how the fastest processor among models with the least RAM capacity is defined:

  1. speed = (SELECT MAX(speed)
  2.          FROM (SELECT speed
  3.                FROM PC
  4.                WHERE ram = (SELECT MIN(ram)
  5.                             FROM PC
  6.                             )
  7.                ) AS z4
  8.                     )

Bookmark and Share
Pages 1 2
Tags
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
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.