Exercise #25

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

SELECT c.maker
FROM Product c,
     (SELECT b.model, MAX(b.speed) speed
      FROM PC b
      WHERE b.ram IN (SELECT MIN(a.ram)
                      FROM PC a
                      )
      GROUP BY b.model
      ) t
WHERE c.model = t.model AND
      EXISTS (SELECT d.model
              FROM Printer d, Product e
              WHERE d.model = e.model AND
                    e.maker = c.maker
              );
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]
  1. Error in the subquery
(SELECT b.model, MAX(b.speed) speed
FROM PC b
WHERE b.ram IN (SELECT MIN(a.ram)
                FROM PC a
                )
GROUP BY b.model
) 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).

  1. Error in defining of the makers of printers
AND EXISTS (SELECT d.model
            FROM Printer d, Product e
            WHERE d.model=e.model AND
                  e.maker = c.maker
            )

We already discussed this question (item 1.2).

  1. 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

SELECT DISTINCT maker
FROM Product
WHERE type = 'printer' AND
      maker IN(SELECT maker
               FROM Product
               WHERE model IN(SELECT model
                              FROM PC
                              WHERE speed = (SELECT MAX(speed)
                                             FROM (SELECT speed
                                                   FROM PC
                                                   WHERE ram=(SELECT MIN(ram)
                                                              FROM PC
                                                              )
                                                   ) AS z4
                                             )
                              )
               )
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

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

speed = (SELECT MAX(speed)
         FROM (SELECT speed
               FROM PC
               WHERE ram = (SELECT MIN(ram)
                            FROM PC
                            )
               ) AS z4
                    )

What is not yet considered? We shall recall the phrase of the above in the beginning of the chapter: “PCs with the lowest RAM and the highest-speed processor among PCs with the  lowest RAM”. Actually, we have here two conditions:

PCs with the lowest RAM

and

PCs with the highest-speed processor among PCs with the  lowest RAM

In solution being inspected, there is used only the second one of these conditions, namely, the solution finds models with speed coincided with the maximum speed among models with lowest RAM.

I explain in terms of an example. If the least RAM capacity for PC in the database is 64 Mb and there are such PC models:

Speedram
60064
600128
45064

The code used for definition of the needed processor speed

SELECT MAX(speed)
FROM (SELECT speed
      FROM PC
      WHERE ram = (SELECT MIN(ram)
                   FROM PC
                   )
      ) AS z4
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]
give us 600. Really, it is the maximal processor speed for models with the least RAM capacity (64). Then we select the models with this speed, among these is the model {600, 128}, although it does not conform with the task conditions. If the maker of this model is also producing printers (and he is producing!), and he is not the maker of the model {600, 64}, we’ll get the “Incorrect” when checking the query on the site.

Of course the right selection is only the model {600, 64}. I hope it isn’t difficult to solve this task now :-).

To solve the problem on SQL-EX.RU