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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s