loading..
Ðóññêèé    English
18:16

Exercise #23 page 2

There is one more attempt to “change” the situation in the better way: 

Solution 1.15.2

Console
Execute
  1. SELECT DISTINCT maker
  2. FROM Product a, PC b, Laptop c
  3. WHERE b.speed >= 750 AND
  4. c.speed >= 750 AND
  5. (a.model = b.model OR
  6. a.model = c.model
  7. );

Using an equality of predicates, 

  1. x AND (y OR z) = (x AND y) OR (x AND z),
we'll perform syntax conversions of the considered query: 

Console
Execute
  1. SELECT DISTINCT maker
  2. FROM Product a, PC b, Laptop c
  3. WHERE ((b.speed >= 750 AND
  4. c.speed >= 750
  5. ) AND
  6. a.model = b.model
  7. ) OR
  8. ((b.speed >= 750 AND
  9. c.speed >= 750
  10. ) AND
  11. a.model = c.model
  12. );

The rows that satisfy even one of predicates, connected with an OR operator, will appear in the result set. Let's consider, for example, a query with the first predicate: 

Console
Execute
  1. SELECT DISTINCT maker
  2. FROM Product a, PC b, Laptop c
  3. WHERE ((b.speed >= 750 AND
  4. c.speed >= 750
  5. ) AND
  6. a.model = b.model
  7. );

Let's rewrite it in a more convenient way from the syntax point of view: 

Console
Execute
  1. SELECT DISTINCT maker
  2. FROM Product a JOIN
  3. PC b ON a.model = b.model,
  4. Laptop c
  5. WHERE (b.speed >= 750 AND
  6. c.speed >= 750
  7. );
and further 

Console
Execute
  1. SELECT DISTINCT maker
  2. FROM (SELECT maker
  3. FROM Product a JOIN
  4. PC b ON a.model = b.model
  5. WHERE b.speed >= 750
  6. ) x,
  7. (SELECT *
  8. FROM Laptop c
  9. WHERE c.speed >= 750
  10. ) y;

Now we may analyze it. First subquery, which we marked as “x”, joins table PC with table Product on a foreign key, selecting the producers of PC with the speed >=750. Second subquery (“y”)  filters models of laptops with the speed >=750. 

The way of joining “x” and “y” is called Cartesian product. That is the producer of required PCs in the result set will match with EVERY model of laptop, even if it was produced by ANOTHER maker.

As a result, we will get again the producers that can make only something one. Some difference in comparison with the first solution is in that if NOONE produces laptops with a required speed, then we'll get the empty result set. The first example 1.15.1 doesn't give this partially correct result. 

The coincidence of results on the main database is completely accidental. It's turned out that those makers, who produce PC, required by task conditions, also produce required laptops. So, in spite of coincidence of results on “visible” database the query is wrong in any scheme-compatible state of DB. 

Bookmark and Share
Pages 1 2 3 4
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.