-- (1) Tabellen declareren.
-- We droppen de tabellen eerst om zeker te zijn dat de aangemaakte
-- tabellen geen conflicten zullen veroorzaken.

DROP TABLE IF EXISTS Classes;
DROP TABLE IF EXISTS Battles;
DROP TABLE IF EXISTS Ships;
DROP TABLE IF EXISTS Outcomes;

CREATE TABLE Classes (
	class VARCHAR(20),
	type VARCHAR(2),
	country VARCHAR(20),
	numGuns INTEGER,
	bore INTEGER,
	displacement INTEGER
);

CREATE TABLE Battles (
	name VARCHAR(20),
	date DATE
);

CREATE TABLE Ships (
	name VARCHAR(20),
	class VARCHAR(20),
	launched INTEGER
);

CREATE TABLE Outcomes (
	ship VARCHAR(20),
	battle VARCHAR(20),
	result VARCHAR(10)
);

-- (2) Voorbeeld-data plaatsen in de tabellen.

insert into Classes values ( 'Bismarck' , 'bb' , 'Germany' , 8 , 15 , 42000 );
insert into Classes values ( 'Iowa', 'bb' , 'USA' , 9 , 16 , 46000  );
insert into Classes values ( 'Kongo' , 'bc' , 'Japan' , 8 , 14 , 32000 );
insert into Classes values ( 'South Dakota' , 'bb' , 'USA' , 9 , 16 , 37000 );
insert into Classes values ( 'Renown' , 'bc' , 'Gt. Britain' , 6 , 15 , 32000 );
insert into Classes values ( 'Revenge' , 'bb' , 'Gt. Britain' , 8 , 15 , 29000 );
insert into Classes values ( 'Mississippi' , 'bb' , 'USA' , 12 , 14 , 33000 );
insert into Classes values ( 'Yamato' , 'bb' , 'Japan' , 9 , 18 , 65000 );


insert into Battles values ( 'Denmark Strait' , '1924-05-01' );
insert into Battles values ( 'Guadalcanal' , '1942-11-15' );
insert into Battles values ( 'North Cape' , '1943-12-26' );
insert into Battles values ( 'Surigao Strait' , '1944-10-25' );


insert into Ships values ( 'Alabama' , 'South Dakota' , 1942 );
insert into Ships values ( 'Haruna' , 'Kongo' , 1915 );
insert into Ships values ( 'Hiei' , 'Kongo' , 1914 );
insert into Ships values ( 'Idaho' , 'Mississippi' , 1919 );
insert into Ships values ( 'Iowa' , 'Iowa' , 1943 );
insert into Ships values ( 'Kirishima' , 'Kongo' , 1915 );
insert into Ships values ( 'Kongo' , 'Kongo' , 1913 );
insert into Ships values ( 'Missouri' , 'Iowa' , 1944 );
insert into Ships values ( 'Musashi' , 'Yamato' , 1942 );
insert into Ships values ( 'New Jersey' , 'Iowa' , 1943 );
insert into Ships values ( 'New Mexico' , 'Mississippi' , 1918 );
insert into Ships values ( 'Ramillies' , 'Revenge' , 1917 );
insert into Ships values ( 'Renown' , 'Renown' , 1916 );
insert into Ships values ( 'Repulse' , 'Renown' , 1916 );
insert into Ships values ( 'Resolution' , 'Revenge' , 1916 );
insert into Ships values ( 'Revenge' , 'Revenge' , 1916 );
insert into Ships values ( 'Royal Oak' , 'Revenge' , 1916 );
insert into Ships values ( 'Royal Sovereign' , 'Revenge' , 1916 );
insert into Ships values ( 'South Dakota' , 'South Dakota' , 1942 );
insert into Ships values ( 'Wisconsin' , 'Iowa' , 1944 );
insert into Ships values ( 'Yamato' , 'Yamato' , 1941 );


insert into Outcomes values ( 'Arizona' , 'Pearl Harbor' , 'sunk' );
insert into Outcomes values ( 'Bismarck' , 'Denmark Strait' , 'sunk' );
insert into Outcomes values ( 'California' , 'Surigao Strait' , 'ok' );
insert into Outcomes values ( 'Duke of York' , 'North Cape' , 'ok' );
insert into Outcomes values ( 'Fuso' , 'Surigao Strait' , 'sunk' );
insert into Outcomes values ( 'Hood' , 'Denmark Strait' , 'sunk' );
insert into Outcomes values ( 'King George V' , 'Denmark Strait' , 'ok' );
insert into Outcomes values ( 'Kirishima' , 'Guadalcanal' , 'sunk' );
insert into Outcomes values ( 'Prince of Wales' , 'Denmark Strait' , 'damaged' );
insert into Outcomes values ( 'Rodney' , 'Denmark Strait' , 'ok' );
insert into Outcomes values ( 'Scharnhorst' , 'North Cape' , 'sunk' );
insert into Outcomes values ( 'South Dakota' , 'Guadalcanal' , 'damaged' );
insert into Outcomes values ( 'Tennessee' , 'Surigao Strait' , 'ok' );
insert into Outcomes values ( 'Washington' , 'Guadalcanal' , 'ok' );
insert into Outcomes values ( 'West Virginia' , 'Surigao Strait', 'ok' );
insert into Outcomes values ( 'Yamashiro' , 'Surigao Strait' , 'sunk' );