sql Query help - little help

Question

Short database description "Ships":

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.

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

can someone help with this query ? i haven't used sql for quite a while

thanks

edit: as i was told it's not allowed to ask for a broad question , i'll be more specific :

i though about how i can do it , but eventually i need some kind of way to count duplicates ? for exmaple..for the list

if i'll say count > 2 i'll get a and c

Name a a b a b a c c c

Answer 1

I don't understand everything in your description, but let's start with this. Is this close enough? Not quite sure what " ... Outcomes relation may include the ships not included in the Ships relation" means. Hope this gets you started.

SELECT DISTINCT b.[Name] AS [Battle Name]
FROM    Class AS c
        JOIN Ship AS s ON s.ClassID = c.ClassID
        JOIN Outcome AS o ON o.ShipID = s.ShipID
        JOIN Battle AS b ON b.BattleID = o.BattleID
GROUP BY b.[Name], c.Country
HAVING  COUNT(c.Country) >= 3

Answer 2

some tips

1. group by and having

2. count(*)

3. ???

4. PROFIT!

Answer 3

Try this

SELECT *
FROM   Battles b
WHERE  EXISTS (
       SELECT     NULL
       FROM       Outcomes   o            
       INNER JOIN Ships      s
       ON         o.ship   = s.name
       INNER JOIN Classes    c
       ON         s.class  = c.class
       WHERE      o.battle = b.name
       GROUP BY   c.country
       HAVING     count(*) >= 3
   )