Main page
Typical errors at solution of exercises.
Exercise 54
S. Moiseenko
To within two decimal digits, define the average amount of guns for all the
battleships (taking into account Outcomes table).
As a rule, there are no more problems with rounding. But same mistake is
regularly admitted. Here is the query that contains it:
SELECT CAST( AVG( numGuns *1.0) AS NUMERIC(10,2)) FROM
(
SELECT numguns
FROM [Classes] c JOIN Ships s ON c.class = s.class
WHERE type='bb'
UNION ALL
SELECT numguns
FROM [Classes] c JOIN Outcomes o ON c.class = o.ship
WHERE type='bb' and o.ship NOT IN(SELECT name FROM
Ships)
) t
In the request two queries are combined (UNION ALL). The first one defines the
number of guns for the ships from the Ships table which belong to linear ships'
classes (‘bb' type). The second one takes into account the head ships of
corresponding classes if they are not in the Ships table.
So, it's an attempt to take into account each ship in the database only once. As
UNION ALL is used for combining data, the duplicates won't be removed. This is
important because many ships will have same number of guns and only this column
is printed out in the SELECT clause of this sub query.
But however the mistake is raised by UNION ALL. Let's take a formal turn, that
is, we won't think of the subject area but will refer to the scheme. In the
Ships table the primary key is the ship's name, that's why the first query in
combination will give us one string for each ship of a known class. But in the
Outcomes table the key is a pair of {ship, battle}, that means, its unique
value is granted for a combination of a ship's name and a battle in which it
took part. What results from this is that the same vessel can be mentioned in
the Outcomes table for several times if it participated in several battles.
As a result, the second query will return duplicated ships if the leading ship
took part in several fights. That's what makes this request wrong.
On the other side, we can't write UNION instead of UNION ALL according to the
reason stated above.
Conclusion. It's not enough to perform the conjunction by only the numguns
column. It seems to me I've said everything 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