Typical mistakes at the solution of SELECT statement exercises

Database "Computer firm"

Point out the maker and speed of the laptops having hard drive capacity more or equal to 10 Gb

Here is the problem, which for the first time requires use of information in several tables: maker name is in Product table, speed and hd volume are in Laptop table.

Thus, the solution is the following:

Console

Execute

SELECT DISTINCT Product.maker, Laptop.speed
FROM Product, Laptop 
WHERE Laptop.hd >= 10 AND 
 type IN(SELECT type 
 FROM Product 
 WHERE type = 'laptop'
 )

SELECT DISTINCT Product.maker, Laptop.speed FROM Product, Laptop WHERE Laptop.hd >= 10 AND type IN(SELECT type FROM Product WHERE type = 'laptop' )

    Scheme

Thereby the author of this solution claims that this query outputs 5 strings more than in the correct answer, and the query that he considers more adequate:

Console

Execute

SELECT DISTINCT Product.maker, Laptop.speed
FROM Product, Laptop 
WHERE Laptop.hd >= 10

SELECT DISTINCT Product.maker, Laptop.speed FROM Product, Laptop WHERE Laptop.hd >= 10

    Scheme

outputs the result that contains all the types of products.

The mistake lies in the fact that listing of the tables through comma without indication of the method of their connection is nothing but Cartesian product, about which one can read in Chapter 5 (Item 5.6).

We agree with the opinion that the second solution is more correct. It lacks only joining of the tables. At the same time the first one is a mere attempt to fudge, limiting the output of the second «correct» solution using the models of the notebooks. It should be noted that this attempt appears to be a rather clumsy one, as, in case we have not misinterpreted the idea of the author, it was enough to write instead of the predicate

type IN (SELECT type 
 FROM Product 
 WHERE type = 'laptop'
 )

the following

type = 'laptop'

T&S

To solve the problem on SQL-EX.RU