I have to make query from DB. I found some solution but it isn't so precise as it need to be.
Here result of my query:
country weight Germany 1687.5 Gt.Britain 1687.5 Japan 1886.6666666666667 USA 1897.7777777777778
And correct query:
country weight Germany 1687.50 Gt.Britain 1687.50 Japan 1886.67 USA 1897.78
How to achieve this outcome?
Here is DB schema:
- 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.
And my query looks next:
SELECT DISTINCT Classes.country, ( SELECT AVG( pen.p ) FROM ( SELECT (c1.bore*c1.bore*c1.bore)/2 AS p FROM Classes AS c1, Ships AS s1 WHERE c1.class=s1.class AND c1.country = Classes.country AND c1.bore IS NOT NULL UNION ALL SELECT (c2.bore*c2.bore*c2.bore)/2 FROM Classes AS c2, Outcomes WHERE c2.country = Classes.country AND c2.class=Outcomes.ship AND c2.bore IS NOT NULL AND Outcomes.ship NOT IN ( SELECT ss.name FROM Ships AS ss ) ) AS pen WHERE pen.p IS NOT NULL ) AS weight FROM Classes WHERE Classes.country IS NOT NULL
- How to count result set?