Exercise #38

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:

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';

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.

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:

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'
                    );

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:

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.

To solve the problem on SQL-EX.RU