Exercise #151 (tips and solutions)

It’s an awful trouble with this headships! Below we see how wrong solution has gotten for the task about the head ships, which were launched before 1941.

SELECT class
FROM Classes
WHERE EXISTS (SELECT 1
              FROM Ships
              WHERE launched < 1941 
                   AND Ships.class = Classes.class
             );

I.e. class here is identified with presence of the headship in the database, namely, the class, which include a ship launched before 1941, is being found. As it is said in the database definition, headship is the ship with name matching with the name of a class. But that not always exist a ship the name of which coincides with a class name. Therefore to search for the head ships that are absent from Ships table follows exclusively in Outcomes table.

At last, about variant unconsidered in the solution 5.3.3. So, the next situation is possible. There is a headship with unknown launch year. Moreover, it could take part in the only one battle, for example in 1945 (i.e. after 1941). Let all the ships of the same class have unknown launch year (NULLs are allowed). But if at least one of these ships took part in battle before 1941, we should include both of the ships into the result set, because the headship (if it exists!) is launched before any other ship in its class.

Here is the solution which, apparently, considers all the stipulated moments:

-- Ships launched before 1941
SELECT name
FROM Ships
WHERE launched < 1941
UNION
-- Ships participated in battles before 1941
SELECT ship
FROM Outcomes 
    JOIN Battles ON Battles.name = Outcomes.battle
WHERE date < '19410101'
UNION
-- Headships in Outcomes, class of which includes other ships
-- launched before 1941
SELECT ship
FROM Outcomes
WHERE ship IN (SELECT class
                FROM Ships
                WHERE launched < 1941
                )
UNION
-- Headships in Outcomes when at least one ship from
-- the same class participated in a battle before 1941
SELECT ship
FROM Outcomes
WHERE Ship IN (SELECT class
                FROM Ships JOIN
                Outcomes ON Ships.name = Outcomes.ship 
                JOIN Battles ON Battles.name = Outcomes.battle
                WHERE date < '19410101'
               );

However, the system all the same informs on a mistake …

As it has already been noted, the headships with unknown launch year can be not only in Outcomes table, but also in Ships table. Moreover, such ships will not be considered by above query if they are absent from Outcomes table, i.e. either they did not participate in battles or the information on their participation is unknown.

Thus, it is a pure logic, nothing more.

To return to discussion of exercise #151

To solve a problem on SQL-EX.RU