loading..
Ðóññêèé    English
18:55
go

Exercise #56 page 4

Solution 3.12.4

Console
Execute
  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.
Pages 1 2 3 4 5
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
several days ago
https://exchangesumo.com/obmen/SVBRUB-NIXEUR/
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.