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 iss_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;   -- 学号和姓名的分组