Exercise: 32

One of the characteristics of a ship is one-half the cube of the calibre of its main guns (mw).Determine the average ship mw with an accuracy of two decimal places for each country having ships in the database.

Solution

SELECT country
	, convert(NUMERIC(6, 2), AVG(power(bore, 3)) * 0.5)
FROM (
	SELECT country, bore, name
	FROM Classes, Ships
	WHERE Classes.class = Ships.class
	UNION
	SELECT country, bore, ship
	FROM Classes, Outcomes
	WHERE Classes.class = Outcomes.ship
) new_ships
GROUP BY country

References