loading..
Русский    English
22:11

Exercise #7 (tips and solutions)

Here is a solution using 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 perform three left outer joins, combining the Product table with each of the other tables.

Missing prices will be substituted by NULL values. I.e., for PC model 1232, the laptop and printer prices will be NULL. Therefore, for each record returned by the query, only one of the price columns will contain an actual price. To avoid finding out which one exactly, the  SELECT  statement uses the following construct:

  1. ISNULL(b.price, 0)+ISNULL(c.price, 0)+ISNULL(d.price, 0),
that sums up all three prices replacing NULL values by 0 before doing so. The latter is necessary, since addition with NULL yields NULL. Using the ISNULL(price, 0) function that isn’t a part of the SQL standard is not necessary, since the whole construct can be replaced by the standard COALESCE function that does the job as well, and doesn’t even need adding the prices together:

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

And yet this solution has one drawback. Imagine maker B has a model (let it be model 1133) not present in any of the specific product type tables. For this model, the query will return the following row:

1133 0
The price will be $0. This result is incorrect, since there are no products in the database at such a price. To make the output of this query match the one of the UNION-based solution that doesn’t display records with zero prices, another condition for filtering by price has to be added. This is left as an exercise to the reader; after that, you can check your solution.

Return to discussion of exercise #7

Solve this task at 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 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.