loading..
Русский    English
18:42
go

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
Execute
  1. SELECT ROUND(SUM(ng)/SUM(cnt), 2) res
  2. FROM (SELECT SUM(numGuns) ng, COUNT(*) cnt
  3.  FROM Classes c,Ships s
  4.  WHERE c.class = s.class AND 
  5.  c.type = 'bb'
  6.  UNION ALL
  7.  SELECT SUM(numGuns) ng, COUNT(*) cnt
  8.  FROM Classes c, Outcomes o
  9.  WHERE c.class = o.ship AND 
  10.  c.type = 'BB'AND 
  11.  NOT EXISTS (SELECT name FROM Ships s
  12.  WHERE s.name = o.ship)
  13.  ) 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
Execute
  1. 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:

  1. 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
Execute
  1. 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
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
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.