loading..
Русский    English
06:44

Exercise #7 (tips and solutions)

The following solution employs join instead of union:

Console
Execute
  1. SELECT DISTINCT a.model,
  2. ISNULL(b.price, 0)+ISNULL(c.price, 0)+ISNULL(d.price, 0) price
  3. FROM (((Product a LEFT JOIN
  4. PC b ON a.model = b.model
  5. ) LEFT JOIN
  6. Laptop c ON a.model = c.model
  7. ) LEFT JOIN
  8. Printer d ON a.model = d.model
  9. )
  10. WHERE a.maker = 'B';

In this case we employ three left outer joins of Product table with each of production tables.

The missing values of the price will be filled in with NULL values. I.e., for model 1232 of the PC the price of the laptop and the printer will be NULL. Therefore only one of the three price columns will contain the value for each row of the resulting set. In order not to determine which column it is, within the list of columns of SELECT clause the following construction is used

  1. ISNULL(b.price, 0)+ISNULL(c.price, 0)+ISNULL(d.price, 0),
summing up all the three prices, preliminarily substituting NULL value by 0. The latter is necessary, as summing up with NULL value will give NULL. The use in the query of non-standard ISNULL(price, 0) function is not essential, as it is not less efficient to substitute the whole construction with the standard COALESCE, even without summation:

  1. COALESCE(b.price, c.price, d.price, 0)

And yet the provided solution has one drawback. Just imagine that maker B has got a model, say model 1133, which is not included into production table. Then the result of execution of the query will be the row:

1133 0
with $0 price. Such a result will give fallacious information as there are no products at this price. In order to conform this solution to the solution on the basis of union, which does not output the rows with zero price, one should add the search condition on the price. Do it on your own and check correctness of your solution.

To return to discussion of exercise #7

To solve a problem on SQL-EX.RU

Bookmark and Share
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 CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 exercise 37 More tags
The book was updated
yesterday
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100