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