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

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

Bookmark and Share
Pages 1 2
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.