Why the Coaching Centre's 'Official' Average Is Inflated
Green Valley Coaching Centre stores its Class XII test data in two tables. STUDENT has 45 rows, one per enrolled student (ROLL is the primary key). MARKS has 42 rows with columns ROLL, SUBJECT and MARKS; three of those rows have NULL in MARKS because the student was absent, and three enrolled students have no row at all in MARKS. The centre's clerk runs:
SELECT COUNT(*), COUNT(marks), AVG(marks) FROM marks;
and gets 42, 39 and 70.00. The director, who computed 2730/42 = 65.00 by hand, is angry that the 'official' average is inflated. Meanwhile a report meant to list every enrolled student with their marks returns only 42 rows, and the three students with no marks row are missing from the list. The clerk's query is:
SELECT s.roll, s.name, m.marks FROM student s, marks m WHERE s.roll = m.roll;
1. Explain the 70.00 versus 65.00 discrepancy. Which figure is defensible for a 'marks obtained' report, and what query would produce 65.00 deliberately?
Read the worked answer
The two figures answer different questions because of how SQL treats NULL.
COUNT(*)counts rows: 42.COUNT(marks)counts non-NULL values: 39, because the three absentees have NULL.AVG(marks)also ignores NULLs, so it computes SUM / 39 = 2730 / 39 = 70.00.
The director divided the same sum by 42, i.e. she scored every absentee as 0: 2730 / 42 = 65.00.
Neither number is arithmetically wrong; the mistake was not stating the convention. 70.00 is the average mark of students who sat the test and is the defensible figure for a 'marks obtained' report, provided the report states that three absentees were excluded. 65.00 is the average per enrolled student with absentees scored zero, which is a different, harsher measure. Publishing 70.00 without the exclusion note is what made it look inflated.
To produce 65.00 deliberately:
SELECT SUM(marks) / COUNT(*) AS avg_all_enrolled,
COUNT(*) - COUNT(marks) AS absentees
FROM marks;
or AVG(COALESCE(marks, 0)). Best practice is to print both figures and the absentee count, so no reader can mistake one convention for the other.
2. Why does the join return only 42 rows, and how should it be rewritten to list all 45 enrolled students?
Read the worked answer
The comma-separated FROM student s, marks m WHERE s.roll = m.roll is an inner join. A row survives only if a matching row exists on both sides, so the three students with no MARKS row at all are eliminated and the result has at most 42 rows.
Note the subtlety: the three rows where MARKS is NULL do appear, because they match on ROLL - they are present but show a blank in the marks column. Only the three students with no MARKS row are lost. This is why the clerk sees exactly 42 rows rather than 39, and it is easy to misdiagnose as a NULL problem when it is really a missing-row problem.
SELECT s.roll, s.name, m.marks
FROM student s
LEFT JOIN marks m ON s.roll = m.roll
ORDER BY s.roll;
A LEFT OUTER JOIN keeps every row of the left table (STUDENT) and fills NULL where no match exists, giving 45 rows: 39 with a real mark, 3 with a NULL mark, and 3 with no MARKS row at all. Ordering by ROLL makes the missing entries easy to find. To distinguish 'absent' from 'never registered for the test', add a flag using CASE WHEN m.roll IS NULL THEN 'NOT REGISTERED' ....
3. A colleague adds AND m.marks > 60 to the WHERE clause of the LEFT JOIN version, intending to show only good marks. Explain what happens, and give a query that instead lists every student while flagging those above 60.
Read the worked answer
In a LEFT JOIN, the ON clause decides which right-hand rows match; the WHERE clause filters the joined result afterwards. NULL > 60 evaluates to UNKNOWN, not TRUE, and WHERE keeps only rows where the condition is TRUE. So every student with a NULL mark and every student with no MARKS row is discarded, and the outer join silently degrades into an inner join. The colleague gets at most 39 rows and believes the LEFT JOIN 'does not work'.
The same trap applies to any condition on the outer-joined table placed in WHERE (m.subject = 'Math', m.marks IS NOT NULL, etc.). If the intention were genuinely to restrict matches, the condition belongs in ON:
FROM student s LEFT JOIN marks m
ON s.roll = m.roll AND m.marks > 60
but that would still drop the non-matching students' marks rather than the students. For 'list everyone, flag the high scorers', keep the join unconditional and flag in the select list:
SELECT s.roll, s.name, m.marks,
CASE WHEN m.marks > 60 THEN 'HIGH'
WHEN m.marks IS NULL THEN 'NO MARK'
ELSE 'LOW' END AS band
FROM student s LEFT JOIN marks m ON s.roll = m.roll
ORDER BY s.roll;
The CASE also handles NULL explicitly, because NULL > 60 would otherwise fall into the ELSE branch and be labelled 'LOW' - which would be a factual error about an absentee.