loading..
   English
16:18

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
Execute
  1. SELECT PC.model, PC.speed, PC.hd
  2. FROM PC
  3. WHERE PC.cd IN (12, 24)
  4. 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
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 represents a logic flaw and lies in inappropriate use of the BETWEEN predicate. Here is a solution containing that mistake:

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 there are no models having a CD drive speed between 12x to 24x (say, 20x), the solution still wont 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, well 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
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;

Solve this task at 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 DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
https://exchangesumo.com/obmen/ADVCRUB-NixMoney/
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.