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