sql question from sql-ex.ru site

[krokodajl]

Hi Everyone.

I have problems with solving a sql question. There is an assumption:

The database of naval ships that took part in World War II is under consideration. The database has the following relations:

Classes(class, type, country, numGuns, bore, displacement)
Ships(name, class, launched)
Battles(name, date)
Outcomes(ship, battle, result)

Ships in classes are arranged to a single project. A class is normally assigned the name of the first ship in the class under consideration (head ship); otherwise, the class name does not coincide with any ship name in the database.
The Classes relation includes the class name, type (bb for a battle ship, or bc for a battle cruiser), country where the ship was built, number of main guns, gun caliber (diameter of the gun barrel, in inches), and displacement (weight in tons). The Ships relation includes the ship name, its class name, and launch year. The Battles relation covers the name and date of a battle the ships participated; while the result of their participation in the battle (sunk, damaged, or unharmed - OK) is in the Outcomes relation. Note: the Outcomes relation may include the ships not included in the Ships relation.

And there is a question:

The weight of a gun missile (in pounds) is almost equal to one-half cube of its caliber (in inches). Define the average weight of the missile for the ships of each country (taking into account Outcomes table).

I have written the following query which does not work correctly:

select country,avg(0.5*(bore*bore*bore)) from classes right join (select class from ships union all select ship from outcomes join classes on outcomes.ship = classes.class) T on classes.class = T.class group by country

the number of question is 32(3).

Any suggestions?
Best regards

[algar68]

Hi Everyone.

I have problems with solving a sql question. There is an assumption:

The database of naval ships that took part in World War II is under consideration. The database has the following relations:

Classes(class, type, country, numGuns, bore, displacement)
Ships(name, class, launched)
Battles(name, date)
Outcomes(ship, battle, result)

Ships in classes are arranged to a single project. A class is normally assigned the name of the first ship in the class under consideration (head ship); otherwise, the class name does not coincide with any ship name in the database.
The Classes relation includes the class name, type (bb for a battle ship, or bc for a battle cruiser), country where the ship was built, number of main guns, gun caliber (diameter of the gun barrel, in inches), and displacement (weight in tons). The Ships relation includes the ship name, its class name, and launch year. The Battles relation covers the name and date of a battle the ships participated; while the result of their participation in the battle (sunk, damaged, or unharmed - OK) is in the Outcomes relation. Note: the Outcomes relation may include the ships not included in the Ships relation.

And there is a question:

The weight of a gun missile (in pounds) is almost equal to one-half cube of its caliber (in inches). Define the average weight of the missile for the ships of each country (taking into account Outcomes table).

I have written the following query which does not work correctly:

select country,avg(0.5*(bore*bore*bore)) from classes right join (select class from ships union all select ship from outcomes join classes on outcomes.ship = classes.class) T on classes.class = T.class group by country

the number of question is 32(3).

Any suggestions?
Best regards

SELECT country,avg(0.5*(bore*bore*bore)) as 'weight'
FROM classes 
left outer JOIN
(SELECT class FROM ships LEFT JOIN Outcomes ON ships.class =  Outcomes.ship) T 
ON classes.class = T.class GROUP BY country

You can try this, thanks..

[skivranger]

SELECT A.COUNTRY,AVG((0.5*(BORE*BORE*BORE)) )AS SUMMA 
       FROM (
            SELECT  DBO.CLASSES.COUNTRY  ,dbo.CLASSES.BORE
            FROM DBO.CLASSES 
            INNER JOIN DBO.ships ON  DBO.CLASSES.CLASS=DBO.ships.CLASS 	
		    UNION ALL
		    SELECT DBO.CLASSES.COUNTRY ,dbo.CLASSES.BORE--,DBO.OUTCOMES.ship
		    FROM   DBO.OUTCOMES
		    LEFT JOIN DBO.ships ON DBO.OUTCOMES.ship=DBO.ships.name
		    INNER JOIN  DBO.CLASSES ON DBO.CLASSES.class= DBO.OUTCOMES.ship
			       
		WHERE DBO.ships.CLASS IS NULL AND NOT DBO.CLASSES.COUNTRY IS NULL 
		group by DBO.CLASSES.COUNTRY ,dbo.CLASSES.BORE,DBO.OUTCOMES.ship
		   )A
       GROUP BY A.COUNTRY

[Ville Valo]

select country as Country, AVG(bore*bore*bore/2) as weight from classes
left join ships on ships.class=classes.class
left join outcomes on outcomes.ship=classes.class
GROUP BY country

[MehranNZ]

SELECT A.COUNTRY,AVG((0.5*(BORE*BORE*BORE)) )AS SUMMA 
       FROM (
            SELECT  DBO.CLASSES.COUNTRY  ,dbo.CLASSES.BORE
            FROM DBO.CLASSES 
            INNER JOIN DBO.ships ON  DBO.CLASSES.CLASS=DBO.ships.CLASS 	
		    UNION ALL
		    SELECT DBO.CLASSES.COUNTRY ,dbo.CLASSES.BORE--,DBO.OUTCOMES.ship
		    FROM   DBO.OUTCOMES
		    LEFT JOIN DBO.ships ON DBO.OUTCOMES.ship=DBO.ships.name
		    INNER JOIN  DBO.CLASSES ON DBO.CLASSES.class= DBO.OUTCOMES.ship
			       
		WHERE DBO.ships.CLASS IS NULL AND NOT DBO.CLASSES.COUNTRY IS NULL 
		group by DBO.CLASSES.COUNTRY ,dbo.CLASSES.BORE,DBO.OUTCOMES.ship
		   )A
       GROUP BY A.COUNTRY

thanks man this code works what a tricky q it was
i was stuck by it for a long time