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)
);

You would need to create StudentScores -> StudentID,CourseID,Score — Nathan_Sav, Jul 15 '21 at 16:16
not tested : `select c.courseid,q.studentid from courses as c left join (select sc.courseid,sc.studentid,sc.score,row_number() over (partition by courseid order by score desc) as r from studentscores) as q on c.courseid=q.courseid where q.r<=5` You could add the score into the university table instead? — Nathan_Sav, Jul 15 '21 at 16:23
@Nathan_Sav Hi, first thanks for reply. And you right, It would be better designing — mike colis, Jul 16 '21 at 18:03

score 0 · Accepted Answer · answered Jul 15 '21 at 17:24

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

I was really wondering to add another column of scores. Thank you so much!! — mike colis, Jul 16 '21 at 18:07

How can I query junction table

1 Answers1