sqljunction

How can I query junction table


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

Solution

  • 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