A table T1 in a relational database has the following rows and columns: The…
2024
A table T1 in a relational database has the following rows and columns:
The following sequence of SQL statements was successfully executed on table T1:
UPDATE T1 SET marks = marks + 5;
SELECT AVG(marks) FROM T1;What is the output of the SELECT statement?
- A.
18.75
- B.
20
- C.
25
- D.
Null
Attempted by 2 students.
Show answer & explanation
Correct answer: C
Concept: Every SQL aggregate function except COUNT(*) — SUM(), AVG(), MAX(), MIN(), COUNT(column) — operates only on the non-NULL values of the column it is given; any NULL rows are excluded from both the running total and the count used to compute the result. Separately, arithmetic performed on a NULL value (NULL + 5, NULL * 2, etc.) always evaluates to NULL, never to a number.
Application: Table T1 starts with marks 10, 20, 30 and NULL for roll numbers 1-4. Working through the two statements in order:
UPDATE T1 SET marks = marks + 5 adds 5 to every row's marks. The three non-NULL rows become 15, 25 and 35.
The fourth row's marks stays NULL, because NULL + 5 evaluates to NULL rather than to a number.
SELECT AVG(marks) FROM T1 now runs over a column holding 15, 25, 35 and NULL. Per the concept above, AVG() drops the NULL row from both the total and the count, so it averages only the three real values.
AVG(marks) = (15 + 25 + 35) ÷ 3 = 75 ÷ 3 = 25.
Cross-check: AVG(column) is defined as SUM(column) ÷ COUNT(column), and COUNT(column) — unlike COUNT(*) — also skips NULLs. Computing it that way independently gives the same 75 ÷ 3 = 25, confirming the value above without any assumption beyond the standard NULL-skipping rule for aggregates.
The output of the SELECT statement is 25.