queries with sql server 2016

Navigation
3

the statement is following: get the name and ID of the students with the highest grade in the subject with the code 46322

The structure of the database is this: 

alumnos (dni pk, nombre, dirección, localidad)
asignaturas (coda pk, nombreas, curso,dpt)
expedientes ((dni,coda)pk, y fk, convocatoria, nota)

The query I have raised it like this: 

select nombre, nombrea, convocatoria
from alumnos inner join expedientes on (expedientes.dni=alumnos.dni) inner join asignaturas on (asignaturas.coda=expedientes.coda)
where  exists (select max(nota)
              from expedientes
              where (coda=47322 ) and (convocatoria like 'junio') );

However, it returns incorrect values. Thanks for the help.

    
asked by ras212 19.06.2016 в 19:37
source

1 answer

2

The query should be: 

Select a.nombre, a.dni, max(e.nota) as Maxima
from alumnos a 
Inner join expedientes e on e.dni=a.dni
where e.coda=47322 
group by a.nombre, a.dni
    
answered by 19.06.2016 / 22:40
source
PHP with Mongo, questions with questions Simulate several clients in nodejs