Main page
Typical errors at solution of exercises.
Exercise 53
S. Moiseenko
To within two decimal digits, define the average amount of guns for the
battleship classes.
I supposed there is only one problem in this task, and that is rounding. But
recently I received the following solution:
SELECT SUM( sum_g )/SUM( count_g ) FROM
(SELECT SUM( numGuns ) AS sum_g , COUNT(*) AS count_g
FROM Classes INNER JOIN Ships ON
Classes.class = Ships.class
WHERE type='bb'
UNION
SELECT SUM( numGuns ) AS sum_g , COUNT(*) AS count_g
FROM Classes INNER JOIN Outcomes ON
Classes.class = Outcomes.ship
WHERE type='bb') AS a
It's very rich for mistakes analysis J . Let's start from the rounding. The
number of guns is an integer (according to the column type, not to the logic!).
That's why the sum itself will be a whole number. When dividing integers in SQL
Server we always get an integer. And the result is achieved not by rounding but
by DISCARDING the fractional part. For example, execute the following query:
SELECT 2/3
The result will be 0, and that confirms what we've said. So, to make cosmetic
improvements of this query, at least one operand should be converted to real
type. As I've written in the Help at www.sql-ex.ru
site, you can use implicit type conversion:
SELECT SUM( sum_g )*1.0/SUM( count_g )
that is, when multiplying by a real unity the numerator becomes a real number
itself.
Now, as we need to count the average by classes, at first, we shouldn't take
ships into account and, second, we don't need to remember ships from the
Outcomes table.
But for analyzing the mistakes let's consider the solution in its author's
interpretation, that is, we'll define the average value by all linear ships
from the database, and this turns out to be task 54. I've discussed that task
but with another mistake.
So, the number of guns and amount are counted separately by ships from the Ships
table and by the head ships from the Outcomes table. Then, in the main query we
summarize the number of guns and the amount of ships by every table and divide
the first by second to get the average value.
Let's discuss an example. Let there be 2 ships with 11 and 8 guns in the Ships
table, and 1 ship with 11 guns in the Outcomes table. So we get 3 ships with 30
guns. The average value is 30/3=10. Is this correct? No, that is, it's correct
only for certain cases, while we need to write a query that will be correct for
any data. I see several examples to prove this.
First. What if there is no head ship that corresponds to the
terms of the task in the Outcomes table? Then the second query will return: 0
ships, the number of guns is NULL. As a result of calculating the average we'll
get
(19 + NULL)/(2+0) = NULL
instead of 10.
Second. Let the head ship of ‘bb' class be both in the Ships and the
Outcomes table, that means, it's the same ship. Then we should get 19/2 but not
30/3 as the solution presents.
Third. But what if in the previous situation on the ships the
head vessel took part in battles twice? Then we'll get (19 + 22) /( 2 + 2)
instead of our 19/2.
Fourth… Think of it yourself. That's how the checking base on the site
is formed J .
» Given examples here can be done directly on the website by selecting the check
box “Without checking” on the page with
SELECT exercises .
Main page