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:

  1. SELECT DISTINCT c1.country
  2. FROM Classes c1 INNER JOIN
  3. Classes c2 ON c1.country = c2.country INNER JOIN
  4. Ships s1 ON c1.class = s1.class INNER JOIN
  5. Ships s2 ON c2.class = s2.class
  6. WHERE c1.type = 'bb' AND
  7. c2.type = 'bc'
  8. UNION
  9. SELECT DISTINCT c1.country
  10. FROM Classes c1 INNER JOIN
  11. Classes c2 ON c1.country = c2.country INNER JOIN
  12. Ships s1 ON c1.class = s1.class INNER JOIN
  13. Outcomes s2 ON c2.class = s2.ship
  14. WHERE c1.type = 'bb' AND
  15. c2.type = 'bc' OR
  16. c2.type = 'bb' AND
  17. c1.type = 'bc';