22:02

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Common mistakes in solving SELECT statement exercises

Database «Ships»

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

mistakes UNION HAVING GROUP BY exercise 70 head ships

Previous [Database «Ships»]

[Exercise #37] Next

Last added:

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
month ago

SQL Problems and solutions

S. I. Moiseenko

Common mistakes in solving SELECT statement exercises

Database «Ships»

Exercise #70 page 4