SQl questions

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

Ships(name, class, launched) --launched is for year 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.

a) Create simple SQL statements to create the above relations (no constraints for initial creations). b) Insert the following data.

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');