loading..
Русский    English
00:10
go

Exercise #56 page 3

Solution 3.12.3

Console
Execute
  1. SELECT class, SUM(CASE 
  2.  WHEN result = 'sunk' 
  3.  THEN 1 ELSE 0 
  4.  END)
  5. FROM (SELECT c.class, sh.name, o.ship, o.result
  6.  FROM Classes c LEFT JOIN 
  7.  Ships sh ON c.class = sh.class LEFT JOIN 
  8.  Outcomes o ON ISNULL(sh.name, c.class) = o.ship
  9.  ) t
  10. GROUP 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:

  1. 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!

Bookmark and Share
Pages 1 2 3 4 5
Tags
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.