Exercise #151

Despite of complexity level of 2, the solution of this exercise have caused, apparently, the greatest difficulties. And these are not due to complexity in construction of query, but due to logic of the solution of a problem.

Until recently this task has been formulated as: «Define the names of all ships, which were launched before 1918.». It seemingly has no difference, hasn’t it? Does the following decision not be a correct?

Solution 3.5.1

SELECT name AS shipName
FROM Ships
WHERE launched < 1941;

No. As it has fairly been noticed by visitors of a site, dates of battles are not considered here in any way. Really, the ship is necessary to include into the result set, if the year of launching of the ship, which took part in the battle before 1941, is unknown. In the initial formulation of this problem where year 1918 was presented, there was possible to not care about it, as the database formally contains the information on battles of the World War II which has begun in 1939.

Solution 3.5.2

SELECT name
FROM Ships
WHERE launched < 1941
UNION
SELECT ship
FROM Outcomes, Battles
WHERE name = battle AND
      DATEPART(YEAR, date) < 1941
UNION
SELECT ship
FROM Outcomes
WHERE ship IN (SELECT class
               FROM Ships
               WHERE launched < 1941
               );

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.

date < 1941

will give us comparison with date of year 1905, as a result of converting of an integer to datetime type (as number of days from the beginning of 1900 hat is a reference point of dates for settings by default in SQL Server). It is easy to be convinced of it by executing the query:

SELECT CAST(1941 AS DATETIME);

1905-04-26 00:00:00.000.

To remain within the Standard’s frame, it would be possible to use the following predicate:

date < '1941'

Then implicit transformation of type would give the result required. Again we shall check up:

SELECT CAST('1941' AS DATETIME);

1941-01-01 00:00:00.000

Naturally, the predicate containing a full date corresponding to the beginning of 1941 (January, 1) will be correct also:

date < '19410101'

However let’s return to our solution. It is incorrect. As it has been said, the situation when the head ship with unknown launch year is present only in Ships table here is not considered. To consider this situation we should add one more query to the union.

Solution 3.5.3

SELECT name   
FROM Ships  
WHERE launched < 1941   
UNION   
SELECT ship  
FROM Outcomes, Battles   
WHERE name = battle AND   
 DATEPART(YEAR, date) < 1941  
UNION   
SELECT ship  
FROM Outcomes  
WHERE ship IN (SELECT class  
 FROM Ships  
 WHERE launched < 1941  
 )  
UNION  
SELECT name  
FROM Ships  
WHERE name IN (SELECT class  
 FROM Ships  
 WHERE launched < 1941  
 );

How a lot of interesting things can be taken from this problem, only having changed a year!

However it is not yet all. We suggest you to find the additional ships responding conditions of the problem by yourself. Check yourself up by glancing into T&S.

T&S

To solve the problem on SQL-EX.RU