practicing WHERE and HAVING in SQL

here is the schema:

alt text http://img30.imageshack.us/img30/3324/123vk.jpg

here is the question:

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

here is my answer:

  SELECT battles.name 
    FROM battles,
         outcomes,
         ships,
         classes 
   WHERE outcomes.ship = ships.name 
GROUP BY battles.name 
  HAVING COUNT(classes.country) >= 3;

Can you please tell me why it is wrong and help me to correct it!

Answer

SELECT battle
  FROM outcomes, ships, classes
 WHERE outcomes.ship = ships.name AND ships.class = classes.class
 GROUP BY battle
HAVING COUNT(outcomes.ship) >= 3;

This will work for your question.

Advertisement

Answer