How can I query junction table

Question

Hi im tyring to query junction table, but i cant figure it out. how can i find the five students with the highest score in a particular cours

 CREATE TABLE Students (
 StudentID int NOT NULL PRIMARY KEY,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255) NOT NULL,
 StudentNum int NOT NULL,
);
CREATE TABLE Courses (
 CourseID int NOT NULL PRIMARY KEY,
 CourseName varchar(255) NOT NULL,
 GPA int(255) NOT NULL
);

CREATE TABLE University (
StudentID int NOT NULL,
CourseID int NOT NULL,
CONSTRAINT PK_University PRIMARY KEY
(
StudentID,
CourseID
),
FOREIGN KEY (StudentID) REFERENCES Students (StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses (CourseID)
);

Answer 1

I added the score field to the University table and change University name to Students_Courses.

First change the University table to :

CREATE TABLE Students_Courses (
 StudentID int NOT NULL,
 CourseID int NOT NULL,
 Score float,
 CONSTRAINT PK_Students_Courses PRIMARY KEY
 (
 StudentID,
 CourseID
 ),
 FOREIGN KEY (StudentID) REFERENCES Students (StudentID),
 FOREIGN KEY (CourseID) REFERENCES Courses (CourseID)
);

Now you can join the tables and sort by score and find 5 of the highest scores.

you can use

select top 5 s.StudentID,s.FirstName,s.LastName,sc.Score,c.CourseName
from Students_Courses sc join Students s on sc.StudentID = s.StudentID
join Courses c on sc.CourseID = c.CourseID
where sc.CourseID = 1
order by Score desc

OR

select top 5 s.StudentID,s.FirstName,s.LastName,sc.Score 
from Students_Courses sc join Students s on sc.StudentID = s.StudentID
where sc.CourseID = 1
order by Score desc

OR use window function

select StudentID,FirstName,LastName,Score 
from
  (select s.StudentID,s.FirstName,s.LastName,sc.Score,ROW_NUMBER() over(order by sc.Score desc) as seq
  from Students_Courses sc join Students s on sc.StudentID = s.StudentID
  where sc.CourseID = 1) T
where seq <= 5

demo in dbfiddle