Write SQL queries for the following requirements

Classes(class, type, country, numGuns, bore, displacement)

Ships(name, class, launched)

Battles(name, date_fought)

Outcomes(ship, battle, result)

Ships are built-in "classes" from the same design, and the class is usually named for the first ship of that class.

Relation Classes records the name of the class, the type (bb for battleship or bc for battlecruiser), the country that built the ship, the number of main guns, the bore (diameter of the gun barrel, in inches) of the main guns, and the displacement (weight, in tons).

Relation Ships records the name of the ship, the name of its class, and the year in which the ship was launched.

Relation Battles gives the name and date of battles involving these ships.

Relation Outcomes gives the result (sunk, damaged, or ok) for each ship in each battle.

Assuming the following data is properly inserted

For Classes: ('Bismarck','bb','Germany',8,15,42000); ('Kongo','bc','Japan',8,14,32000); ('North Carolina','bb','USA',9,16,37000); ('Renown','bc','Gt. Britain',6,15,32000); ('Revenge','bb','Gt. Britain',8,15,29000); ('Tennessee','bb','USA',12,14,32000); ('Yamato','bb','Japan',9,18,65000);

For Ships ('California','Tennessee',1921); ('Haruna','Kongo',1915); ('Hiei','Kongo',1914); ('Iowa','Iowa',1943); ('Kirishima','Kongo',1914); ('Kongo','Kongo',1913); ('Missouri','Iowa',1944); ('Musashi','Yamato',1942); ('New Jersey','Iowa',1943); ('North Carolina','North Carolina',1941); ('Ramilles','Revenge',1917); ('Renown','Renown',1916); ('Repulse','Renown',1916); ('Resolution','Revenge',1916); ('Revenge','Revenge',1916); ('Royal Oak','Revenge',1916); ('Royal Sovereign','Revenge',1916); ('Tennessee','Tennessee',1920); ('Washington','North Carolina',1941); ('Wisconsin','Iowa',1944); ('Yamato','Yamato',1941);

For Battles ('North Atlantic','27-May-1941'); ('Guadalcanal','15-Nov-1942'); ('North Cape','26-Dec-1943'); ('Surigao Strait','25-Oct-1944');

For Outcomes ('Bismarck','North Atlantic', 'sunk'); ('California','Surigao Strait', 'ok'); ('Duke of York','North Cape', 'ok'); ('Fuso','Surigao Strait', 'sunk'); ('Hood','North Atlantic', 'sunk'); ('King George V','North Atlantic', 'ok'); ('Kirishima','Guadalcanal', 'sunk'); ('Prince of Wales','North Atlantic', 'damaged'); ('Rodney','North Atlantic', 'ok'); ('Scharnhorst','North Cape', 'sunk'); ('South Dakota','Guadalcanal', 'ok'); ('West Virginia','Surigao Strait', 'ok'); ('Yamashiro','Surigao Strait', 'sunk');

-- Add the following constraints using views with check option.

a) No ship can be in battle before it is launched.

-- Write your sql statement here.

-- Now we can try some insertion on this view. INSERT INTO OutcomesView (ship, battle, result) VALUES('Musashi', 'North Atlantic','ok'); -- This insertion, as expected, should fail since Musashi is launched in 1942, -- while the North Atlantic battle took place on 27-MAY-41.

b) No ship can be launched before -- the ship that bears the name of the first ships class.

-- Write your sql statement here.

-- Now we can try some insertion on this view. INSERT INTO ShipsV(name, class, launched) VALUES ('AAA','Kongo',1912);