DML-exercises mistakes

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

The mistake consists in the use of

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?!

Typical mistake here is, for example, such approach:

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:

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.