Here is a schema about battleships and the battles they fought in: A typical Shi

Question

Here is a schema about battleships and the battles they fought in:

A typical Ships tuple would be:

which means that the battleship New Jersey was launched in 1943; it belonged to the USA, carried 9 guns of size 16-inch (bore, or inside diameter of the barrel), and weighted (displaced, in nautical terms) 46,000 tons. A typical tuple for Battles is:

That is, H.M.S. Hood was sunk in the battle of the North Atlantic. The other possible results are 'ok' and 'damaged'. Write the following queries:

Which battleships launched before 1930 had 16-inch guns? List their names, their country, and the number of guns they carried.

Which battleship(s) had the guns with the largest gun size?

Which battleships had the guns with the second largest gun size. More precisely, find the ships whose gun size was exceeded by only one gun size, no matter how many other ships had that larger gun size. List the names of the ships and their gun size.

List, for each country, the average number of guns carried by their battleships.

List all the pairs of countries that fought each other in battles. List each pair only once, and list them with the country that comes first in alphabetical order first.

For the battle of Surigao Strait, for each country engaged in that battle (had one or more battleships participating), give the number of its battleships that were sunk. Note: this question is very tricky. In particular, you need to deal with the (historical) case that a country engaged in the battle but did not have any ships sunk.

Explanation / Answer

Q1

SELECT name, country, numGuns
FROM Ships
WHERE yearLaunched < 1930 AND gunSize = 16;

-------------------------------------------------------------------------------

Q2.

SELECT *
FROM Ships
WHERE gunSize = (SELECT MAX(gunSize) FROM Ships);

-------------------------------------------------------------------------------

Q3
SELECT name, gunSize
FROM Ships
WHERE gunSize = (SELECT MAX(gunSize) FROM Ships WHERE gunSize < (SELECT MAX(gunSize) FROM Ships)

Note : The inner query selects the second max gun size.
-------------------------------------------------------------------------------

Q4

SELECT country, AVG(numGuns)
FROM Ships
GROUP BY country;

-------------------------------------------------------------------------------

Q5.

INSUFFICIENT INFORMATION. There is no way to know whether a pair of country fought each other in a battle. Let us assume that "The country that has lost all the battle ships in a battle (LOST), has fought against the country that has some ships which are still 'ok' (WON) ". This would still be wrong because, multiples countries are fighting in a battle. Some country might be having all of it's ships sunk, but it's allies may have won the war against it's opponent.

-------------------------------------------------------------------------------
Q6.

SELECT country, COUNT(*) AS sunk
FROM Ships JOIN Battles ON Ships.name = Battles.ship
WHERE battleName = 'Surigao Strait' AND result = 'sunk'
GROUP BY country;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.