   08:53

# Exercise #54 page 1

To within two decimal digits, define the average amount of guns for all the battleships (taking into account Outcomes table).

Solution 3.10.1  Console
`SELECT ROUND(SUM(ng)/SUM(cnt), 2) resFROM (SELECT SUM(numGuns) ng, COUNT(*) cnt FROM Classes c,Ships s WHERE c.class = s.class AND  c.type = 'bb' UNION ALL SELECT SUM(numGuns) ng, COUNT(*) cnt FROM Classes c, Outcomes o WHERE c.class = o.ship AND  c.type = 'BB'AND  NOT EXISTS (SELECT name FROM Ships s WHERE s.name = o.ship) ) x;`

In this solution an attempt has been made to calculate the average manually, as the sum of values divided by their quantity. However, the peculiar feature of aruthmetic operations 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 lies in the fact that the result is always cast to the type of the argument. Since the quantity of guns is a whole number (the numGuns column is of INTEGER type), the fractional part of a number derived from division is simply discarded, which trivially yields the wrong result.

Attention:

The use of AVG function for calculatiing the average does not change the situation, because casting is done by the same rules. It can be easily checked by running the query  Console
`SELECT AVG(3/2);`
which gives 1 rather than 2, should the rounding be completed.

If you are going to run similar queries on the web site, please, tick off the flag 'run without checking' in the exercise page, lest the system should carry out an unnecessary matching of the result with the reference solution for the specific exercise.

To obtain an "accurate" result of division of the whole numbers, one should cast operands (at least one of them) to a decimal. It can be done with the CAST function or through multiplication by the decimal unity, and it is what we are going to do:

`SELECT SUM(numGuns*1.0) ng`

Now let us talk about rounding which employs the T-SQL (Transact-SQL) is Microsoft`s and Sybase`s proprietary procedural extension to SQL.T-SQL ROUND function. Again, we turn our attention to a simple example (rounding to two digits after the decimal point):  Console
`SELECT ROUND(AVG(5.0/3),2);`
which yields 1.670000 by way of result. That is, rounding is done correctly, but insignificant zeroes are preserved, which number corresponds to the number of significant digits used by default for the representation of the decimals. This number, naturally, depends upon which software is installed; so, in this case we are speaking about SQL Server. Here it would be appropriate to note that, when comparing the result with the "correct" solution, values 1.67 and 1.670000 will be considered as different. Therefore one should aslo take care of deleting the zeroes. Let us postpone this problem till the analysis of the next solution, because there this problem, as well as the rounding one, are solved correctly. There we'll also have a good look at a logical error which the solution 3.10.1 harbors.

 Pages 1 2  