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.
SolutionIn 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.
- [any other relevant student field information added...like contact info, etc]
- ClassName (VARCHAR(50))
- ClassDescription (TEXT)
- ProfessorID(foreign key to professor table)
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