   05:14

# Exercise #5

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
`SELECT PC.model, PC.speed, PC.hd FROM PC WHERE PC.cd IN (12, 24) AND price < 600;`

will terminate with an error caused by incompatible data types:

Conversion failed when converting the varchar value '12x' to data type int.

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
`SELECT PC.model, PC.speed, PC.hd FROM PC WHERE PC.cd IN ('12x', '24x') AND price < 600;`

The second mistake represents a logic flaw and lies in inappropriate use of the BETWEEN predicate. Here is a solution containing that mistake:  Console
`SELECT model, speed, hdFROM PCWHERE price < 600 AND cd BETWEEN '12x' AND '24x';`

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  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'

Based on the said above, we can conclude that apart from the values ‘12x’ and ‘24x’ that should pass the check according to the task, this predicate yields true for a number of other values,   say,

‘130x’, ‘145’, ‘150000000000y’, etc.

Another way to solve this easy task is using two simple comparison operators:  Console
`SELECT PC.model, PC.speed, PC.hd FROM PC WHERE (PC.cd = '12x' OR  PC.cd = '24x') AND price < 600;`  