A Fee-Defaulter Report with Missing and Duplicated Rows
A coaching class imports its records into a relational database. The students table has columns rollno, name, and city; the payments table has receiptno, rollno, amount, and paydate. There are 1,200 student rows and 3,400 payment rows. Eighteen students have never paid, so they have no row in payments. Seven old receipts have rollno NULL because they were entered before the student's admission was confirmed. The owner runs:
SELECT s.rollno, s.name, SUM(p.amount) AS total
FROM students s, payments p
WHERE s.rollno = p.rollno
GROUP BY s.rollno, s.name
HAVING SUM(p.amount) < 5000;
The query returns 940 rows. The owner expected every student who has paid less than ₹5,000, including those who have paid nothing. Also, rollno 1012 shows a total of ₹12,000 although only two receipts of ₹3,000 each exist for that student. The students import was done without a uniqueness check, and rollno 1012 appears twice with the same name.
1. Explain why the query returns 940 rows and why the 18 non-payers and the 7 NULL-rollno receipts are absent. State what the correct row set should include.
Read the worked answer
The comma join with WHERE s.rollno = p.rollno is an inner join. A student with no payment row has nothing to match, so all 18 non-payers are eliminated before grouping. They never reach the HAVING clause, which is why “paid nothing” students cannot appear.
A NULL is not equal to any value, including another NULL. The condition s.rollno = p.rollno is unknown for the seven receipts with NULL rollno, so those rows are also eliminated. They cannot be attributed to a student without correcting the data.
So the 940 rows are only students who have at least one non-NULL matching payment and whose summed amount is below ₹5,000. The correct report should list every student once, including the 18 with zero payments, with a total of 0 for them. The exact corrected count cannot be assumed to be 940 + 18, because duplicate student rows can distort totals and change which students fall below the threshold; the duplicate rollno 1012 must be resolved first.
2. Write a corrected query that lists each student once with total paid, including zero for non-payers, and filters total < 5000. Use LEFT JOIN and COALESCE, and explain why the filter belongs in HAVING rather than WHERE.
Read the worked answer
One correct form, after removing duplicate student rows:
SELECT s.rollno, s.name,
COALESCE(SUM(p.amount), 0) AS total
FROM (SELECT DISTINCT rollno, name FROM students) s
LEFT JOIN payments p ON s.rollno = p.rollno
GROUP BY s.rollno, s.name
HAVING COALESCE(SUM(p.amount), 0) < 5000;
LEFT JOIN keeps every student, including those with no payment. For a non-payer, all payment columns are NULL, so SUM(p.amount) is NULL; COALESCE(..., 0) turns that into 0. The DISTINCT subquery prevents the duplicate rollno 1012 from multiplying payment rows.
The condition belongs in HAVING because it applies to the grouped total. Putting WHERE p.amount < 5000 would filter individual payment rows before summing, dropping large receipts and producing a wrong total. Putting WHERE p.rollno IS NULL would do the opposite: it would keep only non-payers. HAVING evaluates after the sum is formed.
3. Diagnose the doubled total for rollno 1012 and give two ways to prevent the problem in future.
Read the worked answer
The students table contains two rows for rollno 1012. When the query joins payments to students, each of the two receipts matches two student rows, so each ₹3,000 receipt is counted twice: 2 × 2 × 3,000 = ₹12,000. The duplication is in the student table, not in payments.
A diagnostic query is:
SELECT rollno, COUNT(*) AS copies
FROM students
GROUP BY rollno
HAVING COUNT(*) > 1;
This should list rollno 1012. To prevent recurrence, first clean the existing data so each rollno appears once, then declare rollno as the primary key (or at least UNIQUE and NOT NULL) in students. Second, enforce the relationship: make payments.rollno a foreign key referencing students.rollno, and decide whether NULL is allowed. If NULL is not allowed, old receipts must be corrected before the constraint is added. A unique key on students.rollno makes the double-count impossible at the database level, rather than relying on every query to use DISTINCT.