practicing WHERE and HAVING in SQL

Question

here is the schema:

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 1

I have quite never used the HAVING clause of the GROUP BY statement. However, GROUP BY should be used for aggregation purposes only. So, if you have no agregation function (SUM, MAX, MIN, AVG, COUNT, etc.) selected, you will not be able to GROUP BY.

Answer 2

You have messed the joins on the tables. You can't just list the tables, you have to specify how they join together

From Battles
Inner Join Outcomes On Outcomes.Battle = Battles.name
Inner Join Ships.Name On Ship.Name = Outcomes.Ship
Inner Join Classes.Class = Ship.Class

Answer 3

This:

SELECT battles.name 
  FROM battles,
       outcomes,
       ships,
       classes 
 WHERE outcomes.ship = ships.name 

...fundamentally flawed because there's only join criteria between the OUTCOMES and SHIPS. The result is a cartesian product. It's valid ANSI-89 syntax, but won't return a resultset remotely resembling what you would expect.

If you only want the battle "name", use:

  SELECT o.battle
    FROM OUTCOMES o
    JOIN SHIPS s ON s.name = o.ship
    JOIN CLASSES c ON c.class = s.class
GROUP BY o.battle
  HAVING COUNT(c.country) >= 3

If you want the battle table details, use:

SELECT b.*
 FROM BATTLES b
 JOIN (SELECT o.battle
         FROM OUTCOMES o
         JOIN SHIPS s ON s.name = o.ship
         JOIN CLASSES c ON c.class = s.class
     GROUP BY o.battle
       HAVING COUNT(c.country) >= 3) x ON x.battle = b.name

Answer 4

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

Answer 5

SELECT DISTINCT BattleName
FROM (
   SELECT Battles.name AS BattleName, Class.Country, COUNT(*)
   FROM Battles
   JOIN Outcomes ON battles.name = Outcomes.battle 
   JOIN Ships ON Outcomes.ship = Ships.name
   JOIN Classes ON Ships.class = Class.class
   GROUP BY battles.name, Classes.country 
   HAVING COUNT(*) >= 3;
)

The main flaw in the original query was that the joins between tables weren't expressed, resulting in plain cartesian product.

Another problem was that the country wasn't listed to allow a count of ship per country. This modified query was then made the subquery, to allow selecting the BattleName only once.

Notes:
- I added the COUNT(*) in the SELECT list. This is more by safety/ignorance, I think that some SQL implementation would require the aggregate value found in the HAVING clause to exist in the SELECT list. (I may be wrong on this).
- Rather than COUNT(*) one may need to use say COUNT(Classes.country) or any other field (if this this MS-Access as it appears, the COUNT(*) syntax may not be allowed).

Answer 6

I can't see you image (filtered out by big brother)

However, just remember, WHERE filters the entire pool of rows first, GROUP BY combines those remaining rows, and Having filters those groups.

FROM & JOINs determine & filter rows WHERE more filters on the rows
GROUP BY combines those rows into groups
HAVING filters groups
ORDER BY arranges the remaining rows/groups

Answer 7

select distinct A.battle from (select country, o.battle from outcomes o left join ships s on s.name = o.ship left join classes c on c.class = s.class
group by o.battle, country having count(country) >= 3) A