Русский    English

Exercise #54 page 2

Solution 3.10.2

  1. SELECT CAST(AVG(numGuns*1.0) AS NUMERIC(10,2))
  2. FROM (SELECT numguns
  3. FROM Classes c JOIN
  4. Ships s ON c.class = s.class
  5. WHERE type = 'bb'
  7. SELECT numguns
  8. FROM Classes] c JOIN
  9. Outcomes o ON c.class = o.ship
  10. WHERE type='bb' AND
  11. o.ship NOT IN(SELECT name
  12. FROM Ships
  13. )
  14. ) t;

Pay attention to conversion of data type to a number with fixed point, which carries out a demanded rounding off of result.

In the subquery, two queries are combined (UNION ALL). The first one defines the number of guns for the ships from the Ships table which belong to linear ships' classes (‘bb' type). The second one takes into account the head ships of corresponding classes if they are not in the Ships table.

So, it's an attempt to take into account each ship in the database only once. As UNION ALL is used for combining data, the duplicates won't be removed. This is important because many ships will have same number of guns and only this column is printed out in the SELECT clause of this sub query.

But however the mistake is raised by UNION ALL. Let's take a formal turn, that is, we won't think of the subject area but will refer to the scheme. In the Ships table the primary key is the ship's name, that's why the first query in combination will give us one string for each ship of a known class. But in the Outcomes table the key is a pair of {ship, battle}, that means, its unique value is granted for a combination of a ship's name and a battle in which it took part. What results from this is that the same vessel can be mentioned in the Outcomes table for several times if it participated in several battles.

As a result, the second query will return duplicated ships if the leading ship took part in several fights. That's what makes this request wrong.

On the other hand, we can't write UNION instead of UNION ALL according to the reason stated above.


To solve the problem on SQL-EX.RU

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