SQL group by AVG mark of JOIN of 3 tables

Question

I have 3 tables:

Student:

id: int AUTO_INCREMENT
first_name: char(30)
last_name: char(30)

Marks:

id: int AUTO_INCREMENT
mark: int

Subject:

id: int AUTO_INCREMENT
name: char(30)
stud_id int
mark_id int

Where id in all columns is Primary key, and stud_id int, mark_id int from Subject table are secondary keys.

How is it possible to get the first name, last name and average mark of all students who have average mark above 8.5 (out of 10) at maths?

Here I started to write the query, but I don't know how to use the AVG function, alias and Group By function

SELECT student.first, student.last, ??? as avg_mark 
FROM subject
JOIN student on student.ID = subject.stud_id
JOIN marks on marks.id = subject.mark_id
Where ???

Answer 1

Both mysql and sql-server have an avg function (AFAIK, it's defined by the ANSI SQL standard). Note that it's an aggregate function though, so you can't use the where clause to apply a condition to it - you need to use the having clause instead:

SELECT   student.first_name, student.last_name, AVG(mark) avg_mark 
FROM     subject
JOIN     student on student.ID = subject.stud_id
JOIN     marks on marks.id = subject.mark_id
GROUP BY student.first, student.last
HAVING   AVG(mark) > 8.5

Answer 2

Use AVG with the HAVING clause.

Use the alias in the FROM clause next to each table name.

GROUP BY comes last