22:24

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Common mistakes in solving SELECT statement exercises

Database «Ships»

Exercise #37 page 4

Solution 3.2.4

Approach based on unions is not unique. The following solution uses joins.

Console

Execute

SELECT Classes.class
FROM Outcomes RIGHT OUTER JOIN
 Classes ON Outcomes.ship = Classes.class LEFT OUTER JOIN
 Ships ON Classes.class = Ships.class
GROUP BY Classes.class
HAVING (COUNT(COALESCE (Outcomes.ship, Ships.name)) = 1);

Right join of Outcomes and Classes tables gives us the leading ships, with so doing the Outcomes.ship column will contain NULL value if the leading ship will not be in the Outcomes table. Then is executed left join of Classes and Ships tables on foreign key. The Ships.name column will contain a NULL value if the class has no ships in the Ships table. The resulting record set is grouped by class, and then the filtering predicate is used

COUNT(COALESCE (Outcomes.ship, Ships.name)) = 1

Let us stop on this elegant method.

Notes:

The author is not ironic when he says "beautiful", "elegant", etc., on the wrong approach to solving the exercise. Here, most of the queries are written by professionals, fluent in the language of SQL. The analysis such solutions is a good school for beginners. The mistakes in these solutions are usually associated with neglect of a particular subject area, and are often easily corrected by purely cosmetic means.

So to make things completely clear, we give examples of four possible rows (taken from the available database except the last case), which result from the join. Here they are:

Ship	class	name
Bismarck	Bismarck	NULL
Tennessee	Tennessee	California
Tennessee	Tennessee	Tennessee
NULL	Yamato	Musashi
NULL	Yamato	Yamato
NULL	Class_1	NULL

NULL in the column name for the Bismarck class means that the lead ship is only available in the Outcomes table. Ships "California" and "Tennessee" of Tennessee class are in Ships table, in this case, the leading ship is also in the Outcomes table. In the third case, the two ships of class Yamato present in the Ships table, the Outcomes table does not have leading ship of the class. For the fourth case of Class_1, the class has no ships in the database.

We return to the predicate. Function COALESCE (Outcomes.ship, Ships.name) returns the first non-NULL value of its arguments, or NULL, if both arguments are NULL values. More information about the COALESCE function can be found in paragraph 5.10.

The COUNT aggregate function with an argument counts non-NULL values in a group. The result for the Bismark class is 1; the result for the Tennessee and Yamato classes is 2 and for the Class_1 is 0. Only the Bismark of the four classes is present in the resulting set because the predicate is true for this class only.

This solution is free from the mistake of the previous solution 3.2.3: a leading ship contained both in the Ships table and the Outcomes table will be counted just once, because the only row will be present in the resulting set.

Attention:

A question. Is the last assertion always true for our database? Provide an example of data when it is not true. Incidentally this adds another error to the solution.

Furthermore the same error as in the example 3.2.1 rises in this case. If the row mentioned in the example is inserted into the database, just one row will be produced for two Bismark class ships as a result of the union: