A good mysql interview exercise, having comprehensive application, mysqlhaving

A good mysql interview exercise, having comprehensive application, mysqlhaving

Write an SQL statement to calculate the average score of two or more failed subjects.

> Two or more subjects must fail.

> Calculate the average score of all the subjects of this candidate, not just the ones that fail

# Create a table:

Create table 'ecs _ mian2' ('user _ name' varchar (20), 'subobject' varchar (20), 'score 'int (4 )); insert into 'ecs _ mian2' ('user _ name', 'subobject', 'score ') values ('zhang san', 'mat', '90 '); insert into 'ecs _ mian2' ('user _ name', 'subobject', 'score ') values ('zhang san', 'China', '50 '); insert into 'ecs _ mian2' ('user _ name', 'subobject', 'score ') values ('zhang san', 'geolocation', '40 '); insert into 'ecs _ mian2' ('user _ name', 'subobject', 'score ') values ('lily', 'China', '55 '); insert into 'ecs _ mian2' ('user _ name', 'subobject', 'score ') values ('lily', 'political', '45 '); insert into 'ecs _ mian2' ('user _ name', 'subobject', 'score ') values ('wang 5', 'political', '30 '); insert into 'ecs _ mian2' ('user _ name', 'subobject', 'score ') values ('zhao liu', 'Physical', '30 '); insert into 'ecs _ mian2' ('user _ name', 'subobject', 'score ') values ('zhao liu', 'Chemical', '50 '); insert into 'ecs _ mian2' ('user _ name', 'subobject', 'score ') values ('zhao liu', 'China', '45 '); insert into 'ecs _ mian2' ('user _ name', 'subobject', 'score ') values ('zhao liu', 'mat', '90 ');

 

Thought 1:

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

# This method: The where clause is used to filter the score <60 subjects. Even if the score is less than score> = 60, the average score is SELECT user_name, AVG (score) AS avg_score, COUNT (*) AS num FROM ecs_mian2 WHERE score <60 group by user_name HAVING num> = 2

This result is incorrect because: "The where clause is used to filter the score <60, even if the score is less than the average score of the score> = 60"

 

Thought 2:

# Find out the average score of all users: SELECT user_name, AVG (score) FROM ecs_mian2 group by user_name; # Find out the courses in which all users fail (1 for failing, 0 for passing) SELECT user_name, score, score <60 FROM ecs_mian2; # SELECT user_name, SUM (score <60) AS bujige FROM ecs_mian2 group by user_name HAVING bujige> = 2 # based on the results of the preceding three statements, we can conclude that the average score of all subjects whose subjects fail> = 2 is SELECT user_name, AVG (score) AS pjf, SUM (score <60) AS bujige FROM ecs_mian2 group by user_name HAVING bujige> = 2

This idea is different from where. It does not filter scores of any subject, so it can calculate the average score.

 

Summary:

1. mysql statements also require flexible thinking

2. Fields in the table can be considered as variables. Of course, variables can be computed, compared, and functions called.