15:31

# Exercise #46 page 2

Where is this information taken from? The information about ship`s class is in Ships table, so we need to use one more table. Thus, if the ship from Outcomes table is present in the Ships table (Outcomes.ship = Ships.name), then the class is certain, and therefore all information about this ship can be taken from Classes table (Ships.class = Classes.class). Let`s consider solution with such joins:

Solution 3.1.2

Console
Execute
`SELECT o.ship, c.displacement, c.numGunsFROM Outcomes o LEFT JOIN  Ships s ON o.ship = s.name LEFT JOIN  Classes c ON s.class=c.classWHERE o.battle = 'Guadalcanal';`

The result is true on basic database, but the system denies solution. The left join guarantees that the ship draws in outer dataset even if its class is unknown (there is no ship in the Ships table). In the last case such row will be returned:

`A ship NULL NULL`

The mistake is covered in Bismarck ship. The error occures when the Outcomes table has lead ship which is absent in Ships table. Let`s suppose that Bismarck ship participates in the battle of Guadalcanal. The concerned query returns such string:

`Bismarck NULL NULL`
that`s why this ship is absent in the Ships table. However the characteristics are certain, because the class of ship is known (the ship is lead). The right row will be:

`Bismarck 8 42000`

Row:

`A ship NULL NULL`
will be return if the ship which is not lead had participated in battle and it isn`t present in the Ships table. This situation might occur if the class of ship is unknown, however, this it`s impossible, because the `class` column have NOT NULL restriction.

 Pages 1 2 3