   05:56

# Exercise #5

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

When solving this problem two typical mistakes are usually made.

The first mistake is connected with the assumption, that the value of CD-device speed is integer. As a result the query  Console
`SELECT PC.model, PC.speed, PC.hd FROM PC WHERE PC.cd IN (12, 24) AND price < 600;`

will give out error of adjusting of incompatible data types:

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

Attentive study of data scheme will show that CD column is of varchar type. Therefore, in order to obtain the correct result it is sufficient to rewrite the query in the following way:  Console
`SELECT PC.model, PC.speed, PC.hd FROM PC WHERE PC.cd IN ('12x', '24x') AND price < 600;`

The second mistake is logical  and its cause is an unjustified use of BETWEEN predicate. Here is the solution:  Console
`SELECT model, speed, hdFROM PCWHERE price < 600 AND cd BETWEEN '12x' AND '24x';`

Even if we assume that there are no other models (say 20x CD drive) within the range from 12x to 24x CD drive, the solution won’t be correct according to the rule of comparison of character string values. The rule reads that the strings are compared symbol by symbol till it comes to the first different one. Then the conclusion on the result of comparison of the whole strings is made basing on the result of comparison of different symbols. E.g., it is correct that ‘abcz’ < ‘abd’, as the value of the first different symbol in the first string (‘c’) is less than the value of the corresponding symbol in the second string (‘d’). If the first string is the prefix of the second one (e.g., ‘abc’ and ‘abcdef’), then the comparison ‘abc’ < ‘abcdef’ will be valid.

It should be noted also that the comparison (and accordingly the order of sorting) depends on  COLLATION  parameter. In all the cases, if there is no evidence to the contrary, we will assume that for all the character columns this parameter is set up in the same way on the basis of case-insensitive collation.

BETWEEN predicate is equivalent to simultaneous execution of two simple comparison operators:

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

Basing on the aforementioned, the following values will meet the terms apart from those required according to the conditions of the problem, as for example the following values:

‘130x’, 145’, ‘150000000000y’, and so on

Another way of solution of this simple problem will be to use two predicates of simple comparison:  Console
`SELECT PC.model, PC.speed, PC.hd FROM PC WHERE (PC.cd = '12x' OR  PC.cd = '24x') AND price < 600;`

To solve the problem on SQL-EX.RU  