Tag Archives: Joins

Let’s try that again, shall we?


This query works for the previously mentioned SQL problem:

SELECT K.BATTLE
FROM
(SELECT S.name AS SHIP, B.name AS BATTLE, C.country AS COUNTRY
FROM
Ships S, Classes C, Battles B, Outcomes O
WHERE
S.class = C.class AND O.battle = B.name AND O.ship = S.name) K CROSS JOIN
(SELECT S.name AS SHIP, B.name AS BATTLE, C.country AS COUNTRY
FROM
Ships S, Classes C, Battles B, Outcomes O
WHERE
S.class = C.class AND O.battle = B.name AND O.ship = S.name) J
WHERE K.COUNTRY = J.COUNTRY AND K.BATTLE = J.BATTLE
GROUP BY K.BATTLE
HAVING COUNT(K.BATTLE) >=9

But it’s still not perfect, because it would record a positive answer if a single ship from 9 countries took part in a battle or two ships from three countries (something that certainly took place in the Pacific).

So I cheated and looked for clues – and this works properly:

SELECT K.BATTLE
FROM
(SELECT S.name AS SHIP, B.name AS BATTLE, C.country AS COUNTRY
FROM
Ships S, Classes C, Battles B, Outcomes O
WHERE
S.class = C.class AND O.battle = B.name AND O.ship = S.name) K
GROUP BY K.BATTLE
HAVING COUNT(K.COUNTRY) >=3

Solving an old SQL puzzle


HMS Belfast
Image by stewartmorris via Flickr

Ages ago I was puzzled by an online SQL test (sadly no longer online, but here’s the text):

DESCRIPTION

The database of naval ships that took part in World War II is under consideration. The database has the following relations:
Classes(class, type, country, numGuns, bore, displacement)
Ships(name, class, launched)
Battles(name, date)
Outcomes(ship, battle, result)
Ships in classes are arranged to a single project. A class is normally assigned the name of the first ship in the class under consideration (head ship); otherwise, the class name does not coincide with any ship name in the database.
The Classes relation includes the class name, type (bb for a battle ship, or bc for a battle cruiser), country where the ship was built, number of main guns, gun caliber (diameter of the gun barrel, in inches), and displacement (weight in tons). The Ships relation includes the ship name, its class name, and launch year. The Battles relation covers the name and date of a battle the ships participated; while the result of their participation in the battle (sunk, damaged, or unharmed – OK) is in the Outcomes relation. Note: the Outcomes relation may include the ships not included in the Ships relation.

PROBLEM

Point out the battles in which at least three ships from the same country took part.

Well, I now think I can solve it:

SELECT BETA.name
FROM (SELECT S.name, C.country
FROM Ships S, Classes C WHERE S.class = C.class) ALPHA,
(SELECT B.name, O.ship
FROM Battles B, Outcomes O WHERE B.name = O.battle) BETA
WHERE
ALPHA.name = BETA.ship
GROUP BY BETA.name
HAVING COUNT (
SELECT * FROM
(SELECT S.name, C.country
FROM Ships S, Classes C WHERE S.class = C.class) X,
(SELECT B.name, O.ship
FROM Battles B, Outcomes O WHERE B.name = O.battle) Y
WHERE
X.name = ALPHA.name AND
Y.name = BETA.name AND
X.name = B.ship AND
A.country = ALPHA.country
)> 2

SELECT BETA.BATTLE
FROM (SELECT S.NAME, C.COUNTRY
FROM SHIPS S, CLASSES C WHERE S.CLASS = C.CLASS) ALPHA,
(SELECT B.NAME, O.SHIP
FROM BATTLES B, OUTCOMES O WHERE B.NAME = O.BATTLE) BETA
WHERE
ALPHA.SHIP = BETA.SHIP
GROUP BY BETA.BATTLE
HAVING COUNT (
SELECT * FROM
(SELECT S.NAME, C.COUNTRY
FROM SHIPS S, CLASSES C WHERE S.CLASS = C.CLASS) X,
(SELECT B.NAME, O.SHIP
FROM BATTLES B, OUTCOMES O WHERE B.NAME = O.BATTLE) Y
WHERE
X.BATTLE = ALPHA.BATTLE AND
Y.BATTLE = BETA.BATTLE AND
A.COUNTRY = ALPHA.COUNTRY)
)> 2

Sadly as the original is not online I cannot test this and it is so complex I am far from 100% confident there is no mistake, but….