Practice

0

Please login or register to vote for this query.

(click on this box to dismiss)

Chess

Q&A for serious players and enthusiasts of chess

CREATE table #Courses
([course_id] varchar(20), [course_name] varchar(20))
;
    
INSERT INTO #Courses
    ([course_id], [course_name])
VALUES
    ('csc108', 'Programming'),
    ('mat137', 'Calculus')
;

CREATE table #Students
([student_id] int, [student_name] varchar(20))
;
    
INSERT INTO #Students
    ([student_id], [student_name])
VALUES
    (1, 'John Smith'),
    (2, 'Jane Doe')
;

CREATE table #Marks
([student_id] int, [course_id] varchar(20), [mark] int)
;
    
INSERT INTO #Marks
    ([student_id], [course_id], [mark])
VALUES
    (1, 'mat137', 90),
    (2, 'mat137', 83),
    (1, 'csc108', 75)
;

Select
*
FROM
(
  Select 
    #Courses.course_id,
    #Marks.mark,
    #Marks.student_id,
    #Students.student_name
  FROM #Courses
  INNER JOIN #Marks 
  ON #Marks.course_id = #Courses.course_id
  INNER JOIN #Students
  ON #Students.student_id = #Marks.student_id
  ) as source
  PIVOT 
  (
  MAX(mark)
  FOR source.course_id in ([csc108], [mat137])
  ) p

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results