Problem with SQL Server queries

Navigation
1

I have a database of students of a university in which each form corresponds to a particular subject. The DER (Relationship Entity Diagram) is the following:

and I have problems with the following statements to solve:

1) Show the student with the highest grade in each evaluative instance 

select Tipo.Nombre [Instancia], MAX(Evaluacion.Nota) [Nota más alta]
from Evaluacion
inner join Tipo on Tipo.IDTipo = Evaluacion.IDTipo
group by Tipo.Nombre
order by Tipo.Nombre

With this query I show the highest mark of each evaluative instance, but I do not know in which way to show the name of the student.

2) Show the average of disapproved in each instance.

I know that in both you should use subconsulta but I do not know how to implement it since we still do not work with it. Thank you in advance for your help. Greetings

    
asked by Agu Fortini 26.06.2017 в 00:11
source

2 answers

0
SELECT Tipo.Nombre [Instancia], MAX(Evaluacion.Nota) [Nota más alta],
Estudiante.Nombre [Nombre del estudiante]
FROM Evaluacion
INNER JOIN Estudiante on Estudiante.IDEstudiante = Evaluacion.IDEstudiante
INNER JOIN Tipo on Tipo.IDTipo = Evaluacion.IDTipo
group by Tipo.Nombre, Estudiante.Nombre
order by Tipo.Nombre
    
answered by 26.06.2017 / 01:07
source
1
Select es.* , ev.* From Evaluación as ev
Inner join estudiante as es on ev.idestudiante = es.idestudiante;

With this you bring all the data of the evaluations and all the data of the students.

In your query you should add the student table 

select es.nombre, es.apellido ,Tipo.Nombre [Instancia], MAX(Evaluacion.Nota) [Nota más alta] from Evaluacion inner join Tipo on Tipo.IDTipo = Evaluacion.IDTipo
Inner join estudiantes AS es ON evaluación.idestudiante=es.idestudiante 
 group by Tipo.Nombre order by Tipo.Nombre

With this I should bring the names of the students. Try it and notify.

    
answered by 26.06.2017 в 00:29
Modify String Requiring a file makes ajax not work