Exercise #38 page 2
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:
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:
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.