15:59

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Common mistakes in solving SELECT statement exercises

Database «Ships»

Exercise #151 page 2

Solution 3.5.2

Console

Execute

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:

The ships from Ships table with known launch year that is before 1941.
The ships which took part in battles till 1941 (clearly such ships should be launched before battle in which they took part).
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.

Pages

mistakes UNION DATEPART exercise 19

Previous [Exercise #39]

[Exercise #(-2)] Next

Last added:

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

SQL Problems and solutions

S. I. Moiseenko

Common mistakes in solving SELECT statement exercises

Database «Ships»

Exercise #151 page 2