MySQL Classic 50 Questions-Questions 26 to 30

MySQL50-8-questions 26-30

This article introduces topics 26-30, the main knowledge points involved are:

  • Count the number of people after grouping
  • Fuzzy matching
  • Self-join of the same table
  • The use of having

The 5 questions are:

  • Query the number of students selected for each course
  • Find out the student numbers and names of all students with only two courses
  • Query the number of boys and girls
  • Query student information with words in the name
  • Query the list of students with the same name and the same sex and count the number of students with the same name

Question 26

Topic requirements

Query the number of students selected for each course

Analysis process

The question I want to solve is how many people take each course. It’s quite simple.

SQL implementation

select 
	c.c_id
	,c.c_name
	,count(s.s_id)
from Course c
join Score s
on c.c_id = s.c_id
group by c.c_id;

Question 27

Topic requirements

Find out the student numbers and names of all students with only two courses

Analysis process

Courses: Score, the number of courses is determined by c_id

Student: Student

SQL implementation

The having condition is executed after grouping, and the where statement is executed before grouping

select 
	s.s_id
	,s.s_name
from Student s
join Score sc
on s.s_id = sc.s_id
group by 1,2
having count(sc.c_id) = 2;

Question 28

Topic requirements

Query the number of boys and girls

Analysis process

Direct statistical Studentgender table s_sexnumber

SQL implementation

First look at the data: both males and females are 4

--  自己的方法
select 
	count(case when s_sex='男' then 1 end) as '男'
	,count(case when s_sex='女' then 1 end) as '女'
from Student;

-- 参考方法
select 
	s_sex
	,count(s_sex) as `人数`
from Student
group by s_sex;

Question 29

Topic requirements

Query student information with words in the name

Analysis process

With wind in the name, we use fuzzy matching % ; the table used isStudent

SQL implementation

Let’s first see which classmates have wind in their names

-- 模糊匹配:我们在两边都加上了%,考虑的是姓或者名字含有风,虽然风姓很少见
select * from Student where s_name like "%风%";

Question 30

Topic requirements

Query the list of students with the same name and the same sex and count the number of students with the same name

Analysis process

Find the number of students with the same name and the same sex from the Student table

SQL implementation

1. First look at the student information of the class

There are no students with the same name in the existing data, but when the class size increases, it is very likely that students with the same name will appear in the class

2. Suppose there are students with the same name and the same sex

select 
	a.s_name
	,a.s_sex
	,count(*)
from Student a  -- 同一个表的自连接
join Student b
on a.s_id != b.s_id   -- 连接的时候不能是同一个人:学号保证,每个人的学号是唯一的,其他字段都可能重复
and a.s_sex = b.s_sex  -- 性别相同
and a.s_name = b.s_name -- 名字相同
group by 1,2;