Exercise #54 (tips and solutions)

In order to employ UNION in the solution 3.10.2, it is not enough to perform unioning by only numGuns column. It is required of a list of columns that it should determine a ship unambiguously. Then duplicates rather then useful information will actually be excluded.

Below, a solution is presented which makes use of this expedient but contains a small mistake. And we leave it to the reader to find it.

SELECT CAST(AVG(numguns*1.0) AS NUMERIC (6,2))
FROM (SELECT ship, type, numguns
      FROM Outcomes RIGHT JOIN
           Classes ON ship = class
      UNION
      SELECT name, type, numguns
      FROM Ships s JOIN
           Classes c ON c.class = s.class
      ) AS al_sh
WHERE type = 'bb';

To return to discussion of exercise #54

To solve a problem on SQL-EX.RU