How to design the MySQL table structure to support the test score statistics of the online examination system?

How to design the MySQL table structure to support the test score statistics of the online examination system?

Introduction
Online examination system is one of the important components of modern education. In order to conduct statistics and analysis on students' test scores, it is necessary to design a suitable database table structure to store test information. This article will introduce how to design the MySQL table structure to support the test score statistics of the online examination system, and provide specific code examples.

Table structure design
When designing the MySQL table structure, factors such as students, exams, test questions, and scores need to be taken into consideration. The following is a simple table structure design example.

Students table (students)

##nameVARCHARStudent NamegradeVARCHARStudent GradeclassVARCHARClassCreation timeDATETIMEStudent information creation time

Field name	Data type	Description
id	INT	Student ID

Examination form (exams)

Field nameData typeDescriptionidINTExam IDnameVARCHARExam nametimeDATETIMEExam timeSubjectVARCHARExamination subjectsCreation timeDATETIMEExamination information creation timeQuestions (questions)

Field nameData typeDescriptionidINTExam IDexam_idINT Exam IDcontentTEXTQuestion contentAnswerVARCHARCorrect answerCreation timeDATETIMETest question information creation timeScores

##Field nameidstudent_idexam_id scoreCreation timeSample code

Data type	Description
INT	Grade ID
INT	Student ID
INT	Exam ID
FLOAT	Score
DATETIME	Score information creation time

The following is a sample code for querying using the above table structure.

Query all the scores of a student

1. SELECT e.name AS exam_name, s.score
   FROM scores AS s
   JOIN exams AS e ON s.exam_id = e.id
   WHERE s.student_id = <student_id>;

Query the average score of a certain exam

2. SELECT AVG(score) AS average_score
   FROM scores AS s
   WHERE s.exam_id = <exam_id>;

Query List of students who failed a certain exam

3. SELECT st.name AS student_name, s.score
   FROM scores AS s
   JOIN students AS st ON s.student_id = st.id
   WHERE s.exam_id = <exam_id>
   AND s.score < <passing_score>;

Query the number of students in each score range of a certain exam

4. SELECT COUNT(*) AS count,
   CASE
     WHEN score >= 90 THEN 'A'
     WHEN score >= 80 THEN 'B'
     WHEN score >= 70 THEN 'C'
     WHEN score >= 60 THEN 'D'
     ELSE 'F'
   END AS grade
   FROM scores
   WHERE exam_id = <exam_id>
   GROUP BY grade;

Summary

Design MySQL table structure to support Examination score statistics for online examination systems is an important and complex task. By rationally designing the table structure and using query statements flexibly, statistics and analysis of various test scores can be easily performed. The above is a simple example, which can be appropriately adjusted and expanded according to actual needs.

The above is the detailed content of How to design the MySQL table structure to support the test score statistics of the online examination system?. For more information, please follow other related articles on the PHP Chinese website!