Here is a schema about battleships and the battles they fought in:
Ships(name, yearLaunched, country, numGuns, gunSize, displacement)
Battles(ship, battleName, result)
A typical Ships tuple would be:
('New Jersey', 1943, 'USA', 9, 16, 46000)
which means that the battleship New Jersey was launched in 1943; it belonged to the USA, carried 9 guns of size 16-inch (bore, or inside diameter of the barrel), and weighted (displaced, in nautical terms) 46,000 tons. A typical tuple for Battles is:
('Hood', 'North Atlantic', 'sunk')
That is, H.M.S. Hood was sunk in the battle of the North Atlantic. The other possible results are 'ok' and 'damaged'
Question: List all the pairs of countries that fought each other in battles. List each pair only once, and list them with the country that comes first in alphabetical order first
Answer: I wrote this:
SELECT
a.country, b.country
FROM
ships a, ships b, battles b1, battles b2
WHERE
name = ship
and b1.battleName = b2.battleName
and a.country > b.country
But it says ambiguous column name. How do I resolve it? Thanks in advance
Well, name = ship is the problem. name could be from a or b, and ship from b1 or b2
you could do something like that :
select distinct s1.country, s2.country
from ships s1
inner join Battles b1 on b.ship = s1.name
inner join Battles b2 on b2.ship <> s1.name and b2.battleName = b1.battleName
inner join ships s2 on s2.name = b2.ship and s2.country < s1.country