Common mistakes in solving SELECT statement exercises

Database «Ships»

Find the countries having classes of both ordinary ships and cruisers.

That is, we should find the countries, which had the ships of the type "bc" and "bb". The words “that had ever owned”, to the author's opinion, must take into effect the following logical chain of discourses:

• The database in its current state may not contain a ship of any class, while a country may had have it;
• Then , from where can we know that such ships had ever been? Only by available classes in the DB, as there is an information about the type and the country only in the table “Classes”;
• If we have the class, we say, of the type "bc" (cruiser), then there had been ships of this class, as the first ship, released by the current project, gives a name to the class, even if there are no such ships in the table “Ships” or “Outcomes”.

Conclusion: You should consider only the table “Classes” to solve this task. In the result we get quite a simple task. And I would not have written this explanation, if I had not been got suchlike underwritten solutions with the request to explain the reason why the system does not accept them. There is a solution:

Console

Execute

SELECT DISTINCT c1.country
FROM Classes c1 INNER JOIN 
 Classes c2 ON c1.country = c2.country INNER JOIN 
 Ships s1 ON c1.class = s1.class INNER JOIN 
 Ships s2 ON c2.class = s2.class
WHERE c1.type = 'bb' AND 
 c2.type = 'bc'
UNION
SELECT DISTINCT c1.country
FROM Classes c1 INNER JOIN 
 Classes c2 ON c1.country = c2.country INNER JOIN 
 Ships s1 ON c1.class = s1.class INNER JOIN 
 Outcomes s2 ON c2.class = s2.ship
WHERE c1.type = 'bb' AND 
 c2.type = 'bc' OR 
 c2.type = 'bb' AND 
 c1.type = 'bc';

SELECT DISTINCT c1.country FROM Classes c1 INNER JOIN Classes c2 ON c1.country = c2.country INNER JOIN Ships s1 ON c1.class = s1.class INNER JOIN Ships s2 ON c2.class = s2.class WHERE c1.type = 'bb' AND c2.type = 'bc' UNION SELECT DISTINCT c1.country FROM Classes c1 INNER JOIN Classes c2 ON c1.country = c2.country INNER JOIN Ships s1 ON c1.class = s1.class INNER JOIN Outcomes s2 ON c2.class = s2.ship WHERE c1.type = 'bb' AND c2.type = 'bc' OR c2.type = 'bb' AND c1.type = 'bc';

    Scheme

Which statement this solution corresponds to? Find the countries that have the ships of the both types in the DB? If the answer is “yes”, then this solution will be not correct all the way.

In the first join query the countries, which have the ships of the both types in the table Ships, are determined. In the second query the countries, which have the ship of one type in the table Ships, and in the table Outcomes – the other one, are determined.