Common mistakes in solving SELECT statement exercises

Computer Firm Database

Find the model number, speed and hard drive capacity of PCs cheaper than $600 having a 12x or a 24x CD drive.

Two typical mistakes occur at solving this exercise.

The first one is related to the intuitive assumption the CD drive speed is integer. Thereby, the query

Console

Execute

SELECT PC.model, PC.speed, PC.hd 
FROM PC 
WHERE PC.cd IN (12, 24) 
AND price < 600;

SELECT PC.model, PC.speed, PC.hd FROM PC WHERE PC.cd IN (12, 24) AND price < 600;

    Scheme

will terminate with an error caused by incompatible data types:

A careful examination of the database schema shows that the CD column has the varchar data type. Therefore, in order to obtain the correct result it is sufficient to rewrite the query as follows:

Console

Execute

SELECT PC.model, PC.speed, PC.hd 
FROM PC 
WHERE PC.cd IN ('12x', '24x') 
AND price < 600;

SELECT PC.model, PC.speed, PC.hd FROM PC WHERE PC.cd IN ('12x', '24x') AND price < 600;

    Scheme

The second mistake represents a logic flaw and lies in inappropriate use of the BETWEEN predicate. Here is a solution containing that mistake:

Console

Execute

SELECT model, speed, hd
FROM PC
WHERE price < 600 
AND cd BETWEEN '12x' AND '24x';

SELECT model, speed, hd FROM PC WHERE price < 600 AND cd BETWEEN '12x' AND '24x';

    Scheme

Even if we assume there are no models having a CD drive speed between 12x to 24x (say, 20x), the solution still won’t be correct because of the string comparison rule. This rule says strings are compared character by character till a difference is encountered. Then, the result of the comparison of the entire strings completely depends on the comparison of these differing characters. E.g., ‘abcz’ < ‘abd’ is true, since the first differing character of the first string (‘c’) precedes the corresponding character of the second string (‘d’). If the first string represents a prefix of the second one (e.g., ‘exercise’ and ‘exercises), then the comparison ‘exercise’ < ‘exercises’ is true.

It should be noted that the way springs are compared (and hence the sorting order) depends on the  COLLATION [3] setting. If not explicitly specified otherwise, we’ll always assume all columns of a string data type have the same collation setting, namely the one providing for case-insensitive comparison.

The BETWEEN predicate is equivalent to a combination of two simple comparison operators:

cd >= ‘12x' AND cd <= '24x'

Another way to solve this easy task is using two simple comparison operators:

Console

Execute

SELECT PC.model, PC.speed, PC.hd 
FROM PC 
WHERE (PC.cd = '12x' OR 
 PC.cd = '24x') 
AND price < 600;

SELECT PC.model, PC.speed, PC.hd FROM PC WHERE (PC.cd = '12x' OR PC.cd = '24x') AND price < 600;

    Scheme