10:26

# 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
`SELECT maker, a.model a_m, b.model b_m, c.model c_mFROM Product a, PC b, Laptop c WHERE ((b.speed >= 750 AND         c.speed >= 750        ) AND         a.model = b.model       ) OR        ((b.speed >= 750 AND          c.speed >= 750        ) AND          a.model = c.model       );`

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
`SELECT makerFROM (SELECT maker       FROM Product INNER JOIN            PC ON Product.model = PC.model       WHERE type='PC' AND             speed >= 750      UNION ALL      SELECT maker       FROM Product INNER JOIN             Laptop ON Product.model = Laptop.model      WHERE type='laptop' AND             speed >= 750      ) SGROUP 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
`SELECT makerFROM ProductWHERE model IN (SELECT model                 FROM PC                 WHERE speed >= 750                 UNION ALL                SELECT model                 FROM Laptop                 WHERE speed >= 750                )GROUP BY maker;`

 Pages 1 2 3 4