A good MySQL interview exercises, have a comprehensive application

Write an SQL statement to find the average of 2 and 2 failed subjects

> to have 2 or more 2-door disciplines fail

> calculates the average score for all subjects of the examinee, not just the number of failed

#创建表:

Create Table' ecs_mian2 ' ('user_name`varchar(20), ' Subject 'varchar(20), ' score 'int(4)); Insert  into' Ecs_mian2 ' ('user_name', ' subject ', ' score ')Values('Zhang San','Mathematics',' -');Insert  into' Ecs_mian2 ' ('user_name', ' subject ', ' score ')Values('Zhang San','language',' -');Insert  into' Ecs_mian2 ' ('user_name', ' subject ', ' score ')Values('Zhang San','Geography',' +');Insert  into' Ecs_mian2 ' ('user_name', ' subject ', ' score ')Values('John Doe','language',' -');Insert  into' Ecs_mian2 ' ('user_name', ' subject ', ' score ')Values('John Doe','political',' $');Insert  into' Ecs_mian2 ' ('user_name', ' subject ', ' score ')Values('Harry','political',' -');Insert  into' Ecs_mian2 ' ('user_name', ' subject ', ' score ')Values('Zhao Liu','Physical',' -');Insert  into' Ecs_mian2 ' ('user_name', ' subject ', ' score ')Values('Zhao Liu','Chemical',' -');Insert  into' Ecs_mian2 ' ('user_name', ' subject ', ' score ')Values('Zhao Liu','language',' $');Insert  into' Ecs_mian2 ' ('user_name', ' subject ', ' score ')Values('Zhao Liu','Mathematics',' -');

Idea One:

Calculate the number of subjects for score < 60, and then calculate the average score

#这种方法: Using where to filter the score<60 of the subjects, even if not to score>=The average score for 60 of the subjectsSELECT user_name,AVG(score) asAvg_score,COUNT(*) asNum fromEcs_mian2WHEREScore<  - GROUP  by user_name   havingNum>= 2

The result is wrong because: "Where is the score < 60 subject, even if the average score of score >= 60 is not reached"

Idea two:

#查出所有人的平均分SELECT user_name,AVG(score) fromEcs_mian2GROUP  by user_name; #查出所有人不及格的课程 (mark of failure is 1, pass mark is 0)SELECT user_name, score, score<  -  fromecs_mian2; #不及格在2门以上的人SELECT user_name,SUM(Score<  -) asBujige fromEcs_mian2GROUP  by user_name  havingBujige>= 2#综合以上3条语句的结果, can be drawn, failed subjects>=2 per cent of all subjects of the averageSELECT user_name,AVG(score) asPjfSUM(Score<  -) asBujige fromEcs_mian2GROUP  by user_name  havingBujige>= 2

The idea differs from where, without filtering the scores of any subject, so that the average score can be counted

Summary:

1,mysql statements also require a flexible approach

2, the fields in the table can be considered as variables, variables can of course be computed, compared, called functions, etc.

A good MySQL interview exercises, having a comprehensive application