loading..
Русский    English
19:21

DML-exercises mistakes

Here I analyse some mistakes which have being made when solving DML exercises at sql-ex.ru.

Problem. It is required to define the maximal speed among available CD-ROMs.

The mistake consists in the use of

  1. SELECT MAX( cd ) FROM ...

The matter is that speed of CD ROM is stored as character string (for example, '12x'). While comparing '4x' and '24x', the first will be greater than the second. Imagine that you need to calculate average speed?!

Problem. It is required to round off the average value of ships' launched year.

Typical mistake here is, for example, such approach:

  1. round(AVG(launched), 0)

The source of mistake is that the average value has the type of argument. In above case, it gives integer since the launched column has integer data type. Thus, no rounding off occurs, as fractional part is simply rejected (in SQL Server). Anyway, it is easy for checking up:

 

Console
Execute
  1. SELECT AVG(launched) FROM (VALUES(9),(10),(10)) X(launched);

Obviously, we should receive 29/3, i.e. almost 10. However, we shall receive 9. As a result, function ROUND is already perfect superfluous, since there is nothing to round here.

How we need to act here? You should convert an argument to a non-integer data type, then calculate an average and make rounding the result after that.

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 date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100