06:36

Exercise #32 page 4

Solution 3.14.5

Console
Execute
1. SELECT Country, AVG(bore*bore*bore)/2
2. FROM (SELECT c.country, bore
3. FROM Classes C,
4. Ships S
5. WHERE S.class = C.Class AND
6. NOT bore IS NULL
7. UNION ALL
8. SELECT country, bore
9. FROM Classes C,
10. OutComes O
11. WHERE O.Ship = C.Class AND
12. NOT EXISTS (SELECT 1
13. FROM Ships S
14. WHERE s.Name = O.Ship
15. ) AND
16. NOT bore IS NULL
17. GROUP BY country, bore
18. ) AS Q1
19. GROUP BY country;

Two-attribute relations {country, caliber} are joined in the sub query. The second query predicate is the following:

1. NOT EXISTS (SELECT 1
2. FROM Ships S
3. WHERE s.Name = O.Ship
4. )
It eliminates possibility of multi-counting the ship if this ship is present in both Ships and Outcomes tables. This justifies the use of UNION ALL operator. Duplicates which can appear in case of ship participating in multiple battles are excluded by grouping by country and caliber.

However, consider a case when a country has several classes of ships with the same caliber and these ships are present in the Outcomes table. As the result, only one ship would be counted, instead of several times what makes the given solution a mistaken.

To solve the problem on SQL-EX.RU

 Pages 1 2 3 4