SQL sample query (Is my answer correct?)

Question

Tables:

• Classes (class, type, country, numGuns, bore, displacement)
• Ships (name, class, launched)
• Outcome (ship, battle, result)

Question:

Find for each class the number of ships of that class that sunk in battle.

My answer:

  SELECT ships.class, COUNT(*)
  FROM ships, outcome
  WHERE ships.name = outcomes.ship AND outcome.result = 'sunk'
  GROUP BY ships.class

Answer with join:

  SELECT class, COUNT(*)
  from ships
  inner join outcomes
  on name = ship 
  where outcome.result = 'sunk'
  group by class

Answer given in sample booklet:

  SELECT classes.class, COUNT(*)
  FROM classes, ships, outcomes
  WHERE classes.class = ships.class AND ship = name AND result = 'sunk'
  GROUP BY classes.class;

What I don't get is why did they have to include the classes table, isn't my query sufficient? I'm doing the same thing but not joining on the classes table. Thanks in advance.

Answer 1

The only real difference is that if there is a Class with no ships, the provided answer will include a row for it, where as your query would not.

Oops, sorry, I misread the sample answer initially. There is no real difference between them.

---

It's also worth noting that this syntax:

SELECT classes.class, COUNT(*)
FROM classes, ships, outcomes
WHERE classes.class = ships.class AND ship = name AND result = 'sunk'
GROUP BY classes.class;

is archaic and deprecated. This is the preferred modern syntax:

SELECT classes.class, COUNT(*)
FROM classes
INNER JOIN ships    ON classes.class = ships.class
INNER JOIN outcomes ON outcomes.ship = ships.name
WHERE  outcomes.result = 'sunk'
GROUP BY classes.class;

Answer 2

Most simple answer...

SELECT class,
SUM(CASE WHEN result='sunk' THEN 1 ELSE 0 END) 
FROM
(
    SELECT class, ship, result 
    FROM Classes c 
    LEFT JOIN Outcomes o 
    ON o.ship=c.class
UNION
    SELECT class, ship, result
    FROM Ships s 
    LEFT JOIN  Outcomes o 
    ON o.ship=s.class OR o.ship=s.name
)
AS res
GROUP BY class