Sunday, April 6, 2014

Design a Database for storing students’ grades


Imagine a simple database storing information for students’ grades. Design what this database might look like, and provide a SQL query to return a list of the honor roll students (top 10%), sorted by their grade point average.


In a simplistic database, we’ll have at least these three objects: Students, Courses, and courseEnrollment. Students will have at least the student name and ID, and will likely have other personal information.
  • ID(INT)
  • FirstName(VARCHAR(60))
  • LastName(VARCHAR(60))
  • Birthday(DateTime)
  • [any other relevant student field information contact info, etc]
Courses will contain the course name and ID, and will likely contain the course description, professor, etc.
  • ID(INT)
  • ClassName (VARCHAR(50))
  • ClassDescription (TEXT)
  • ProfessorID(foreign key to professor table)
CourseEnrollment will pair Students and Courses, and will also contain a field for CourseGrade. We will assume that CourseGrade is an integer.
CourseEnrollment :
  • ID(INT)
  • StudentID(INT)
  • ClassID(INT)
  • Grade(Int)

Our SQL query to get the list of honor roll students might look like this:
SELECT StudentName, GPA
  SELECT top 10 percent Avg(CourseEnrollment.Grade) AS GPA,
  FROM CourseEnrollment
  GROUP BY CourseEnrollment.StudentID
  ORDER BY Avg(CourseEnrollment.Grade)) Honors
INNER JOIN Students ON Honors.StudentID = Students.StudentID



Post a Comment