This question is based on the following SQL table MYSTUDENT. ROLLNO NAME SEC…
2022
This question is based on the following SQL table MYSTUDENT.
ROLLNO | NAME | SEC | MARKS |
|---|---|---|---|
80105 | MARIA | A | 83.0 |
80108 | AMAR | B | 44.0 |
80109 | MANPREET | A | 92.0 |
80112 | SAMEER | A | 81.0 |
80115 | AKBAR | B | NULL |
Which of the following command can display the SEC-wise average of the students’ marks?
Answer: C. SELECT SEC, AVG(MARKS) FROM MYSTUDENT GROUP BY SEC; — Concept: In SQL, the GROUP BY clause partitions the rows of a table into groups that share the same value in a specified column; every aggregate function in…
- A.
SELECT SEC, AVG(MARKS) FROM MYSTUDENT ORDER BY SEC;
- B.
SELECT SEC, AVG(MARKS) FROM MYSTUDENT GROUP BY MARKS;
- C.
SELECT SEC, AVG(MARKS) FROM MYSTUDENT GROUP BY SEC;
- D.
SELECT AVG(MARKS) FROM MYSTUDENT ORDER BY SEC;
Attempted by 1571 students.
Show answer & explanation
Correct answer: C
Concept: In SQL, the GROUP BY clause partitions the rows of a table into groups that share the same value in a specified column; every aggregate function in the SELECT list (AVG, SUM, COUNT, etc.) is then computed once per group instead of once for the whole table. Any non-aggregated column listed alongside an aggregate must be the same column named in GROUP BY.
Application: The requirement is a SEC-wise average, so SEC is the grouping column.
Partition the five rows of MYSTUDENT by SEC: Section A holds ROLLNO 80105, 80109, 80112; Section B holds 80108, 80115.
Apply AVG(MARKS) separately within each partition. AVG ignores NULL, so AKBAR's NULL mark is excluded from both the sum and the count for Section B.
Section A average = (83.0 + 92.0 + 81.0) / 3 = 256.0 / 3 = 85.33.
Section B average = 44.0 / 1 = 44.0, since only AMAR's mark is non-NULL.
Pairing the grouping column with the aggregate gives the command: SELECT SEC, AVG(MARKS) FROM MYSTUDENT GROUP BY SEC;
Cross-check: Manually splitting the rows into Section A {83, 92, 81} and Section B {44, NULL} and averaging each group independently reproduces 85.33 and 44.0 exactly — one output row per section. This is what distinguishes GROUP BY SEC from a clause that only reorders rows without partitioning them, from a clause that partitions by the wrong column, or from an aggregate computed with no grouping column at all: none of those alternatives yields one figure per section.