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.

My initial thoughts:
The query is given by:

SELECT TOP 10 PERCENT Student.Name, AVG(Grade) AS GPA
FROM Student JOIN StudentTakesCourse
GROUP BY Student.Name
ORDER BY GPA

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. Courses will contain the course name and ID, and will likely contain the course description, professor, etc. CourseEnrollment will pair Students and Courses, and will also contain a field for CourseGrade. We will assume that CourseGrade is an integer.
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

This database could get arbitrarily more complicated if we wanted to add in professor information, billing, etc.