Exercise #17 (tips and solutions)

Thus, let’s get rid of the Cartesian product. To do so, we remove the PC table from the  FROM clause, and join the Product and Laptop tables based on the model column:

SELECT DISTINCT type, Laptop.model, speed
FROM Laptop, Product
WHERE Product.model = Laptop.model AND
      Laptop.speed < (SELECT MIN(speed) FROM PC);

The condition p.type = ’laptop’ is superfluous by itself, since the inner join will return models of this type only. As it turns out the join can be omitted altogether, since the Product table is used solely for displaying the product type in the result set. However, the product type is known beforehand – it’s a laptop, and therefore we can just use an expression (a constant) to specify the type, thus removing the join:

SELECT DISTINCT 'Laptop', model, speed
FROM Laptop
WHERE speed < (SELECT MIN(speed) FROM PC);

Note that this solution will be valid only if the Laptop table is reserved for products of type ’laptop’. This is the case for our database, since there are just three types of products with three respective tables. Hence, a violation of this condition can occur only as a result of database restructuring, which, however, should be borne in mind as well when developing applications with built-in SQL queries.

Return to discussion of exercise #17

Solve this task at SQL-EX.RU