Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 29 March 2024 12:14:01


www.sql-ex.ru
Skip Navigation Links  

 

Print  Print version

Main page

DML exercises.

S. Moiseenko

» Any exercise must be solved by one query. Let us assume, if on a condition of a task it is required to add in the table some rows, instead of several queries of type

INSERT ... VALUES...

it is necessary to use the operator INSERT with a sub query.

» In one of the tasks it is required to define the maximal speed of available CD ROM. The mistake consists in use

SELECT MAX( cd ) FROM ...

The matter is that speed of CD ROM is stored in text performance (for example, '12x'). At comparison of text constants '4x' appears more, than '24x'. And if is it required to count average speed?!

» In one of exercises it is required to round off the average value of ships' launched year.
Typical mistake here is, for example, such approach:

round (avg (launched), 0)

As I already wrote in the site's help, average value is brought to the type of argument. In above case, it gives integer since the launched column has int type. Thus, no rounding off occurs, as fractional part is simply REJECTED.

These are features of realization in SQL Server. Anyway, it is easy for checking up (though very few people does this):

SELECT AVG (launched) FROM (SELECT 9 AS launched UNION ALL SELECT 10 UNION ALL SELECT 10) x

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 is it necessary to act here?

It should to convert an argument to one of the non-integer numeric types, to calculate an average after that, and already then to round the result.

Main page

Print  Print version


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.