Exercise #56 (tips and solutions)

Below is a solution which accounts the sunken ships correctly. This solution has an insignificant mistake; we suggest you to find it by yourself. In case of difficulty, return to solution 3.12.3.

  1. SELECT class, SUM(r) sunks
  2. FROM (SELECT name, class, CASE
  3. WHEN result = 'sunk'
  4. THEN 1 ELSE 0
  5. END r
  6. FROM Ships AS s LEFT JOIN
  7. Outcomes AS o ON o.ship = s.name
  8. UNION
  9. SELECT ship, class, CASE
  10. WHEN result = 'sunk'
  11. THEN 1 ELSE 0
  12. END r
  13. FROM Classes c JOIN
  14. (SELECT *
  15. FROM Outcomes
  16. WHERE NOT Ship IN (SELECT name
  17. FROM Ships)
  18. ) AS ot ON ot.ship = c.class
  19. ) AS b GROUP BY class;

To return to discussion of exercise #56

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 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
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.