loading..
Ðóññêèé    English
20:17
go

Exercise #37 (tips and solutions)

Consider the following solution that is free from mistakes analyzed in 3.2:

Console
Execute
  1. SELECT t1.class
  2. FROM (SELECT a.class AS class, COUNT(b.name) AS coun
  3.       FROM Classes a LEFT JOIN 
  4.            Ships b ON b.class = a.class
  5.       GROUP BY a.class
  6.       UNION ALL
  7.       SELECT a1.class AS class, COUNT(ship) AS coun
  8.       FROM Classes a1 LEFT JOIN 
  9.            Outcomes d ON d.ship = a1.class
  10.       WHERE d.ship NOT IN (SELECT b.name 
  11.                            FROM Ships b
  12.                            )
  13.       GROUP BY a1.class
  14.       ) t1
  15. GROUP BY t1.class
  16. HAVING SUM(t1.coun) = 1;

Indeed, a subquery consists of the two queries, the first of which counts ships for each class from the Ships table and the second one counts only those leading ships that are absent in the Ships table. After that the main query sums the numbers for each class and filters classes with many ships.

Please, pay attention that it is necessary to use the UNION ALL clause here.  Otherwise duplicate pairs (class, ships number) would be eliminated and a class containing one non-leading ship in the Ships table and a leading one in the Outcomes table would be produced. It is the usual mistake that we observed in 3.2. 

What would remain to correct still, if even that solution is rejected by the system? The cause of the rejection is that a leading ship of a class is able to fight several battles and then, the last of the queries in the union counts the same leading ship as many times as many battles it has fought.  

To return to discussion of exercise #37

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.