loading..
Ðóññêèé    English
15:35
go

Exercise #23 page 3

To not be proofless, I'll show the results of original query (solution 1.15.2) with an extension of output columns set:

Console
Execute
  1. SELECT maker, a.model a_m, b.model b_m, c.model c_m
  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.        );

Let's consider a couple of rows from the result set: 

maker a_m b_m c_m
B 1121 1121 1752
A 1752 1121 1752

As you can see, model 1121 (PC) belongs to the maker B, but model 1752 (laptop) – to the maker A. So, we don't have a reason to consider that both these makers satisfy the task conditions.

Uniting of demanded models of the PCs and laptops in one set gives only illusion that we receive both types:

Solution 1.15.3

Console
Execute
  1. SELECT maker
  2. FROM (SELECT maker 
  3.       FROM Product INNER JOIN 
  4.            PC ON Product.model = PC.model 
  5.       WHERE type='PC' AND 
  6.             speed >= 750
  7.       UNION ALL
  8.       SELECT maker 
  9.       FROM Product INNER JOIN 
  10.             Laptop ON Product.model = Laptop.model
  11.       WHERE type='laptop' AND 
  12.             speed >= 750
  13.       ) S
  14. GROUP BY maker;

As a result, the list of manufacturers for which there is at least one row in a set from FROM clause will be received. Below shorter variant of the same mistake.

Solution 1.15.4

Console
Execute
  1. SELECT maker
  2. FROM Product
  3. WHERE model IN (SELECT model 
  4.                 FROM PC 
  5.                 WHERE speed >= 750 
  6.                 UNION ALL
  7.                 SELECT model 
  8.                 FROM Laptop 
  9.                 WHERE speed >= 750
  10.                 )
  11. GROUP BY maker;
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
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.