Typical mistakes at the solution of SELECT statement exercises

Database "Computer firm"

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

There is another quite easy task. However there is one mistake of instructive value. Here is the solution which contains this mistake:

Console

Execute

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

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

    Scheme

String constants in statements instructions are quoted in single quotes. The constants of numeric types are given without quotation marks. Thus the last predicate should have been written as price > 1000. However there is one peculiarity connected with implicit transformation of the types. You can read about it in detail in Chapter 5 (item 5.9). It should be also noted here that  Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. 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 Server 2000 does not fulfill implicit conversion of the string into money data type value. Therefore the considered query generated the warning message:

One should take note that if the price column were of any other digital type, i.e.,float, then the implicit conversion would be made, and there would be no error. Of course one can make explicit conversion of this type; this 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

Now, if you run the query, which generated error, then it will produce the resulting set and not the aforementioned error message. The matter is that SQL Server 2005, which is currently used on the site, this strange difference in behaviour of money data type from other digital types with respect to implicit conversion is already eliminated.

Thus, you will obtain the correct result when using the implicit conversion of the data type. The only point is that there is no use to make the server waste its resources for it, if one can do without conversion of the data types?

To solve the problem on SQL-EX.RU