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