Use SQL Queries to answer the following questions The database schema is as foll

Question

Use SQL Queries to answer the following questions

The database schema is as follows:

Ships(name, class, launched)

Classes(class, type, country, numGuns, bore, displacement): 'bb' for battleship and 'bc' for battlecruiser.

Battles(name, date)

Outcomes(ship, battle, result)

All relations are defined as tables in the database.

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

2. Which battleships had the guns with the largest gun size?

3. 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.

Explanation / Answer

1.

select ships.name,classes.country,classes.numguns from ships join classes join battles

on battles.date<1942

and classes.bore=16 inch;

2.

select ships.name from ships join classes join battles

on classes.bore=max(classes.bore);

3.

select ships.name,classes.bore from ships join classes

on (select max(bore)from classes where bore<(select max(bore) from classes));

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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