MySQL Learning--mysql query statement synthesis exercise

Practice One:

There is a score table Stu as follows:

Name	Subjects	Results
Tom	Mathematical	90
Tom	Chinese	50
Tom	Geographical	40
John doe	Chinese	55
John doe	Political	45
Harry	Political	30
Harry	Mathematical	70

Try to check the average score of two or more failed students

The answers are as follows

1. Create a data table

CREATE TABLE Stu (
ID int primary KEY auto_increment,
Name Char (3) NOT null default ' ',
Subject char (3) NOT null default ' ',
Score Decimal (3,1) NOT null default 0.0
) CharSet UTF8;

2. Writing data

Set names GBK; #这样中文汉字才可以正常输入
INSERT INTO Stu
(Name,subject,score)
Values
(' Zhang San ', ' mathematics ', 90),
(' Zhang San ', ' language ', 50),
(' Zhang San ', ' geography ', 40),
(' John Doe ', ' language ', 55),
(' John Doe ', ' politics ', 45),
(' Harry ', ' politics ', 30),
(' Harry ', ' mathematics ', 70);

3. Enquiry

#本题的解答-The average score for a student who has failed in two-door (two-door) courses
Select Name,avg (Score), SUM (score<60) as GK from Stu Group by name have gk>=2;

#其它相关查询1-Check the names of students who have failed in two-door (including two-door) courses

Select name from Stu where score<60 group by name have count (subject) >=2;

#其它相关查询2--A little explanation of the subject