Exercise #3

Another easy task. However, a very instructive mistake occurred among solutions to it. Here’s a query containing this error:

SELECT model, ram, screen
FROM Laptop
WHERE price > '1000';

In SQL statements, string constants are enclosed in single quotation marks. Numeric constants aren’t enclosed in quotation marks. Thus, the final predicate should have been rewritten as price > 1000. However, there is a peculiarity related to implicit type conversion. See Chapter 5 (section 5.9) for more details. Here, we just note that SQL Server 2000 doesn’t implicitly convert string values to data type money. Therefore, the aforementioned query terminated with the following error message:

Note that if the price column had any other numeric data type, i.e. float, an implicit conversion would be done, and no error would occur. Certainly, it’s possible to do an explicit data type conversion; here is a quite legitimate version of this query:

SELECT model, ram, screen
FROM Laptop
WHERE price > CAST('1000' AS MONEY);

If you execute the query at the beginning of this section now, it will return the correct result set without generating any error messages. This is because the server version at the site has been updated – starting with SQL Server 2005, this strange behavior exclusivity of money has been fixed.

Thus, you will get the correct result when using implicit data type conversion. However, what use is it to make the server waste its resources on it when you don’t really need any conversion?

Solve this task at SQL-EX.RU