Exercise #3

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. Heres a query containing this error:

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

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 arent 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 doesnt implicitly convert string values to data type money. Therefore, the aforementioned query terminated with the following error message:

Disallowed implicit conversion from data type varchar to data type money, table 'Laptop', column 'price'. Use the CONVERT function to run this query.

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, its possible to do an explicit data type conversion; here is a quite legitimate version of this query:

  1. SELECT model, ram, screen
  2. FROM Laptop
  3. 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 dont really need any conversion?

Solve this task at 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
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.