Exercise #46

The all ships which had taken part in the battle of Guadalcanal is in the Outcomes table, and all the characteristics is in the Classes table. The first occured idea is in the joining of this tables:

Solution 3.1.1

SELECT Outcomes.ship, Classes.displacement, Classes.numGuns
FROM Classes RIGHT JOIN
Outcomes ON Classes.class = Outcomes.ship
WHERE Outcomes.battle = 'Guadalcanal';

The outer join is using correctly, because task says about ships which had taken part in battle, so we need to select all ships, no matter the name matches the class name or it does not. Note that the inner join returns empty set, because there is no lead ships in the main database which take part in this battle. So we have:

The right answer is:

Where is this information taken from? The information about ships 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). Lets consider solution with such joins:

Solution 3.1.2

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
WHERE 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 isnt present in the Ships table. This situation might occur if the class of ship is unknown, however, this its impossible, because the class column have NOT NULL restriction.

In conclusion, I adduce one more solution with such mistake, but without using outer joins.

Solution 3.1.3

SELECT a.ship, b.displacement, b.numguns
FROM Outcomes a, Ships c, Classes b
WHERE a.battle='Guadalcanal' AND
a.ship = c.name AND
c.class = b.class
UNION
SELECT a.ship, NULL AS displacement, NULL AS numguns
FROM Outcomes a
WHERE a.battle = 'Guadalcanal' AND
a.ship NOT IN (SELECT name
FROM Ships
);

Lets note that the first offered solution would return right result for such lead ship. Thats why that to solve this task we need to append the first solution to the second. The ways how should not to “append” solution, you can see in the Chapter four.

T&S

To solve the problem on SQL-EX.RU