loading..
Русский    English
05:29

Exercise #70 page 4

Solution 3.7.4

Console
Execute
  1. SELECT DISTINCT battle
  2. FROM (SELECT battle, country
  3. FROM (SELECT battle, country
  4. FROM Outcomes INNER JOIN
  5. Classes ON ship = class
  6. UNION
  7. SELECT battle, country
  8. FROM Outcomes o INNER JOIN
  9. Ships s ON o.ship = s.name INNER JOIN
  10. Classes c ON s.class = c.class
  11. ) x
  12. GROUP BY battle, country
  13. HAVING COUNT(*) > 2
  14. ) y;

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

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

Console
Execute
  1. SELECT battle, country
  2. FROM Outcomes o INNER JOIN
  3. Ships s ON o.ship = s.name INNER JOIN
  4. 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
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
yesterday
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.