Common mistakes in solving SELECT statement exercises

Computer Firm Database

Find the model number, RAM and screen size of the laptops with prices over $1000.

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

Console

Execute

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

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

    Scheme

In  SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.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  A database management system (DBMS) by Microsoft Corporation.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:

Console

Execute

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

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

    Scheme

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?