Typical mistakes at the solution of SELECT statement exercises

Tips and solutions

So, get rid of the Cartesian product. To do this, we remove the PC table from FROM clause, and join Product and Laptop tables on ‘model’ column:

Console

Execute

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

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

    Scheme

Condition p.type = 'laptop' is unnecessary, because the internal joining will contain only model of this type. It turns out that join is no needed also, because Product table is used only for product type output in result set. But the product type is known in advance that a laptop computer, therefore we can simply use the expression (a constant) to specify the type, removing the join:

Console

Execute

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

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

    Scheme

Note that this decision will be valid only if the products with ‘laptop’ type will be located in Laptop table. For our database this condition is satisfied, because there are only three types of products and, accordingly, three tables. Therefore, a violation of this condition may be associated only with the restructuring, which, however, should also be borne in mind when developing applications with built-in SQL queries.

To return to discussion of exercise #17

To solve a problem on SQL-EX.RU