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
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:
The second mistake is logical and its cause is an unjustified use of BETWEEN predicate. Here is the solution:
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: