WITH sc AS (

SELECT s.StudentNo
     , stuff((SELECT ',' + c.CourseName
              FROM
                  Course c
                  JOIN Score s2
                      ON c.CourseNo = s2.CourseNo
              WHERE
                  s2.StudentNo = s.StudentNo
              FOR XML
                  PATH ('')), 1, 1, '') AS Courses
FROM
    Score s
GROUP BY
    s.StudentNo
)

SELECT c.ClassName
     , s.StudentName
     , sc.Courses
FROM
    Student s
    JOIN Class c
        ON s.ClassNo = c.ClassNo
    LEFT JOIN sc
        ON sc.StudentNo = s.StudentNo

WITH Rnk AS
(
SELECT StudentNo, Score2Time, CourseNo, Score2, row_number() OVER (PARTITION BY CourseNo ORDER BY Score2 DESC) as Rnk
FROM Score
)

SELECT Course.CourseName, rnk.Score2Time, rnk.Score2, Student.*, Rnk.Rnk
FROM Rnk JOIN Student on rnk.StudentNo = Student.StudentNo
JOIN Course on rnk.CourseNo = Course.CourseNo
WHERE Rnk.Rnk < 4

/****** Script for SelectTopNRows command from SSMS  ******/

WITH fellowStudents as
(
SELECT *
     , (SELECT count(*)
        FROM
            (SELECT [CourseNo]
                  , [Score2]
             FROM
                 [StudentInfo].[dbo].[Score]
             WHERE
                 StudentNo = 'S00001'
             INTERSECT
             SELECT CourseNo
                  , [Score2]
             FROM
                 [StudentInfo].[dbo].[Score]
             WHERE
                 Score.StudentNo = s.StudentNo) base) AS SameCourseNo

FROM
    Student s
)

SELECT *
FROM
    fellowStudents
WHERE
    SameCourseNo >= 2