Exercise #151 page 2

Solution 3.5.2

  1. SELECT name
  2. FROM Ships
  3. WHERE launched < 1941
  4. UNION
  5. SELECT ship
  6. FROM Outcomes, Battles
  7. WHERE name = battle AND
  8. DATEPART(YEAR, date) < 1941
  9. UNION
  10. SELECT ship
  11. FROM Outcomes
  12. WHERE ship IN (SELECT class
  13. FROM Ships
  14. WHERE launched < 1941
  15. );

The solution 3.5.2 takes into account:

  1. The ships from Ships table with known launch year that is before 1941.
  2. The ships which took part in battles till 1941 (clearly such ships should be launched before battle in which they took part).
  3. The ships from Outcomes table with names that coincide with a class names of any ship (launched till 1941) from Ships table.

It should be noted, that possible duplicates are eliminated by uniting by means of UNION.

Last variant considers also those cases when the head ship took part in battles only after 1941 as earlier battles are considered by the previous inquiry. It remains to find out what it is necessary for. The answer on this question could be found in the hung head ships. So, a ship from Outcomes with the name matched with one of the classes ` name (head ship) is absent from the Ships table or it is present there but with unknown launching year. Let us assume that there is another ship with the same class and known launching year in the Ships table. If this year is an earlier one than 1941, then the head ship is necessary to be included into the result set with the mentioned ship. This follows the fact that the head ship is the first ship in its class. Therefore, it should be launched not later than launch year of any other ship of the class.

Bookmark and Share
Pages 1 2 3 4
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
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.