loading..
Ðóññêèé    English
17:25
go

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
Execute
  1. SELECT DISTINCT CASE
  2.                     WHEN PC.price > = l.price AND 
  3.                          PC.price > = prn.price 
  4.                     THEN pc.model
  5.                     WHEN l.price > = PC.price AND 
  6.                          l.price > = prn.price 
  7.                     THEN l.model
  8.                     WHEN prn.price > = l.price AND 
  9.                          prn.price > = pc.price
  10.                     THEN prn.model
  11.                 END AS model
  12. FROM PC, laptop l, printer prn
  13. WHERE PC.price = (SELECT MAX(price) 
  14.                   FROM PC
  15.                   ) AND 
  16.       l.price = (SELECT MAX(price) 
  17.                  FROM Laptop
  18.                  ) AND 
  19.       prn.price = (SELECT MAX(price) 
  20.                    FROM Printer
  21.                    );

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:

  1. WHEN PC.price > = l.price AND 
  2.      PC.price > = prn.price 
  3. 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
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.