Main page

## Typical errors at solution of exercises. Exercise 35

S. Moiseenko

Point out the names, displacements and number of guns of ships participating in the battle at Guadalcanal.

Why doesn't the system accept the following solution:

SELECT o.Ship , c.displacement , c.numGuns
FROM outcomes o
LEFT JOIN Ships s ON o.ship = s.Name
LEFT JOIN classes c ON s.class = c.Class
LEFT JOIN battles b ON b.Name = o.Battle
WHERE ( b.Name =' Guadalcanal ' ) ?

At first sight everything is true. Joining with selection:

outcomes o
LEFT JOIN battles b ON b.Name = o.Battle
...
WHERE ( b.Name =' Guadalcanal ')

gives us all ships sitted in battle at Guadalcanal . Further,

LEFT JOIN Ships s ON o.ship = s.Name
LEFT JOIN classes c ON s.class = c.Class

joining with table Ships lets us to find out a class of the ship, and joining Ships with Classes lets us to find out required characteristics of the ship. Thus the left joining guarantees appearance of the ship in a result set even in that case if its class is unknown (the ship is not presented in Ships or is, but its class is unknown - NULL ). In the latter case it will be received the row of type:

Ship NULL NULL

The mistake consists in notorious “ Bismarck ”. Not in it exactly, but in that situation when in Outcomes there is a head ship which there isn't in Ships . Let us assume that " Bismarck " was at Guadalcanal . Since this ship isn't in the Ships, the query considered by us will return such row:

Bismarck NULL NULL

However, its characteristics are known to us, as the class (the head ship!) of the ship is known. The c orrect line will be:

Bismarck 8 42000

We receive a row

Ship NULL NULL

only in that case if not head ship absent in table Ships took part in the battle (or at a unknown class).

INSERT INTO Ships VALUES('Terplits', 'Bismarck', 1940)
INSERT INTO outcomes VALUES('Kyoto','Guadalcanal','ok')

Main page

 Usage of any materials of this site is possibleonly under condition of mandatory allocation of the direct link to a sitehttp://www.sqlbooks.ruon each page where used materials are placed. Main Articles Books