01:30

# Exercise #70 page 4

Solution 3.7.4

Console
Execute
`SELECT DISTINCT battle FROM (SELECT battle, country  FROM (SELECT battle, country  FROM Outcomes INNER JOIN  Classes ON ship = class  UNION  SELECT battle, country  FROM Outcomes o INNER JOIN  Ships s ON o.ship = s.name INNER JOIN  Classes c ON s.class = c.class ) x  GROUP BY battle, country  HAVING COUNT(*) > 2 ) y;`

In the internal sub-query the two queries are connected. The first of these:

Console
Execute
`SELECT battle, country  FROM Outcomes INNER JOIN  Classes ON ship = class;`
determines the country and battles which leading ships are take part. The second one:

Console
Execute
`SELECT battle, country  FROM Outcomes o INNER JOIN  Ships s ON o.ship = s.name INNER JOIN  Classes c ON s.class = c.class;`
determines the country and battles for ships in the Ships table. Joining with Classes table is necessary for recognition of the owner country.

Using of the UNION clause leads to the removal of the duplicates. From one side, it seemes rightly, because the leading ship might be present in the both - the Outcomes and the Ships table. But from the other side if we remove the duplicates the resultant set will have only unique pairs {battle, country}. This means that for any count of the ships from one country for one battle will be only the one row. As result the following grouping will be superfluous, like the HAVING clause.

The first idea is in the using of UNION ALL instead of UNION, which means we take into account duplicates too. But, as we know from previous consideration, for one ship which is present in the both tables, we get two rows.

What should we do? Author offers two methods. The first one: we keep UNION, but count not the countries but the ships. Then duplicates would be eliminated rightly. In the second method UNION ALL clause is using, but in this case we need to check that the ship is present in the one table would not be present in the other, and count it only once.

Which of two methods is more preferable not only depends on our predilection, but on the execution plan`s cost. We offer you to appraise it by your own, in process of solving this task by both of the methods.

T&S

To solve the problem on SQL-EX.RU

 Pages 1 2 3 4
Last added:
Tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.