Exercise #70 (tips and solutions)

Here we want to consider some transparent solution with two slight mistakes. Here is it (pay attention on commentaries):

SELECT q.battle
FROM (
    --Determine ships from Ships table which taking part in battles
    SELECT Outcomes.battle, Outcomes.ship, Classes.country
    FROM Classes 
        INNER JOIN Ships ON Classes.class = Ships.class 
        INNER JOIN Outcomes ON Ships.name = Outcomes.ship
    UNION
    --Determine leading ships from the Outcomes table
    SELECT Outcomes.battle, Outcomes.ship, Classes.country
    FROM Outcomes 
        INNER JOIN Classes ON Outcomes.ship = Classes.class
    ) as q
GROUP BY q.battle
HAVING COUNT(q.country) >= 3;
🚫
[[ error ]]
[[ column ]]
NULL [[ value ]]

We hope that you`ll easily find this two mistakes and correct it.

To return to discussion of exercise #70

To solve a problem on SQL-EX.RU