Exercise #54

Solution 3.10.1

SELECT ROUND(SUM(ng)/SUM(cnt), 2) res
FROM (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 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.

SELECT AVG(3/2);

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 ROUND function. Again, we turn our attention to a simple example (rounding to two digits after the decimal point):

SELECT ROUND(AVG(5.0/3),2);

Solution 3.10.2

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;

Pay attention to conversion of data type to a number with fixed point, which carries out a demanded rounding off of result.

In the subquery, 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 hand, we can’t write UNION instead of UNION ALL according to the reason stated above.

T&S

To solve the problem on SQL-EX.RU