04:04

# Exercise #56 page 3

Solution 3.12.3

Console
Execute
`SELECT class, SUM(CASE  WHEN result = 'sunk'  THEN 1 ELSE 0  END)FROM (SELECT c.class, sh.name, o.ship, o.result FROM Classes c LEFT JOIN  Ships sh ON c.class = sh.class LEFT JOIN  Outcomes o ON ISNULL(sh.name, c.class) = o.ship ) tGROUP BY class;`

Let's disregard calculation of quantity of the sunken ships. A mistake is not in it, but is in how the set of rows for this calculation was formed.

So, the left join of Classes table with Ships table on a class column allows us to consider also classes which have not the ships in Ships table. It is correct, as we should deduce a given class with value of 0 as quantity of the sunken ships if those are absent.

Further the left join with Outcomes table which contains the information on results of battles has been carried out. The join predicate uses ISNULL function specific for  SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server, which returns the first argument if it is not NULL, and the second otherwise:

`ISNULL(sh.name, c.class) = o.ship`

I.e. the name of a ship in Outcomes table compares to the name of a ship received from Ships table or to the class name if the name of a ship contains NULL value. NULLs arise in the previous join when the class has no ships in Ships table; and only in this case!

Let's again consider the case when there is ship "A" of some class (Class_1) in Ships table, and Outcomes table contains both this ship and the head ship of Class_1 class (the name of head ship coincides with a class name). Let both these ships be sunken. Then the first join will give:

Class_1 А

The second join will look for in Outcomes table those rows that satisfy to the above predicate. Such row will be only one:

Class_1 А A
because the comparison will be only fulfilled by name of ship (A), but not by class!

 Pages 1 2 3 4 5