loading..
Русский    English
12:49

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:

Console
Execute
  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.

T&S

To solve the problem on SQL-EX.RU

Bookmark and Share
  • Дачный поселок эконом класса
    Коттеджный поселок окруженный лесом. Пруд, рыбалка, инфраструктура
    sb195.ru
  • Заказ линз
    Информация о доставке. FAQ по заказу и применению продукции.
    para-linz.ru
  • Аренда вышки
    Аренда шаланды по Москве и М/О. Низкие цены! Круглосуточно! Без выходных
    saphireavto.ru
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100