Example of GROUPBY Operator

Example: How many students sat each exam,

using GROUP BY, NATURAL LEFT JOIN, and COALESCE

SELECT CourseId, COALESCE (n, 0) AS n

FROM COURSE NATURAL LEFT JOIN

(SELECT CourseId, COUNT (*) AS n

FROM EXAM_MARK

GROUP BY CourseId) AS T

Explanation

• NATURAL JOIN: Note, however, that the use of LEFT makes this an outer join, whereas Codd's term natural join referred to the "inner" variety only.
• LEFT specifies that each unmatched row in the first join operand, COURSE, is to be extended with NULL for the column n.
• COALESCE (n, 0) AS n effectively replaces those appearances of NULL by the correct value, 0.