Русский    English

Exercise #23 page 4

The following solution uses join.

Solution 1.15.5

  1. SELECT maker
  2. FROM Product INNER JOIN
  3.      PC ON Product.model = PC.model INNER JOIN
  4.      Laptop ON Laptop.model = Product.model
  5. WHERE PC.speed >= 750 AND
  6.      Laptop.speed >= 750
  7. GROUP BY maker;

The idea lies in the following: to have in derived table a column with a manufacturer's name, a column with speed of PC produced by this manufacturer, and also a column with speed of a laptop of the same manufacturer. Therefore, if the necessary restrictions on speeds will be imposed, the rows satisfying to these restrictions should give us what is necessary (a grouping on the manufacturer eliminates duplicates). The idea correct, however, what is joining is not that and not on those predicates which are expected.

Really, the first INNER JOIN will give us the list of manufacturers and nos of PC models. But only of PCs, as model is a primary key in Product table . Therefore the second INNER JOIN with Laptop table (on model column!) will give us empty result set, as none of PCs is present (and cannot be!) in Laptop table.

To achieve this idea, it is necessary to join the manufacturers of the necessary PCs with corresponding manufacturers of laptops not on model, but on the name of the manufacturer, or to use outer join instead of inner join.


To solve the problem on SQL-EX.RU

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