loading..
Ðóññêèé    English
11:34
go

Exercise #17

Get the laptop models that have a speed smaller than the speed of any PC. Result set: type, model, speed.

Mistakes made here have mostly to do with overusing join operations. The most glaring example, the author believes, is the following query:

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';

Here, the FROM clause refers to the Cartesian product of three tables! While using the Product table can be justified to some extent, since you need to display the equipment type in this task, the PC table can safely be excluded from the query – it won't affect the result. Obviously, this solution is not optimal in terms of performance. Besides, memory issues may arise, since the cardinality of the intermediate result can grow huge even for relatively small tables. Remember, the cardinality of the Cartesian product is equal to the product of the cardinalities of its operands. E.g., the Cartesian product of three tables with 100, 500 and 1000 records will contain 50000000 rows!

Nevertheless, this solution is correct, since the DISTINCT statement eliminates all duplicates generated by the Cartesian product.

T&S

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.