Exercise #24

Below is erroneous solution that has been admitted by site’s checking system some time ago.

Solution 1.16.1

SELECT  model
FROM (SELECT  model, price
      FROM PC
      WHERE price = (SELECT MAX(price)
                     FROM PC
                     )
      UNION
      SELECT  model,  price
      FROM Laptop
      WHERE price = (SELECT MAX(price)
                     FROM Laptop
                     )
      UNION
      SELECT  model,  price
      FROM Printer
      WHERE price = (SELECT MAX(price)
                     FROM Printer
                     )
      ) T
WHERE price = (SELECT MAX(price)
               FROM Laptop
               );

I suggest to clear up what data must be in the test database to block such solutions.

So, what does this query do? In each of three similar subqueries models from three types of products - PC, PC-laptop or printer - are selected by maximum price. Then UNION is used to combine the found models and besides to remove duplicated rows {model, price}. At last, the models having the same price as the maximum price for PC-laptops are left.

So if the maximum price over all products coincides with the maximum price on printers this solution will be rejected by the system. But then a solution having

WHERE price = (SELECT MAX(price)
               FROM Printer  
               )

as a last string will be taken. What’s more, if the maximum price will be only for one type of products (printers, for instance), even more incorrect solution will be passed through checking:

Solution 1.16.2

SELECT   DISTINCT  model
FROM Printer
WHERE price = (SELECT MAX(price)
               FROM Printer
               );

Conclusion. Whatever the data may be, with the help of the first query the solution can be adjusted in three attempts, in the worst case. The second solution won’t pass at all, if the maximum is reached for at least two types of products. But then for adjusting the first solution we’ll need only two attempts. If in every type of product there is a model with the same maximum price, one try will be enough.

By the way, the data is picked up optimally for the examined cases, but, anyway, it doesn’t prevent from the wrong queries’ passage.

Way out of this situation, and not only this, can be found in increasing the amount of test databases, where different data variants will be simulated. The only thing that prevents me from doing this is slowing down the system’s work, which will cause the user to spend more time waiting. I also comfort myself by a thought that our visitors’ motivation is SQL study and qualification improvement but not a tendency to fool the system.

Lets consider one else method without using UNION, although its wrong. This solution uses join of all models and then selects variants by operator CASE.

Solution 1.16.3

SELECT DISTINCT CASE
                    WHEN PC.price > = l.price AND
                         PC.price > = prn.price
                    THEN pc.model
                    WHEN l.price > = PC.price AND
                         l.price > = prn.price
                    THEN l.model
                    WHEN prn.price > = l.price AND
                         prn.price > = pc.price
                    THEN prn.model
                END AS model
FROM PC, laptop l, printer prn
WHERE PC.price = (SELECT MAX(price)
                  FROM PC
                  ) AND
      l.price = (SELECT MAX(price)
                 FROM Laptop
                 ) AND
      prn.price = (SELECT MAX(price)
                   FROM Printer
                   );

The Cartesian product of three tables is using in the FROM clause. Then rows contain models of each type of production with maximal price in its production category are selecting in WHERE clause. Excessiveness of the result isn`t wrong (excessiveness occures for instance in case when two models in each table have maximal price, in this case the result will have eigth rows — 222), because duplicates will be removed by DISTINCT option subsequently.

Next, models with global maximal price are selecting in CASE operator. Mistake is consealed here. The peculiarity of working of operator CASE is in sequentially checking of WHEN clauses. That`s why when condition will became true first, the corresponding THEN clause value will be returned, and the inspection of further WHEN clauses will not be executed.

Let`s consider the next data variant from this point. Let the models of printer and PC has maximal prices. Then the first WHEN clause of CASE operator will be true:

WHEN PC.price > = l.price AND
     PC.price > = prn.price
THEN pc.model

Both predicates are true, indeed. The query returns the model of PC only. To be more precise, the result returns all models of PC with maximal price.

Try to correct this solution without using UNION operator.

To solve the problem on SQL-EX.RU