Task 19(ships)

–Define the names of all the ships in the database which were definitely launched before 1941.

–1. There can be a class which does not contain the head ship therefore
— it is fallacious to use the class name instead of the ship name.
–2. The head ship should be identified even if the year of launching of all the ships of this class is not known,
–BUT at least one of them took part in the sea battle before the year 41.
–3. The head ship the year of launching of which is not known, may be included only into Ships table.
–4. Note that the column [date] has datetime data type.

select name from ships

where launched < 1941

union

select ship from Outcomes where
battle in

      (select name from Battles

            where date < ‘1941-01-01 00:00:00.000’)

union

select s.class from ships s

inner join

(select ship from Outcomes where
battle in

      (select name from Battles

            where date < ‘1941-01-01 00:00:00.000’)

      union

            select name from ships

            where launched < 1941       

) x on x.ship = s.name

inner join Ships s1 on s1.name = s.class

union

select s.class from ships s

inner join

(select ship from Outcomes where
battle in

      (select name from Battles

            where date < ‘1941-01-01 00:00:00.000’)

      union

            select name from ships

            where launched < 1941       

) x on x.ship = s.name

inner join Outcomes o on o.ship = s.name