Common mistakes in solving SELECT statement exercises

Computer Firm Database

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

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
              );

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               );

    Scheme

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

2. 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).

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

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
                                             )
                              )
               )

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                                              )                               )                )

    Scheme

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
                    )