loading..
Русский    English
09:30

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. Here’s a query containing this error:

Console
Execute
  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 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:

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

Console
Execute
  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 don’t really need any conversion?

Solve this task at SQL-EX.RU

Bookmark and Share
Tags
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
several days ago
https://exchangesumo.com/obmen/BTC-UNPCNY-sort
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.