Create SQL queries to answer the following questions: The database schema is as

Question

Create 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)

Question 1.

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

Question 2.

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.

Explanation / Answer

Question 1:

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

Solution:

SELECT country, AVG(numGuns) AS avgGuns FROM SHIPS GROUP BY country

Question 2:

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.

Solution:

SELECT DISTINCT s1.country, s2.country FROM Ships s1, Ships s2, Battles b1,

Battles b2 WHERE s1.name = b1.ship AND s2.name = b2.ship AND b1.battleName =
b2.battleName AND s1.country < s2.country

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects