Русский    English

Exercise #51 (tips and solutions)

Here we would like to present an interesting solution which employs only joinings:

  2. WHEN sh.name IS NOT NULL
  3. THEN sh.name
  4. ELSE ou.ship
  5. END name
  6. FROM Ships sh FULL JOIN
  7. Outcomes ou ON ou.ship = sh.name LEFT OUTER JOIN
  8. Classes cl ON ou.ship = cl.class OR
  9. sh.class = cl.class LEFT OUTER JOIN
  10. Classes cl2 ON cl.displacement = cl2.displacement AND
  11. cl.numGuns < cl2.numGuns
  12. WHERE cl.class IS NOT NULL AND
  13. cl2.class IS NULL;

Quite complex logic of the solution will undoubtedly be useful for training purposes at this stage. Let us start, as usual, from the FROM statement. FULL JOIN yields all ships from the database. And here the following variants are possible:

  • a ship can be present in the Ships table only;
  • a ship can be present in both tables;
  • a ship can be present in the Outcomes table only.

The above three instances are matched by the following rows of the resulting set (only significant columns are shown):

Ship name
NULL ship_1
ship_2 ship_2
ship_3 NULL

Then the left join with the classes table is performed by predicate:

  1. ou.ship = cl.class OR
  2. sh.class = cl.class

That is, either it is the name of a ship from Outcomes that coincides with the name of a class (unappropriated leading ships), or it is the class of a ship from Ships that does.

ship name sh.class cl.class
NULL ship_1 class_1 class_1
ship_2 ship_2 class_1 class_1
class_1 NULL class_1 class_1

The third row of the table corresponds to the case when a ship's class is unknown (the ship is not the lead one!) and the fourth describes the instance of a lead ship which is absent in the Ships table. Jumping the gun, we will note that the third line can provide no information about displacement and the number of guns of such ship, therefore it is filtered with the following predicate in the WHERE statement of the solution in question:

  1. cl.class IS NOT NULL

Finally, the last left join is performed with the classes table, but this time by another predicate:

  1. cl.displacement = cl2.displacement AND
  2. cl.numGuns < cl2.numGuns

The first stipulation of the predicate is clear - we join rows across equal values of displacement, since we are looking for the maximum in groups of ships having equal displacement. In order to understand the second stipulation let us again turn to an example. We will add necessary columns to our table and examine the result of joining by the given predicate, using as an example, say, the first row of the previous table with the following values of the number of guns for the classes of ships having displacement of 30 000 tons:

class_1 16
class_2 10
class_3 14

ship Name sh.class cl.class displacement cl2.class cl.numGuns cl2.numGuns
NULL ship_1 class_1 class_1 30000 NULL 16 NULL
NULL ship_2 class_2 class_2 30000 class_1 10 16
NULL ship_2 class_2 class_2 30000 class_3 10 14
NULL ship_3 class_3 class_3 30000 class_1 14 16

Thus, the ships belonging to a class which has maximum number of guns in its displacement group will have null values in columns related to the cl2 table (with the left join!) including cl2.class column, because the predicate is not satisfied. It is this criterium that is used in the WHERE clause for selection of the rows complying with the problem statement.

Lastly, CASE operator establishes the ship's name in the final resulting set, selecting from two options - ship or name column - the one which contains a non-null value.

If the above exposition is not quite clear, refer to chapter 5 regarding outer joins (item 5.6.1).

It is an interesting solution, but it does not take into account all possible data variants. It will fail to produce the correct result, if there is a class with an unknown number of guns on its ships. Pay attention to the data schema: numGuns column allows for null values! We suggest that you analyze the cause of the error by yourself and correct the examined solution.

To return to discussion of exercise #51

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