Public account: You and the cabin by: Peter Editor: Peter

Hello, I’m Peter

MySQL50-9-31-35 items

Directions: For this part, you are allowed 30 minutes to write a passage on the topic 31 to 35.

  • Fuzzy matching
  • Specify multiple sorts at the same time
  • Join queries for multiple tables

The five topics are:

  • Query information about students born in 1990
  • Query the average grade of each course, the results are in descending order of average grade; With the same average score, they will be ranked in ascending order of course number C_ID
  • Query student id, name, and gpa of all students whose gpa is 85 or greater
  • Select * from student whose course name is math and whose score is less than 60
  • Query all students’ courses and grades

The title 31

The subject requirements

Query information about students born in 1990

The analysis process

Again, we’re going to do fuzzy matching, using the field s_birth

SQL implementation

select *
from Student
where s_birth like '1990%';   -- Fuzzy matching
Copy the code

The title of 32

The subject requirements

Query the average grade of each course, the results are in descending order of average grade; With the same average score, they will be ranked in ascending order of course number C_ID

The analysis process

Courses: Score/Course

Results: the Score

Group and rank by gpa for each course

SQL implementation

-- Your own way
select 
	c_id
	,round(avg(s_score),2) avg_score
from Score
group by 1
order by 2 desc, c_id;  -- Specify fields and sorting methods
Copy the code

If you want to bring the name of the Course, you need to link it with the Course sheet

-- Your own way
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;  -- Specify fields and sorting methods
Copy the code

Questions 33

The subject requirements

Query student id, name, and gpa of all students whose gpa is 85 or greater

The analysis process

  • According to the average score of the students, then choose the person with more than 85 points
  • Link with the student information sheet to find out specific information

SQL implementation

-- Your own way
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;
Copy the code

The subject of 34

The subject requirements

Select * from student whose course name is math and whose score is less than 60

The analysis process

1, Find the student ID from the Score and Course table

Select * from Student where name = ‘Student’

SQL implementation

select 
	s.s_name
	,sc.s_score
from Score sc   - record table
join Student s  -- Student information sheet
on sc.s_id = s.s_id
join Course c  -- Class schedule, assigned math
on sc.c_id = c.c_id
where c.c_name = 'mathematics'
and sc.s_score < 60;  -- Assigned grades
Copy the code

If you look at the real data, only one person is satisfied

The title 35

The subject requirements

Query all students’ courses and grades

The analysis process

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

From the main table of Score, the two fields can be joined with the other two tables

SQL implementation

select 
	s.s_id
	,s.s_name
	,sum(case c.c_name when 'Chinese' then sc.s_score else 0 end) as 'Chinese'  -- Chinese score
	,sum(case c.c_name when 'mathematics' then sc.s_score else 0 end) as 'mathematics'
	,sum(case c.c_name when 'English' then sc.s_score else 0 end) as 'English'
	,sum(sc.s_score) as 'total'  -- The total score of each person
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;   -- Grouping of student number and name
Copy the code