Common mistakes in solving SELECT statement exercises

Tips and solutions

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

Console

Execute

SELECT DISTINCT CASE 
 WHEN sh.name IS NOT NULL 
 THEN sh.name 
 ELSE ou.ship
 END name
FROM Ships sh FULL JOIN
 Outcomes ou ON ou.ship = sh.name LEFT OUTER JOIN
 Classes cl ON ou.ship = cl.class OR 
 sh.class = cl.class LEFT OUTER JOIN
 Classes cl2 ON cl.displacement = cl2.displacement AND
 cl.numGuns < cl2.numGuns
WHERE cl.class IS NOT NULL AND 
 cl2.class IS NULL;

SELECT DISTINCT CASE WHEN sh.name IS NOT NULL THEN sh.name ELSE ou.ship END name FROM Ships sh FULL JOIN Outcomes ou ON ou.ship = sh.name LEFT OUTER JOIN Classes cl ON ou.ship = cl.class OR sh.class = cl.class LEFT OUTER JOIN Classes cl2 ON cl.displacement = cl2.displacement AND cl.numGuns < cl2.numGuns WHERE cl.class IS NOT NULL AND cl2.class IS NULL;

    Scheme

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):

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

ou.ship = cl.class OR 
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.

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:

cl.class IS NOT NULL

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

cl.displacement = cl2.displacement AND
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:

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