loading..
Русский    English
07:28

Exercise #32 page 4

Solution 3.14.5

Console
Execute
  1. SELECT Country, AVG(bore*bore*bore)/2
  2. FROM (SELECT c.country, bore
  3. FROM Classes C,
  4. Ships S
  5. WHERE S.class = C.Class AND
  6. NOT bore IS NULL
  7. UNION ALL
  8. SELECT country, bore
  9. FROM Classes C,
  10. OutComes O
  11. WHERE O.Ship = C.Class AND
  12. NOT EXISTS (SELECT 1
  13. FROM Ships S
  14. WHERE s.Name = O.Ship
  15. ) AND
  16. NOT bore IS NULL
  17. GROUP BY country, bore
  18. ) AS Q1
  19. GROUP BY country;

Two-attribute relations {country, caliber} are joined in the sub query. The second query predicate is the following:

  1. NOT EXISTS (SELECT 1
  2. FROM Ships S
  3. WHERE s.Name = O.Ship
  4. )
It eliminates possibility of multi-counting the ship if this ship is present in both Ships and Outcomes tables. This justifies the use of UNION ALL operator. Duplicates which can appear in case of ship participating in multiple battles are excluded by grouping by country and caliber.

However, consider a case when a country has several classes of ships with the same caliber and these ships are present in the Outcomes table. As the result, only one ship would be counted, instead of several times what makes the given solution a mistaken.

To solve the problem on SQL-EX.RU

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