Русский    English

Exercise #3

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:

  1. SELECT model, ram, screen
  2. FROM Laptop
  3. WHERE price > '1000'

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  A database management system (DBMS) by Microsoft Corporation. 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:

Disallowed implicit conversion from data type varchar to data type money

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:

  1. SELECT model, ram, screen
  2. FROM Laptop
  3. WHERE price > CAST('1000' AS MONEY)

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

Bookmark and Share
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
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.