Exercise #56 page 5

Solution 3.12.5
  1. SELECT t1.class, COUNT(*) AS cnt
  2. FROM (SELECT a.class, b.name
  3. FROM Classes a LEFT JOIN
  4. -- join with Ships without head ships:
  5. Ships b ON a.class = b.class AND
  6. a.class <> b.name
  7. ) AS t1 JOIN
  8. -- join either by class for head ships or by name:
  9. Outcomes t2 ON t1.class = t2.ship OR
  10. t1.name = t2.ship
  11. WHERE result = 'sunk'
  12. GROUP BY t1.class
  13. -- choose ship classes that absent from the first query.
  14. -- these are classes having no sunken ships.
  15. UNION
  16. SELECT class, '0'
  17. FROM Classes
  18. WHERE class NOT IN (SELECT DISTINCT t1.class
  19. FROM (SELECT a.class, b.name
  20. FROM Classes a LEFT JOIN
  21. Ships b ON a.class = b.class AND
  22. a.class <> b.name
  23. ) AS t1 JOIN
  24. Outcomes t2 ON t1.class = t2.ship OR
  25. t1.name = t2.ship
  26. WHERE result = 'sunk'
  27. );

Solution 3.12.6
  1. SELECT d.class class, (SELECT COUNT(f.result)
  2. FROM (SELECT c.result
  4. Outcomes c ON (b.name = c.ship)
  5. WHERE c.result = 'sunk' AND
  6. d.class = b.class
  8. SELECT c.result
  9. FROM Outcomes c
  10. WHERE c.result = 'sunk' AND
  11. d.class = c.ship
  12. ) f
  13. ) Sunks
  14. FROM Classes d

For the analysis of two last solution - 3.12.5 and 3.12.6 - we shall consider following variants of data. In Ships table (for the purpose of analysis, meaningful columns are only shown):

name class
ship1_class_1 class_1
ship2_class_1 class_1

In Outcomes table:

ship result
ship1_class_1 Sunk
class_1 Sunk

Then according to a join predicate in the solution 3.12.5

  1. ON t1.class = t2.ship OR
  2. t1.name = t2.ship
ship1_class_1 ship from Ships table will fall twice into result set as its name coincides with the name of the ship from the first row in Outcomes table and its class coincides with name of the ship from the second row. As a result we shall receive 3 sunken ships, though actually them only 2.

The task solution 3.12.6 will give us correct result in this case, as the first query in union (join by ship name) will give ship1_class_1 only, whereas the second gives class_1 only. However this solution is not correct also as will be shown on other variant of data.

In Ships table

name class
ship1_class_2 class_2
class_2 class_2

In Outcomes:

ship result
ship1_class_2 sunk
class_2 sunk

The first query in union will give us the both of sunken ships in the class_2 class, and the second one gives the head ship of this class. As here UNION ALL is used, the head ship will be twice presented in result set; therefore we again receive 3 ships instead of 2. Cosmetic correction that changes UNION ALL by UNION gives not correct solution, because we get the same mistake as in the solution 3.12.4 when for any quantity of the sunken ships of a class only 1 ship is presented in the result set .

By the way, the solution 3.12.5 gives value of 3 on these data also, but for other reason described above.


To solve the problem on SQL-EX.RU

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
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.