Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 22 October 2017 09:33:16


www.sql-ex.ru
Skip Navigation Links  

 

Print  Print version

Main page

Typical errors at solution of exercises. Exercise 41

S. Moiseenko

Define the names of all the ships in the database which were definitely launched before 1941.

Until recently this task has been formulated as:

Define the names of all ships, which were launched before 1918.

This task assumed a fairly simple solution:

SELECT name AS shipName
FROM Ships
WHERE launched < 1918

But, as Zhekaus truthfully mentioned, the dates of the battles are not considered here. 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 1918, is unknown. The database does not include the battles before 1918. The system will accept this solution without taking this fact into consideration.

In order not to change the data, it's easier to reformulate the task using the comparing with another date. Now we need to find the ships, which were launched before 1941.

However there is one more nuance. We will need to include into the result set a ship which took part in the later battle and with unknown launching year.

Why? 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 before 1941.

There is another trap. One can only change the year, getting so much interesting things :-). Try to find it out by yourself. Otherwise let's read next.

It's an awful trouble with this head ships! As it is said in the database definition, the name for the class should be given by the first ship in this class, also known as head ship, or by the project name. In the latter case the head ship is not exist. Therefore, when task asks “all the ships… ”, we shouldn't consider the Classes table as the list of head ships.

Below we see how wrong solution has gotten for the task about the head ships, which were launched before 1941.

SELECT class FROM Classes WHERE EXISTS
(SELECT 1 FROM Ships WHERE launched < 1941 and Ships.class = Classes.class )

So, the head ship is the ship with name matching with the name of a class.

At last I'll talk about the trap, mentioned above. It turned out, that the trap was for me :) . Until I wrote this explanation, which you are reading now, I had to reply to several letters about this every day.

So, the next situation is possible. There is a head ship with unknown launching year. Moreover, it could take part in the only one battle, for example in 1945. There is another ship in the same class, also with unknown launching year. But if it 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.

Thus it is a pure logic, nothing more.

Given examples here can be done directly on the website by selecting the check box “Without checking” on the page with SELECT exercises.

Main page

Print  Print version


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.