loading..
Русский    English
23:26

Exercise #121 (tips and solutions)

It's an awful trouble with this head ships! Below we see how wrong solution has gotten for the task about the head ships, which were launched before 1941.

Console
Execute
  1. SELECT class
  2. FROM Classes
  3. WHERE EXISTS (SELECT 1
  4. FROM Ships
  5. WHERE launched < 1941 AND
  6. Ships.class = Classes.class
  7. );

I.e. class here is identified with presence of the head ship in the database, namely, the class, which include a ship launched before 1941, is being found. As it is said in the database definition, head ship is the ship with name matching with the name of a class. But that not always exist a ship the name of which coincides with a class name. Therefore to search for the head ships that are absent from Ships table follows exclusively in Outcomes table.

At last, about variant unconsidered in the solution 5.3.3. So, the next situation is possible. There is a head ship with unknown launch year. Moreover, it could take part in the only one battle, for example in 1945 (i.e. after 1941). Let all the ships of the same class have unknown launch year (NULLs are allowed). But if at least one of these ships took part in battle before 1941, we should include both of the ships into the result set, because the head ship (if it exists!) is launched before any other ship in its class.

Here is the solution which, apparently, considers all the stipulated moments:

Console
Execute
  1. -- Ships launched before 1941
  2. SELECT name
  3. FROM Ships
  4. WHERE launched < 1941
  5.  
  6. UNION
  7. -- Ships participated in battles before 1941
  8. SELECT ship
  9. FROM Outcomes JOIN
  10. Battles ON Battles.name = Outcomes.battle
  11. WHERE date < '19410101'
  12.  
  13. UNION
  14. -- Head ships in Outcomes, class of which includes other ships
  15. -- launched before 1941
  16. SELECT ship
  17. FROM Outcomes
  18. WHERE ship IN (SELECT class
  19. FROM Ships
  20. WHERE launched < 1941
  21. )
  22.  
  23. UNION
  24. -- Head ships in Outcomes when at least one ship from
  25. -- the same class participated in a battle before 1941
  26. SELECT ship
  27. FROM Outcomes
  28. WHERE Ship IN (SELECT class
  29. FROM Ships JOIN
  30. Outcomes ON Ships.name = Outcomes.ship JOIN
  31. Battles ON Battles.name = Outcomes.battle
  32. WHERE date < '19410101'
  33. );

However the system all the same informs on a mistake …

As it has already been noted in item 3.5, the head ships with unknown launch year can be not only in Outcomes table, but also in Ships table. Moreover, such ships will not be considered by above query if they are absent from Outcomes table, i.e. either they did not participate in battles or the information on their participation is unknown.

Thus it is a pure logic, nothing more.

To return to discussion of exercise #121

To solve a problem on SQL-EX.RU


Bookmark and Share
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 CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100