Question

Find the countries who has the heaviest ships.

create table classes(
cclass varchar2(30) primary key,
type varchar2(2),
country varchar2(30),
numguns integer,
bore integer,
displacement integer
);

create table ships(
sname varchar2(20) primary key,
sclass varchar2(30),
launched integer
);

create table battles(
sname varchar2(30) primary key,
bdate date
);

create table outcomes(
ship varchar2(30),
battle varchar2(30),
result varchar2(10),
constraint outcomes_pk primary key (ship, battle)

);

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('North Carolina','bb','USA',9,16,37000);
insert into classes values('Renown','bc','Gt. Britain',6,15,32000);
insert into classes values('Revenge','bb','Germany',8,15,29000);
insert into classes values('Tennessee','bb','USA',12,14,32000);
insert into classes values('Yamato','bb','Japan',9,18,65000);

insert into ships values ('California','Tennessee',1921);
insert into ships values ('Haruna','Kongo',1915);
insert into ships values ('Hiei','Kongo',1914);
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 ('North Carolina','North Carolina',1941);
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 ('Rasolution','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','Tennessee',1921);
insert into ships values ('Tennessee','Tennessee',1920);
insert into ships values ('Washington','North Carolina',1941);
insert into ships values ('Wisconsin','Iowa',1944);
insert into ships values ('Yamato','Yamato',1941);

insert into battles values ('North Atlantic','10-MAY-1924');
insert into battles values ('Guadalcanal','15-NOV-1942');
insert into battles values ('North Cape','26-DEC-1943');
insert into battles values ('Surigao Strait','25-OCT-1944');

insert into outcomes values ('Bismarck','North Atlantic','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','North Atlantic','sunk');
insert into outcomes values ('King George V','North Atlantic','ok');
insert into outcomes values ('Kirishima','Guadalcanal','sunk');
insert into outcomes values ('Prince of Wales','Guadalcanal','damaged');
insert into outcomes values ('Prince of Wales','North Atlantic','damaged');
insert into outcomes values ('Rodney','North Atlantic','damaged');
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');

Answer 1

select top 1 country from classes order by numguns*bore group by country;

--- here i am assuming that heaviest ship is that that is having maximum numguns multiplied by bore. so i am grouping this value by country and then selecting the top most country. kindly mention in question properly about the weight of the ship...