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

Question

Here is a schema about battleships and the battles they fought in: Battles (ship, battleName, result) A typical Ships tuple would be: Ships (name, yearLaunched, country, numGuns, gunSize, displacement) 'New Jersey, 1943, 'USA', 9,16, 46000) which means that the battleship New Jersey was launched in 1943; it belonged to the USA, carried 9 guns of size ló-inch (bore, or inside diameter of the barrel), and weighted (displaced, in nautical terms) 46,000 tons. A typical tuple for Battles is: Hood',North Atlantic', 'sunk') 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: 1. 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. 2. 3. 4. List, for each country, the average number of guns carried by their battleships

Explanation / Answer

Battles (ship, battleName, result);

Ships (name, yearLaunched, country, numGuns, gunSize, displacement);

Q1) Which battleships launched before 1930 had 16 inch guns?

select name, country, numGuns from Ships where yearLaunched < 1930 and gunSize = 16;

Q2) Which battleships had the guns with the largest size?

select name, country, numGuns from Ships where gunSize in(select max(gunSize) from Ships);

Q3) Which battleships had the guns with the second largest gun size?

select name, gunSize from Ships where gunSize in (SELECT MAX(gunSize)

FROM Ships

WHERE gunSize< (select max(gunSize) from Ships));

Q4) List for each country the average number of guns carried by their ships.

select country, avg(numGuns) from Ships 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.