Русский    English

Exercise #7 page 2

It is quite easy to introduce corrections into the query, it is sufficient to substitute the predicate of the simple comparison («=») with the predicate of being among a set of values (IN):

  1. SELECT model, price
  2. FROM PC
  3. WHERE model IN (SELECT model
  4. FROM Product
  5. WHERE maker = 'B' AND
  6. type = 'PC'
  7. )
  8. UNION
  9. SELECT model, price
  10. FROM Laptop
  11. WHERE model IN (SELECT model
  12. FROM Product
  13. WHERE maker = 'B' AND
  14. type = 'Laptop'
  15. )
  16. UNION
  17. SELECT model, price
  18. FROM Printer
  19. WHERE model IN (SELECT model
  20. FROM Product
  21. WHERE maker = 'B' AND
  22. type = 'Printer'
  23. );

Notice that possible pairs of values {model, price} repeating here will be eliminated by UNION operator.

However, redundancy is quite evident in this case: within each of the united queries the models of Producer B are selected. We can eliminate the aforementioned drawback, first completing the union, and then making selection in accordance with the type of producer:

  1. SELECT * FROM (SELECT model, price
  2. FROM PC
  3. UNION
  4. SELECT model, price
  5. FROM Laptop
  6. UNION
  7. SELECT model, price
  8. FROM Printer
  9. ) AS a
  10. WHERE a.model IN (SELECT model
  11. FROM Product
  12. WHERE maker = 'B'
  13. );

Thereby in this case there can be no selection in accordance with the type, and there is no need in one as the model number is unique in Product table, i. e. one and the same number cannot refer to the products of different types. As a result we will make the procedure plan, comprising 8 operations instead of 12, which was the case for the first option of the solution. Accordingly the time of execution of the last query will be less.

IN predicate will be verified for each entry of the union. Therefore the efficiency of execution of such query will depend on how far in the list the target model is situated. It is necessary to scan the whole list in search of the excluded models. Ultimately the longer is the list (that is the more models Producer B offers) the more will be the time of processing of such queries.

Bookmark and Share
Pages 1 2 3
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
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.