Books and articles about SQL Rambler's Top100 : 22 October 2017 09:31:44


www.sql-ex.ru
Skip Navigation Links  

 

Print  Print version

Main page

Typical errors at solution of exercises. Exercise 38

S. Moiseenko

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 condition 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:

Find the countries having classes of both ordinary ships (bb'), and cruisers( bc ').

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.

Given examples here can be done directly on the website by selecting the check box Without checking on the page with SELECT exercises.

Main page

Print  Print version


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
@Mail.ru Rambler's Top100 Alt   SQL: , ,    SQL Copyright c 2002-2006. All rights reserved.