Exercise #51 (tips and solutions)
Here we would like to present an interesting solution which employs only joinings:
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:
The above three instances are matched by the following rows of the resulting set (only significant columns are shown):
Then the left join with the classes table is performed by predicate:
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.
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:
Finally, the last left join is performed with the classes table, but this time by another predicate:
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:
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.