sql-test scores for each subject and the number of subjects involved

Check the students ' scores in each subject: (Statistics on the test scores of each subject)

Chart of Accounts:

Id:int primary key self-increment

Course:varchar len:20

Score Table:

ID int PRIMARY Key auto-increment

Name:varchar len:20

Score int

course_id int foreign key associated account table ID

The ID of the foreign key related account table and the course_id of the score table;

SQL as follows

SELECTt_score.name Name,SUM( CaseT_course.course when 'Mathematics'  ThenT_score. ScoreELSE 0 END) asMathematics,SUM( CaseT_course.course when 'language'  ThenT_score. ScoreELSE 0 END) aslanguage fromT_course,t_scoreWHERET_score. course_id=t_course.idGROUP  byName

Query Result:

Check the number of students in each subject: (count the number of exams per course)

There are a few more data on the score table:

Field definition as above

Query SQL as follows:

SELECTt_score.name Name,COUNT( CaseT_course.course when 'language'  ThenT_score. ScoreELSE NULL END) aslanguages,COUNT( CaseT_course.course when 'Mathematics'  ThenT_score. ScoreELSE NULL END) asMathematics fromT_course,t_scoreWHERET_score. course_id=t_course.idGROUP  byName

Query Result:

sql-test scores for each subject and the number of subjects involved