Exercise #46 (tips and solutions)

-- Ships which had taken part in the battle of Guadalcanal and are present in the Ships table.
-- Take note of using the correlative subquery in the WHERE clause
-- which solve problem of removing duplicates in Cartesian product.
SELECT a.ship, displacement, numGuns
FROM (SELECT ship
FROM Outcomes
WHERE battle = 'Guadalcanal'
) AS a, Classes
WHERE class IN (SELECT class
FROM Ships
WHERE name = a.ship
)
UNION
-- The query is analogous to one which selects lead ships from
-- Outcomes table which had actioned in battle of Guadalcanal.
SELECT a.ship, displacement, numGuns
FROM (SELECT ship
FROM Outcomes
WHERE battle = 'Guadalcanal'
) AS a, Classes
WHERE class IN (SELECT ship
FROM Outcomes
WHERE ship = a.ship
)
UNION
-- In essence this is the solution in 3.1.1
SELECT a.ship, displacement, numGuns
FROM (SELECT ship
FROM Outcomes
WHERE battle = 'Guadalcanal'
) AS a LEFT JOIN
Classes ON a.ship = class;

SELECT name, displacement, numGuns
FROM Classes, Ships
WHERE Classes.class = Ships.class AND
name IN (SELECT Ship
FROM Outcomes
WHERE battle = 'Guadalcanal'
)
UNION
SELECT class, displacement, numGuns
FROM Classes
WHERE class IN(SELECT ship
FROM Outcomes
WHERE battle = 'Guadalcanal'
);