loading..
Ðóññêèé    English
19:02
go

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.

Console
Execute
  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
  11.      UNION ALL
  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 
  17.                               NOT EXISTS (SELECT * 
  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 
  36.                         NOT EXISTS (SELECT * 
  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
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-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.