loading..
Ðóññêèé    English
02:51
go

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.

Console
Execute
  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
The book was updated
yesterday
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100