# MySQL Classic 50 Questions-Questions 31-35

### MySQL50-9-Questions 31-35

This article introduces topics 31-35, and the main knowledge points involved are:

• Fuzzy matching
• Specify multiple sorting methods at the same time
• Multi-table join query

The 5 questions are:

• Query information about students born in 1990
• Query the average score of each course, and the results are sorted in descending order of the average score; when the average score is the same, they are sorted in ascending order by the course number c_id
• Query the student ID, name and average score of all students whose average score is greater than or equal to 85
• Query the names and scores of students whose course name is mathematics and whose score is lower than 60
• Check the courses and scores of all students

### Question 31

#### Topic requirements

Query information about students born in 1990

#### Analysis process

For this topic, we still use fuzzy matching, and the field used is`s_birth`

#### SQL implementation

``````select *
from Student
where s_birth like '1990%';   -- 模糊匹配
``````

### Question 32

#### Topic requirements

Query the average score of each course, and the results are sorted in descending order of the average score; when the average score is the same, they are sorted in ascending order by the course number c_id

#### Analysis process

Course: Score/Course

Score: Score

Grouping and sorting by the average grade of each course

#### SQL implementation

``````-- 自己的方法
select
c_id
,round(avg(s_score),2) avg_score
from Score
group by 1
order by 2 desc, c_id;  -- 指定字段和排序方法
``````

If you want to bring the name of the course, you need to link with the Course table

``````-- 自己的方法
select
c.c_id
,c.c_name
,round(avg(sc.s_score),2) avg_score
from Score sc
join Course c
on sc.c_id = c.c_id
group by 1,2
order by 3 desc, c.c_id;  -- 指定字段和排序方法
``````

### Question 33

#### Topic requirements

Query the student ID, name and average score of all students whose average score is greater than or equal to 85

#### Analysis process

• According to the average score of the students, select those with a score greater than 85
• Link with the student information table to find out specific information

#### SQL implementation

``````-- 自己的方法
select
sc.s_id
,s.s_name
,round(avg(sc.s_score),2) avg_score
from Score sc
join Student s
on sc.s_id = s.s_id
group by sc.s_id,s.s_name
having avg_score >= 85;
``````

### Question 34

#### Topic requirements

Query the names and scores of students whose course name is mathematics and whose score is lower than 60

#### Analysis process

1. First find out the student ID of students with a score of less than 60 in mathematics

2. Connect with the Student table to query the name

#### SQL implementation

``````select
s.s_name
,sc.s_score
from Score sc   -- 成绩表
join Student s  -- 学生信息表
on sc.s_id = s.s_id
join Course c  -- 课程表，指定数学
on sc.c_id = c.c_id
where c.c_name = '数学'
and sc.s_score < 60;  -- 指定成绩
``````

Looking at the real data, there is indeed only one person satisfied

### Question 35

#### Topic requirements

Check the courses and scores of all students

#### Analysis process

1. Course: Course
2. Score: Score
3. Student name: Student

Through the main table of Score, the two fields and the other two tables can be connected to query.

#### SQL implementation

``````select
s.s_id
,s.s_name
,sum(case c.c_name when '语文' then sc.s_score else 0 end) as '语文'  -- 语文分数
,sum(case c.c_name when '数学' then sc.s_score else 0 end) as '数学'
,sum(case c.c_name when '英语' then sc.s_score else 0 end) as '英语'
,sum(sc.s_score) as '总分'  -- 每个人的总分
from Student s
left join Score sc
on s.s_id = sc.s_id
left join Course c
on sc.c_id = c.c_id
group by s.s_id, s.s_name;   -- 学号和姓名的分组
``````