This exercise concerns World War II capital ships. Ships are built in "classes" from the same design, and the class is usually named for the first ship of that class. The CLASSES table records the name of the class, the type $($bb for battleship or bc for battlecruiser, etc.$),$ 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$).$ The SHIPS table records the name of the ship, the name of its class, and the year in which the ship was launched. The BATTLES table gives the name and date of battles involving these ships, and the OUTCOMES table gives the result $($either sunk, damaged, or ok$)$ for each ship in a battle. $($Note: some of the outcomes data has been fabricated for the purposes of this assignment.$)$

Use the attached SQL script to create the tables in SQLite and populate them with sample data. Then write SQL queries to answer the following ten questions. Your queries should work for arbitrary data, not only the data currently found in the tables.

$1$: List the name, displacement, and number of guns of the ships engaged in the battle of Guadalcanal.

$2$: Find the classes of ships that have exactly two ships as members of that class.

$3$: Find the names of the countries that had both battleships and battlecruisers.

$4$: Find those ships that were damaged in one battle, but later fought in another.

$5$: Find those battles that featured at least four ships from a single country.

$6$: List each country and the ship name$($s$)$ with the greatest number of guns for that country.

$7$: Find the battles in which Kongo$-$class ships participated.

$8$: For each country, find the average number of guns found on battleship classes.

$9$: For each class, find the year in which the first ship of that class launched.

$10$: For each class, count the number of ships of that class that were sunk in battle.

create table classes $($

class varchar$(20),$

type char $(2),$

country varchar $(20),$

numGuns int,

bore int,

displacement int,

primary key $($class$)$

;

create table ships $($

name varchar$(20),$

class varchar$(20),$

launched int,

primary key $($name$),$

foreign key $($class$)$ references classes$($class$)$

;

create table battles $($

name varchar$(20),$

date date,

primary key $($name$)$

;

create table outcomes $($

ship varchar$(20),$

battle varchar$(20),$

result varchar$(10),$

primary key $($ship$,$ battle$),$

foreign key $($ship$)$ references ships$($name$),$

foreign key $($battle$)$ references battles$($name$)$

;

insert into classes values $(\text{'}$Bismarck$\text{'},\text{'}$bb$\text{'},$ 'Germany', $8,15,42000)$;

insert into classes values $(\text{'}$Iowa$\text{'},\text{'}$bb$\text{'},$ 'USA', $9,16,46000)$;

insert into classes values $(\text{'}$Kongo$\text{'},\text{'}$bc$\text{'},$ 'Japan', $8,14,32000)$;

insert into classes values $(\text{'}$North Carolina', $\text{'}$bb$\text{'},$ 'USA', $9,16,37000)$;

insert into classes values $(\text{'}$Renown$\text{'},\text{'}$bc$\text{'},\text{'}$Gt$.$ Britain', $6,15,32000)$;

insert into classes values $(\text{'}$Revenge$\text{'},\text{'}$bb$\text{'},\text{'}$Gt$.$ Britain', $8,15,29000)$;

insert into classes values $(\text{'}$Tennessee$\text{'},\text{'}$bb$\text{'},$ 'USA', $12,14,32000)$;

insert into classes values $(\text{'}$Yamato$\text{'},\text{'}$bb$\text{'},$ 'Japan', $9,18,65000)$;

insert into ships values $(\text{'}$California$\text{'},$ 'Tennessee', $1921)$;

insert into ships values $(\text{'}$Haruna$\text{'},$ 'Kongo', $1915)$;

insert into ships values $(\text{'}$Hiei$\text{'},$ 'Kongo', $1914)$;

insert into ships values $(\text{'}$Iowa$\text{'},$ 'Iowa', $1943)$;

insert into ships values $(\text{'}$Kirishima$\text{'},$ 'Kongo', $1915)$;

insert into ships values $(\text{'}$Kongo$\text{'},$ 'Kongo', $1913)$;

insert into ships values $(\text{'}$Missouri$\text{'},$ 'Iowa', $1944)$;

insert into ships values $(\text{'}$Mushashi$\text{'},$ 'Yamato', $1942)$;

insert into ships values $(\text{'}$New Jersey', 'Iowa', $1943)$;

insert into ships values $(\text{'}$North Carolina', 'North Carolina', $1941)$;

insert into ships values $(\text{'}$Ramillies$\text{'},$ 'Revenge', $1917)$;

insert into ships values $(\text{'}$Renown$\text{'},$ 'Renown', $1916)$;

insert into ships values $(\text{'}$Repulse$\text{'},$ 'Renown', $1916)$;

insert into ships values $(\text{'}$Resolution$\text{'},$ 'Revenge', $1916)$;

insert into ships values $(\text{'}$Revenge$\text{'},$ 'Revenge', $1916)$;

insert into ships values $(\text{'}$Royal Oak', 'Revenge', $1916)$;

insert into ships values $(\text{'}$Royal Sovereign', 'Revenge', $1916)$;

insert into ships values $(\text{'}$Tennessee$\text{'},$ 'Tennessee', $1920)$;

insert into ships values $(\text{'}$Washington$\text{'},$ 'North Carolina', $1941)$;

insert into ships values $(\text{'}$Wisconsin$\text{'},$ 'Iowa', $1944)$;

insert into ships values $(\text{'}$Yamato$\text{'},$ 'Yamato', $1941)$;

insert into battles values $(\text{'}$North Atlantic', $\text{'}1941-05-24\text{'})$;

insert into battles values $(\text{'}$Guadalcanal$\text{'},\text{'}1942-11-15\text{'})$;

insert into battles values $(\text{'}$North Cape', $\text{'}1943-12-26\text{'})$;

insert into battles values $(\text{'}$Surigao Straight', $\text{'}1944-10-25\text{'})$;

insert into outcomes values $(\text{'}$California$\text{'},$ 'Surigao Straight', $\text{'}$ok$\text{'})$;

insert into outcomes values $(\text{'}$Haruna$\text{'},$ 'Surigao Straight', 'sunk'$)$;

insert into outcomes values $(\text{'}$Iowa$\text{'},$ 'Guadalcanal', $\text{'}$ok$\text{'})$;

insert into outcomes values $(\text{'}$Iowa$\text{'},$ 'Surigao Straight', $\text{'}$ok$\text{'})$;

insert into outcomes values $(\text{'}$Kirishima$\text{'},$ 'Guadalcanal', 'sunk'$)$;

insert into outcomes values $(\text{'}$Mushashi$\text{'},$ 'Surigao Straight', 'sunk'$)$;

insert into outcomes values $(\text{'}$New Jersey', 'Guadalcanal', 'damaged'$)$;

insert into outcomes values $(\text{'}$New Jersey', 'Surigao Straight', $\text{'}$ok$\text{'})$;

insert into outcomes valu