loading..
Русский    English
10:44

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:

  1. INSERT INTO Classes
  2. VALUES('c_bb', 'bb' , 'AAA' ,10 ,15 , 35000);
  3. INSERT INTO Classes
  4. VALUES('c_bc', 'bc', 'AAA', 6, 15, 45000);
  5. INSERT INTO Outcomes
  6. VALUES('c_bb', 'Guadalcanal', 'ok');
  7. INSERT INTO Outcomes
  8. 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:

Console
Execute
  1. SELECT DISTINCT country
  2. FROM Classes RIGHT JOIN
  3. (SELECT DISTINCT COALESCE(ship, name) AS name, class
  4. FROM Outcomes FULL OUTER JOIN
  5. Ships ON ship = name
  6. )AS z ON z.name = Classes.class OR
  7. z.class = Classes.class
  8. WHERE type = 'bb' AND
  9. country IN (SELECT country
  10. FROM classes
  11. WHERE type = 'bc'
  12. );

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.

To solve the problem on SQL-EX.RU

Bookmark and Share
Pages 1 2
Tags
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
https://exchangesumo.com/obmen/NIXEUR-PSBRUB-sort
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.