Exercise #17

Find the laptops having speeds less than all PCs. Result set: type, model, speed.

Following errors are associated with overuse of join operations. The most egregious example, the author believes, is the following:

  1. SELECT DISTINCT p.type, l.model, l.speed
  2. FROM Product p, Laptop l, PC c
  3. WHERE l.speed < (SELECT MIN (speed)
  4. FROM PC
  5. ) AND
  6. p.type = 'laptop';

In the FROM clause it is using of the Cartesian product of three tables! If the presence of the Product table can still be somehow justified because is needed to specify also the type of product, then the PC table can be safely removed without affect on the result. Obviously, the decision would not be optimal for speed of execution. In addition, you may have problems with memory, since the power of an intermediate result can be huge even for relatively small tables. Recall that the power of the Cartesian product is the product of power operands. For example, for tables with the number of rows 100, 500 and 1000 of the Cartesian product contains 50000000 row!

And, nevertheless, the decision was correct, because the DISTINCT statement eliminates all duplicates, resulting from the Cartesian product.


To solve the problem on SQL-EX.RU

Bookmark and Share
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.