loading..
   English
10:38

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

The second mistake is logical  and its cause is an unjustified use of BETWEEN predicate. Here is the solution:

Console
Execute
  1. SELECT model, speed, hd
  2. FROM PC
  3. WHERE price < 600
  4. 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 wont 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 [3] 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
Execute
  1. SELECT PC.model, PC.speed, PC.hd
  2. FROM PC
  3. WHERE (PC.cd = '12x' OR
  4. PC.cd = '24x')
  5. AND price < 600;

To solve the problem on SQL-EX.RU

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
yesterday
average conveyancing fees
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100