Exercise #54 (tips and solutions)

In order to employ UNION in the solution 3.10.2, it is not enough to perform unioning by only numGuns column. It is required of a list of columns that it should determine a ship unambiguously. Then duplicates rather then useful information will actually be excluded.

Below, a solution is presented which makes use of this expedient but contains a small mistake. And we leave it to the reader to find it.

  1. SELECT CAST(AVG(numguns*1.0) AS NUMERIC (6,2))
  2. FROM (SELECT ship, type, numguns
  3. FROM Outcomes RIGHT JOIN
  4. Classes ON ship = class
  5. UNION
  6. SELECT name, type, numguns
  7. FROM Ships s JOIN
  8. Classes c ON c.class = s.class
  9. ) AS al_sh
  10. WHERE type = 'bb';

To return to discussion of exercise #54

To solve a problem on SQL-EX.RU

Bookmark and Share
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
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100