using Max and Avg function together in SQL

Question

I have the following four tables containing student information, lecture information, attending lectures information and exams.

using the SELECT Student_id, AVG(marks) as average FROM exams GROUP BY Student_id; I get a list of average marks obtained by each student.

How do i combine it with MAX function to get the ID, name, and average of students who has the highest average in the list?

students

student_id
student_name

lectures

lecture_id
lecture_name
ECTS

attending_lectures

id
student_id
lecture_id

exams

id
Student_id
Lecture_id
Marks

Answer 1

You may use such a SQL with an ORDER BY ... DESC clause to get MAX

For MySQL :

SELECT e.Student_id, s.student_name, AVG(marks) as "average" 
  FROM exams e
  LEFT JOIN students s ON e.Student_id = s.Student_id
 GROUP BY e.Student_id, s.student_name
 ORDER BY "average" DESC
 LIMIT 1;

For Oracle :

SELECT * FROM
(
 SELECT q.*, row_number() over (order by "average" desc) as rn 
   FROM
  (
   SELECT e.Student_id, s.student_name, AVG(marks) as "average"           
     FROM exams e
     LEFT JOIN students s ON e.Student_id = s.Student_id
    GROUP BY e.Student_id, s.student_name
   ) q
 )
 WHERE rn = 1;