Choose the correct option regarding the following query
WITH max_marks (VALUE) AS
(SELECT MAX(marks)
FROM student)
SELECT studentID
FROM student,max_marks
WHERE student.marks = max_marks.value;
a)
The query is syntactically wrong
b)
The query gives the studentID of the student with the maximum marks
c)
The query gives the maximum marks amongst all the students
d)
The query gives all the studentID values except the student with the maximum marks
Correct answer is option 'B'. Can you explain this answer?

Question

Choose the correct option regarding the following query

WITH max_marks (VALUE) AS
(SELECT MAX(marks)
FROM student)
SELECT studentID
FROM student,max_marks
WHERE student.marks = max_marks.value;

a)
The query is syntactically wrong
b)
The query gives the studentID of the student with the maximum marks
c)
The query gives the maximum marks amongst all the students
d)
The query gives all the studentID values except the student with the maximum marks

Correct answer is option 'B'. Can you explain this answer?

Answer

Explanation:

The given query first creates a common table expression (CTE) named max_marks which selects the maximum marks from the student table.

Then it selects the studentID from the student table and max_marks CTE where student's marks are equal to the maximum marks obtained by any student.

Therefore, the query returns the studentID of the student with the maximum marks.

Option 'c' is incorrect as the query only selects the studentID, not the maximum marks.

Option 'd' is incorrect as the query specifically selects the studentID with the maximum marks.

Therefore, the correct option is B.

Answer

The query gives the studentID with the maximum marks because the max(marks) aggregation gives the maximum of the marks.