[Careercup] 15.7 Student Grade Student Results

15.7 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%), S Orted by their grade point average.

In a simplified database we have three tables, the students table, the courses table, and the Courseenrollment table as follows:

TABLE Students

+-------------+-------------+|Students|+-------------+-------------+|StudentID| int( One)||Studentname| varchar( -)||Address| varchar( -)|+-------------+-------------+

TABLE Courses

+-------------+-------------+|Courses|+-------------+-------------+|CourseID| int( One)||Coursename| varchar( -)||Professorid| int( One)|+-------------+-------------+

TABLE courseenrollment

+-----------+---------+|Courseenrollment|+-----------+---------+|CourseID| int( One)||StudentID| int( One)||Grade| float   ||Term| int( One)|+-----------+---------+

Use top of SQL Server. The Percent function can be code like this:

-- SQL Server (incorrect Code) SELECT TOP Ten PERCENT AVG  as GPA, Courseenrollment.studentid  from GROUP  by ORDER  by AVG (Courseenrollment.grade);

Since I am using MySQL, I can use the Limit keyword to do, but MySQL limit keyword can only be a constant, cannot be a variable, so can only be a different way of writing, so the code is as follows:

SET @limit =(SELECT 0.1 * COUNT(*) fromcourseenrollment);SELECTGrade from (SELECT *,@rownum:= @rownum + 1  asRank fromCourseenrollment, (SELECT @rownum:= 0) InitORDER  byGradeDESC) dWHERERank<= @limit;

But the above notation does return the top 10% line, but if we have 100 students, the first 15 students are 4.0 GPA, and the above method can only return 10 students, and we need to return the 15 are 4.0 students, so in order to achieve this, we can do this:

--SQL ServerDECLARE @GPACutOff FLOAT;SET @GPACutOff =(SELECT MIN(GPA) as 'Gpamin'  from (SELECT TOP Ten PERCENT AVG(Courseenrollment.grade) asGpa fromcourseenrollmentGROUP  byCourseenrollment.studentidORDER  byGpaDESC) Grades);SELECTStudentname, GPA from (SELECT AVG(Courseenrollment.grade) asGPA, Courseenrollment.studentid fromCourseenrollmentGROUP  byCourseenrollment.studentid having AVG(Courseenrollment.grade)>= @GPACutOff) HonorsINNER JOINStudents onHonors.studentid=Students.studentid;

The above method first defines a Gpacutoff variable, calculates the first 10% GPA, and then iterates through all the GPA in the subsequent code, returning all rows greater than or equal to Gpacutoff. Instead of using MySQL to write code that implements the same functionality, refer to my previous blog Department Top three salaries,Department highest Salary and Second Highest Salary, in a more concise manner as follows:

--MySQLSELECTC.grade fromCourseenrollment CWHERE(SELECT COUNT(DISTINCTGrade) fromcourseenrollmentWHEREGrade>C.grade)<(SELECT 0.1 * COUNT(*) fromCourseenrollment)ORDER  byC.gradeDESC;

Note the underlying assumptions, such as looking at the design above, a potential hypothesis is that each course can only be handed in by one professor, while in some schools, courses may be handed over by multiple professors. However, we need to make some assumptions, and some of the potential incorrect assumptions need to be paid more attention to.

We need to strike a balance between flexibility and complexity, such as designing a system in which a course can be delivered by multiple professors adds flexibility, but also adds complexity, and if our systems apply to any situation, then the system is incredibly complex. So we need to design a relatively flexible system, but still have assumptions and limitations, which not only requires knowledge of database design, but also includes object-oriented design.

Careercup all in one topic summary

[Careercup] 15.7 Student Grade Student Results