Exercise #57 (tips and solutions)

Below is the solution which calculates correctly the number of the sunk ships (though, in our opinion, is rather bulky), however the general number of the ships in a class is incorrectly counted up.

  1. SELECT f.class, SUM(count_out) AS cnt
  2. FROM (SELECT t.class, SUM(cnt) AS count_out
  3. FROM (SELECT c.class, ship, COUNT(*) CNT
  4. FROM Classes c LEFT JOIN
  5. Ships s ON c.class = s.class INNER JOIN
  6. Outcomes o ON o.ship = s.name AND
  7. result = 'sunk'
  8. GROUP BY c.class,ship
  9. ) AS t
  10. GROUP BY t.class
  12. SELECT t.class, SUM(cnt) AS count_out
  13. FROM (SELECT c.class, ship, COUNT(*) cnt
  14. FROM Classes c INNER JOIN
  15. Outcomes o ON c.class = o.ship AND
  16. o.result = 'sunk' AND
  18. FROM Ships
  19. WHERE o.ship = name
  20. )
  21. GROUP BY c.class,ship
  22. ) AS t
  23. GROUP BY t.class
  24. ) AS f
  25. GROUP BY f.class
  26. HAVING 2 < (SELECT SUM(cnt)
  27. FROM (SELECT COUNT(c.class) AS cnt
  28. FROM Classes c, Ships s
  29. WHERE c.class = s.class AND
  30. c.class = f.class
  31. UNION
  32. SELECT COUNT(c.class) AS cnt
  33. FROM Classes c, Outcomes o
  34. WHERE c.class = o.ship AND
  35. c.class = f.class AND
  37. FROM Ships
  38. WHERE o.ship = name
  39. )
  40. ) AS k
  41. );

Calculation of the total number of the ships in a class is carried out here in HAVING clause of the main query. In a subquery of this clause for each class in main query the unite of number of the ships in Ships table with number of the ships (head ships) of Outcomes table is carried out provided that latter ships have not been considered before (they are not present in Ships table).

It is obvious that as one-attribute relations are united by means of UNION operator we receive incorrect result in view of elimination of duplicates if one ship is available in each of the sets. However here it should not be a mistake, as we select the classes having more than two ships in sum. And other possible variants should not be here, as the head ship, if any, is only one (despite of excessive use of COUNT in the second query). And still the mistake is discovered here. The matter is that the head ship might take part in more than one battle, so we are considering she as many times as the number of her battles, certainly, if she is not present in Ships table.

It is simple to correct this solution, we suggest you to make it by yourself. However it is possible to write more simple (and more effective also) query.

To return to discussion of exercise #57

To solve a problem on SQL-EX.RU

Bookmark and Share
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 CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100