duda con consulta sql

[Mr.Blue]

Hola gente tengo un problema con una consulta que quiero hacer y no me sale

tengo las siguientes tablas

alumno
-nombre
-apellido
id_alumno
id_curso

curso
-materia
-id_curso

nota
-nota
id_nota
id_alumno

y lo que quier hacer es listar los alumnos por materia y mostrar  el promedio mayor por materia

Alguna idea como hacerlo

logre hacer esto

Código (sql) [Seleccionar] Expandir

select a.nombre as alumno,avg(n.nota)as promedio
from alumno as a
inner join curso as c on(c.id_curso=a.id_curso)
INNER JOIN nota as n on(n.id_alumno=a.id_alumno)
where a.id_alumno in (select a2.id_alumno from alumno as a2
inner join nota as n
on (n.id_alumno = a2.id_alumno)
group by a2.id_alumno
having avg (n.nota)>7)
group by a.nombre

pero me muestra 1 alumno de 1 materia y su promedio
alguna idea?
gracias

[Beakman]

y lo que quier hacer es listar los alumnos por materia y mostrar  el promedio mayor por materia

Si es que te entendí bien eso solo lo hacés con dos consultas, ¿ por qué ? porque en la primera parte: "mostrar los alumnos por materia" mostras muchos alumnos por materia, en la segunda: "el promedio mayor por materia" es un solo dato por materia, en una tabla no podrías encajar esos datos de esa forma.
Lo que sí podés hacer es algo como: mostrar la cantidad de alumnos por materia y el promedio máximo.