   16:00

# Exercise #24 page 2

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

Solution 1.16.3  Console
`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 modelFROM PC, laptop l, printer prnWHERE 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 — 2*2*2), 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

 Pages 1 2  