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.