About three tables: student information table, score table, curriculum table for the operation of combination query, not much to say, first table information.
Student transcript 
Student Information Table 
Course information sheet 
It's easy for you to operate, attach the code of creating table record, just extract it directly.
CREATE TABLE Member --Student table
(
MID char(10) primary key, --Student number
MName CHAR(50) NOT NULL --Full name
)
GO
CREATE TABLE F --Class Schedule Card
(
FID char(10) primary key, --Course Number
FName CHAR(50) NOT NULL --Course Name
)
GO
CREATE TABLE score --Student transcript
(
SID int identity(1,1) primary key, --Achievement Record Number
FID char(10) foreign key(FID) references F(FID) , --Course Number
MID char(10) foreign key(MID) references Member(MID) , --Student number
Score int NOT NULL --achievement
)
GO
--Insert data into the curriculum--
INSERT INTO F(FID,FName)VALUES('F001','Chinese')
INSERT INTO F(FID,FName)VALUES('F002','Mathematics')
INSERT INTO F(FID,FName)VALUES('F003','English?')
INSERT INTO F(FID,FName)VALUES('F004','History')
--Insert data into student table--
INSERT INTO Member(MID,MName)VALUES('M001','Zhang Sha')
INSERT INTO Member(MID,MName)VALUES('M002','Wang Qiang')
INSERT INTO Member(MID,MName)VALUES('M003','Li San')
INSERT INTO Member(MID,MName)VALUES('M004','Li Si')
INSERT INTO Member(MID,MName)VALUES('M005','satisfied')
INSERT INTO Member(MID,MName)VALUES('M006','usually celadon')
INSERT INTO Member(MID,MName)VALUES('M007','Summer snow')
INSERT INTO Member(MID,MName)VALUES('M008','Lu Lu')
INSERT INTO Member(MID,MName)VALUES('M009','tinkling of jade pendants')
INSERT INTO Member(MID,MName)VALUES('M010','Chanel')
--Data insertion in the transcript--
INSERT INTO Score(FID,MID,Score)VALUES('F001','M001',78)
INSERT INTO Score(FID,MID,Score)VALUES('F002','M001',67)
INSERT INTO Score(FID,MID,Score)VALUES('F003','M001',89)
INSERT INTO Score(FID,MID,Score)VALUES('F004','M001',76)
INSERT INTO Score(FID,MID,Score)VALUES('F001','M002',89)
INSERT INTO Score(FID,MID,Score)VALUES('F002','M002',67)
INSERT INTO Score(FID,MID,Score)VALUES('F003','M002',84)
INSERT INTO Score(FID,MID,Score)VALUES('F004','M002',96)
INSERT INTO Score(FID,MID,Score)VALUES('F001','M003',70)
INSERT INTO Score(FID,MID,Score)VALUES('F002','M003',87)
INSERT INTO Score(FID,MID,Score)VALUES('F003','M003',92)
INSERT INTO Score(FID,MID,Score)VALUES('F004','M003',56)
INSERT INTO Score(FID,MID,Score)VALUES('F001','M004',80)
INSERT INTO Score(FID,MID,Score)VALUES('F002','M004',78)
INSERT INTO Score(FID,MID,Score)VALUES('F003','M004',97)
INSERT INTO Score(FID,MID,Score)VALUES('F004','M004',66)
INSERT INTO Score(FID,MID,Score)VALUES('F001','M006',88)
INSERT INTO Score(FID,MID,Score)VALUES('F002','M006',55)
INSERT INTO Score(FID,MID,Score)VALUES('F003','M006',86)
INSERT INTO Score(FID,MID,Score)VALUES('F004','M006',79)
INSERT INTO Score(FID,MID,Score)VALUES('F002','M007',77)
INSERT INTO Score(FID,MID,Score)VALUES('F003','M008',65)
INSERT INTO Score(FID,MID,Score)VALUES('F004','M007',48)
INSERT INTO Score(FID,MID,Score)VALUES('F004','M009',75)
INSERT INTO Score(FID,MID,Score)VALUES('F002','M009',88)** A frequently needed query operation is to query each student's performance in each subject and divide their average score into grades.
90-100-- A
80-90--B
70-80--C
0-70--D
And in descending order ** like this 
Analysis
Obviously, this is to connect the three tables and take out the fields you want to display. Now we can connect the three tables to see what the result is.
select * from score ,Member,F where score.MID=Member.MID and score.FID=F.FID
The results are as follows 
It can be analyzed that each record is the result of each student's course.
To simplify it, just take the three fields you need
select MName,FName,Score from score ,Member,F where score.MID=Member.MID and score.FID=F.FID
Need a name, four subject names and four grades to correspond, so here we use the keyword case.
select MName AS Name of student,
//Language=sum(case FName WHEN 'Chinese' then score end),
//English=sum(case FName WHEN 'English?' then score end),
//Mathematics=sum(case FName WHEN 'Mathematics' then score end),
//History=sum(case FName WHEN 'History' then score end))
from score,Member,F where score.MID=Member.MID AND score.FID=F.FID
GROUP BY MNameIn this way, the first step is successfully completed, and each student's test scores are corresponded.
Interpretation of case:
When aggregating MName, each time FName is a language, the score recorded in this article is placed under the language, and so on, the score is separated according to the corresponding position. 
The next step is to find the average score and evaluate the average score.
I'm going to use case again when I think about it.
select MName AS Name of student,
//Language=sum(case FName WHEN 'Chinese' then score end),
//English=sum(case FName WHEN 'English?' then score end),
//Mathematics=sum(case FName WHEN 'Mathematics' then score end),
//History=sum(case FName WHEN 'History' then score end),
**case
when AVG(score)>=90 then 'A'
when 80<AVG(score) AND AVG(score)<=90 then 'B'
when 70<AVG(score) and AVG(score)<=80 then 'C'
else 'D'
END as evaluate**
from score,Member,F where score.MID=Member.MID AND score.FID=F.FID
GROUP BY MName
ORDER BY AVG(score) desc
Here we can find a problem that some students did not participate in the exam, so the results are empty, so his average score is the average score of two courses, which is obviously unfair to other students, so the average score of four courses is needed. We can go to sum ()/4 to solve this problem, or we can only take the average score of the students who took four courses to evaluate.
Another point is that the NULL values in the records are really ugly and need to be dealt with. Here, the main function of the building is isnull().
select b.MName AS Name of student,
//Language = isnull(sum(case FName WHEN 'Chinese' then score end),'0'),
//English = isnull(sum(case FName WHEN 'English?' then score end),'0'),
//Mathematics = isnull(sum(case FName WHEN 'Mathematics' then score end),'0'),
//History = isnull(sum(case FName WHEN 'History' then score end),'0'),
//Total score= sum(score),
//Average score=SUM(score)/4,
COUNT(b.MID) as Participate in exam subjects,
case
when SUM(score)/4>=90 then 'A'
when 80<=SUM(score)/4 AND SUM(score)/4<90 then 'B'
when 70<=SUM(score)/4 and SUM(score)/4<80 then 'C'
else 'D'
END as evaluate
from score a LEFT join Member b on a.MID=b.MID
left join F on F.FID=A.FID
group by b.MName
having COUNT(b.MID)>=4
order by Average desc
So the result is perfect!
If you don't understand, or where you criticize and correct me, you are welcome to leave a message below. Thank you very much!
PS: Main Content Extracted from Red-Black Alliance( http://www.2cto.com/database/201409/330246.html)