Sunday, April 6, 2014

Design a Database for storing students’ grades

Problem

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.

Solution

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.
Students:
  • ID(INT)
  • FirstName(VARCHAR(60))
  • LastName(VARCHAR(60))
  • Birthday(DateTime)
  • [any other relevant student field information added...like contact info, etc]
Courses will contain the course name and ID, and will likely contain the course description, professor, etc.
Classes:
  • 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
FROM (
  SELECT top 10 percent Avg(CourseEnrollment.Grade) AS GPA,
    CourseEnrollment.StudentID
  FROM CourseEnrollment
  GROUP BY CourseEnrollment.StudentID
  ORDER BY Avg(CourseEnrollment.Grade)) Honors
INNER JOIN Students ON Honors.StudentID = Students.StudentID

References 
http://tianrunhe.wordpress.com/2012/04/19/database-and-query-for-students-grades/

0 comments:

Post a Comment