–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