Exercise #56 page 4

Solution 3.12.4

  1. SELECT class, SUM(sunks) sunks
  2. FROM (SELECT cl.class, 1 sunks
  3. FROM Classes cl LEFT JOIN
  4. Ships sh ON cl.class = sh.class INNER JOIN
  5. Outcomes ou ON ou.ship = sh.name OR
  6. ou.ship = cl.class
  7. WHERE result='sunk'
  8. UNION
  9. SELECT DISTINCT class, 0 sunks
  10. FROM classes
  11. ) tab
  12. GROUP BY class;

The first of united queries in the FROM clause gives the following row in the result set for each sunken ship:

class 1

In the main query, these units are being summed. But as UNION eliminates duplicates, finally we obtain either 1 (from this first query) or 0 (from second query which considers the classes which are not having the sunken ships) for any quantity of the sunken ships in a class.

If to unite by means of UNION ALL, we'll get to the solution 3.12.2 which contains a similar mistake.

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 date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
several days ago
Wholesale fitted hats
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100