Exercise 6.3.2: Write the following queries, based on the database schema Classe

Question

Exercise 6.3.2: Write the following queries, based on the database schema Classes (class, type, country, numGuns, bore, displacement) Ships(name, class, launched Battles (name, date) Outcomes (ship, battle, result) of Exercise 2.4.3. You should use at least one subquery in each of your answers and write each query in two significantly different ways (e.g., using different sets of the operators EXISTS, IN, ALL, and ANY) a) Find the countries whose ships had the largest number of guns ! b) Find the classes of ships, at least one of which was sunk in a battle c) Find the names of the ships with a 16-inch bore. d) Find the battles in which sips of the Kongo class participated.

Explanation / Answer

SELECT C.country

FROM Classes C

WHERE numGuns IN

(SELECT MAX(numGuns)

FROM Classes

);

SELECT DISTINCT C.class

FROM Classes C, Ships S

WHERE C.class = S.class

AND EXISTS

(SELECT ship

FROM Outcomes O

WHERE O.result='sunk'

AND O.ship = S.name ) ;

SELECT Ships.name FROM Ships

INNER JOIN Classes ON Ships.class = Classes.class

WHERE Classes.bore = 16;

SELECT O.battleFROM Outcomes OWHERE O.ship IN(SELECT name FROM Ships SWHERE S.Class ='Kongo');

**Comment for any further queries.

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

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