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 than 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