Exercise #56 page 1

For each class, define the number of ships of this class that were sunken in a battles. Result set: class, number of sunken ships.

Solution 3.12.1

  1. SELECT aa.class, SUM(aa.sunks) Sunks
  2. FROM (
  3. -- 1
  4. SELECT c.class, COUNT(a.ship) sunks
  5. FROM Outcomes a INNER JOIN
  6. Ships b ON a.ship = b.name INNER JOIN
  7. Classes c ON b.class = c.class
  8. WHERE a.result = 'sunk'
  9. GROUP BY c.class
  10. UNION
  11. -- 2
  12. SELECT c.class, COUNT(a.ship)
  13. FROM Outcomes a INNER JOIN
  14. Classes c ON a.ship = c.class
  15. WHERE a.result = 'sunk'
  16. GROUP BY c.class
  17. UNION
  18. -- 3
  19. SELECT c.class, 0
  20. FROM Classes c
  21. ) aa
  22. GROUP BY aa.class;

Three tables are united in the subquery in FROM clause:

1. A class and a number of the sunk ships which are in the table Ships.

2. A class and a number of the sunk head ships of this class. There is an "excess" already, namely: there is no necessity to use a grouping and accordingly the function COUNT, since a class may have only one head ship and a ship may be sunk only once.

3. Each class with the zero amount of sunk ships. It allows to take into account those classes which have no sunk ships and, hence, do not get in the previous two sets of the records.

Uniting with the use of UNION eliminates duplicates, that, in opinion of the author, allows correctly to process a situation when the sunk head ship is also in the table Ships. At last, the grouping on classes with summation is carried out. Thus last set will not give the contribution to the final result if in a class there are sunk ships that is correct.

However the mistake is covered that two-attribute tuples are united {a class, a number of the sunk ships}. Therefore if there are two sunk ships in some class (for example, Bismarck), and the head ship is absent from Ships table then two identical tuples will be united.

Bismarck 1
And after elimination of duplicates we receive one sunk ship instead of two.

But it is not all. Even we may count the head ship twice if it is in Ships table too. It is fair for a case if there are also other ships of this class sunk in battles. Let's take for an example Bismarck again, but now it is also in the Ships table. Let there is also one more sunk ship (not a head) of this class. Then the first set will give

Bismarck 2
and the second

Bismarck 1

As a result we shall receive

Bismarck 3
Though actually there are only two ships.

Bookmark and Share
Pages 1 2 3 4 5
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
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.