04:11

Exercise #38 page 1

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.countryFROM 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.classWHERE c1.type = 'bb' AND  c2.type = 'bc'UNIONSELECT DISTINCT c1.countryFROM 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.shipWHERE 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

 Pages 1 2
Tags
The book was updated
several days ago