The STUDENT information in a university stored in the relation STUDENT (Name,…
2015
The STUDENT information in a university stored in the relation STUDENT (Name, SEX, Marks, DEPT_Name).
Consider the following SQL Query:
SELECT DEPT_Name from STUDENT where SEX='M' group by DEPT_Name having avg(Marks)>SELECT avg(Marks) from STUDENTIt returns the name of the department for which :
- A.
The average marks of male students is more than the average marks of students in the same department
- B.
The average marks of male students is more than the average marks of students in the university
- C.
The average marks of male students is more than the average marks of male students in the university
- D.
The average marks of students is more than the average marks of male students in the university
Attempted by 225 students.
Show answer & explanation
Correct answer: B
Interpretation of the query: The query filters to male students, groups those male students by department, and keeps departments whose average marks for male students is greater than the overall average marks across all students in the university.
Filter: WHERE SEX='M' restricts the rows to male students only.
Group: GROUP BY DEPT_Name computes aggregates (here AVG(Marks)) separately for male students in each department.
Compare: HAVING avg(Marks) > (SELECT avg(Marks) FROM STUDENT) compares the department-level average of male students to the university-wide average over all students (both sexes).
Result: The query returns the names of departments where the average marks of male students in that department exceed the overall average marks of all students in the university.
Syntax note: The HAVING comparison must include the subquery in parentheses, for example: HAVING avg(Marks) > (SELECT avg(Marks) FROM STUDENT).
A video solution is available for this question — log in and enroll to watch it.