Exercise #32 page 3

Solution 3.14.3

  1. SELECT country, AVG(bore*bore*bore/2)
  2. FROM Ships s FULL JOIN
  3. Outcomes o ON s.name = o.ship LEFT JOIN
  4. Classes c ON c.class = ISNULL(s.class, o.ship)
  5. WHERE c.class IS NOT NULL
  6. GROUP BY country;

The mistake lies in the join.

  1. Ships s FULL JOIN Outcomes o ON s.name = o.ship

It it visible only in case when some ship has participated in multiple battles, as then it will be counted multiple times in a result set. But we do not claim that we cant use a full outer join here. However, you need to care about excluding the duplicates.

Solution 3.14.4

  1. SELECT country, AVG(bore*bore*bore/2)
  2. FROM (SELECT country, bore, name
  3. FROM Classes LEFT JOIN
  4. Ships ON Ships.class = Classes.class
  5. UNION
  6. SELECT DISTINCT country, bore, ship
  7. FROM Classes C LEFT JOIN
  8. Outcomes O ON O.ship = C.class
  10. FROM SHips
  11. WHERE name = O.ship) AND
  12. NOT (ship IS NULL)
  13. ) ABC
  14. GROUP BY country;

Lets consider a case when there is class (e.g. name class_1, gun caliber 12) without ships of this class in the database and another class (e.g. name class_2, gun caliber 14) which has only one main ship in the database referred to in the Outcomes table. In this case both classes belongs to one country, lets say, country_1. For more clarity, add a class column to the table, then the first query within union will produce the following results:

country bore Name class
country_1 12 NULL class_1
country_1 14 NULL class_2
while the second query will produce the correct results:

country bore Name class
country_1 14 class_2 class_2

As you can see, we will consider two odd rows in our result set.

Bookmark and Share
Pages 1 2 3 4
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.