Common mistakes in solving SELECT statement exercises

Database «Ships»

But there is one more variant , when there are only the leading ships in Outcomes of both types. Insert into your database the following rows:

INSERT INTO Classes 
 VALUES('c_bb', 'bb' , 'AAA' ,10 ,15 , 35000);
INSERT INTO Classes 
 VALUES('c_bc', 'bc', 'AAA', 6, 15, 45000);
INSERT INTO Outcomes 
 VALUES('c_bb', 'Guadalcanal', 'ok');
INSERT INTO Outcomes 
 VALUES('c_bc', 'Guadalcanal', 'ok');

The country AAA has the ships of the both types. However, the aforecited query will not output this country as it was expected.

I will note also that DISTINCT in both queries is completely unwanted, because UNION will eliminate possible duplicates. From the logical point of view this remark is not significant. However, from the point of view of optimization it is quite an important moment. Server spends the considerable resources on deleting duplicates, that's why it doesn't need to do it several times. Compare the plans of queries processing with DISTINCT and without it.

And here is an example of half-and-half solution accepted by the system:

Console

Execute

SELECT DISTINCT country
FROM Classes RIGHT JOIN 
 (SELECT DISTINCT COALESCE(ship, name) AS name, class
 FROM Outcomes FULL OUTER JOIN 
 Ships ON ship = name
 )AS z ON z.name = Classes.class OR 
 z.class = Classes.class
WHERE type = 'bb' AND
 country IN (SELECT country 
 FROM classes
 WHERE type = 'bc'
 );

SELECT DISTINCT country FROM Classes RIGHT JOIN (SELECT DISTINCT COALESCE(ship, name) AS name, class FROM Outcomes FULL OUTER JOIN Ships ON ship = name )AS z ON z.name = Classes.class OR z.class = Classes.class WHERE type = 'bb' AND country IN (SELECT country FROM classes WHERE type = 'bc' );

    Scheme

Here all the ships from both tables – Ships and Outcomes – are taken. Then their class is determined by joining the table Classes, and those of them that have the ‘bb' (battle ships) type are being selected. Finally, we check that the country of have found ships earlier, also has the classes ' bc '. The solution turned out to be correct only because the countries, that have the classes of both types, also have in the current state of DB the ships of ‘bb' type.

To make such solutions unacceptable is very easy: it is enough to insert two classes into the table Classes (of 'bc' and 'bb' types) for the country that doesn't have ships in the DB at all. However, it's likely better to adjust the formulation, say, in this way:

Find the countries having classes of both ordinary ships ('bb'), and cruisers ('bc').

Change of the formulation would rather be made by the issue of this number of the routing. Although, I hope that the have made analysis of the solutions will turn out to be useful.