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.

To solve the problem on SQL-EX.RU

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