MySQL50-11-41-45 items

Questions 41 to 45 are based on the following information:

  • Table self-join query comparison information
  • Find the top 2
  • Group sorting + Having filtering

Five questions are

  • Query student id, course id, student grade of students with same grades in different courses
  • The top two in every subject
  • Count the number of students enrolled in each course (only for courses with more than 5 students). Ask to output the course number and the number of electives, query results according to the number of descending order, if the number of the same, according to the course number ascending order
  • Retrieves the student numbers of students enrolled in at least two courses
  • Query information about students who have taken all courses

Topic 41

The subject requirements

Query student id, course id, student grade of students with same grades in different courses

The analysis process

  • Course scores: Score, S_score
  • Student ID: Score, S_id
  • Course id: Score, C_ID

Three fields are in one table at the same time, so we can find out through the self-connection of a table Score

SQL implementation

select
	a.s_id
	,a.c_id
	,a.s_score
from Score a
join Score b
on a.c_id ! = b.c_id
and a.s_score = b.s_score
and a.s_id ! = b.s_id;
Copy the code

We also need to go to the student number:

select
	distinct a.s_id
	,a.c_id
	,a.s_score
from Score a
join Score b
on a.c_id ! = b.c_id
and a.s_score = b.s_score
and a.s_id ! = b.s_id;
Copy the code

Let’s look at the original data and see if it fits:

The subject of 42

The subject requirements

Query the top two students with the best performance in each course

The analysis process

Find the top two students in each class

  • Results: the Score
  • Subject: Course

SQL implementation

Your own way

Also need to be well optimized 😭

-- Find the top two students in Chinese first

select 
	c.c_id
	,sc.s_id
	,sc.s_score	
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = 'Chinese'   -- Change to math and English to work out the corresponding information
order by sc.s_score desc
limit 2;
Copy the code

The answer can be obtained by stitching the information of the three disciplines:

-- Final script

(select 
	c.c_id
	,sc.s_id
	,sc.s_score	
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = 'Chinese'   
order by sc.s_score desc
limit 2)

union

(select 
	c.c_id
	,sc.s_id
	,sc.s_score	
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = 'mathematics'   
order by sc.s_score desc
limit 2)

union

(select 
	c.c_id
	,sc.s_id
	,sc.s_score	
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = 'English' 
order by sc.s_score desc
limit 2)
Copy the code

Reference method (good method)

How to solve the problem of sorting the top several 🐂🍺🚗 too cow

select 
	a.c_id
	,a.s_id
	,a.s_score
from Score a
where (select count(1)   -- count(1) Similar to count(*) : the number of large scores in table B
       from Score b 
       where b.c_id=a.c_id   -- Same curriculum
       and b.s_score > = a.s_score) < = 2   The former two
order by a.c_id;
Copy the code

First let’s take a look at the real data, we will use the 01 lesson to explain the above code:

There are only two cases where count(1)<=2

Also need a good understanding of 😭

The title 43

The subject requirements

Count the number of students enrolled in each course (only for courses with more than 5 students). Ask to output the course number and the number of electives, query results according to the number of descending order, if the number of the same, according to the course number ascending order

The analysis process

Course number: Score, C_ID

Student: Score, s_id

SQL implementation

select 
	c_id
	,count(s_score) num
from Score 
group by c_id
having num > 5
order by num desc, c_id;
Copy the code

Topic 44

The subject requirements

Retrieves the student numbers of students enrolled in at least two courses

The analysis process

Courses: Score, C_ID

Student ID: Score, s_id

SQL implementation

The results show that all requirements are met

select  
	s_id
	,count(*) num
from Score 
group by s_id
having num > = 2;   
Copy the code

The title of 45

The subject requirements

Query information about students who have taken all courses

The analysis process

  1. First let’s look at the total number of courses in the Course table (num).
  2. Then find the information of the student whose course is NUM in the Score table

SQL implementation

Your own way

1, total number of courses num

select count(*) from Course;   B: Three in all
Copy the code

2. Count the number of courses of each person from the Score table, and meet the student information of 3

select 
	s_id
	,count(c_id) num   -- Number of courses
from Score
group by s_id
having num in (select count(*)  
               from Course); Meet all courses
Copy the code

3. We can find out the student information in the above results

select 
	s.*
	,count(c_id) num   -- Number of courses
from Score sc
join Student s
on sc.s_id = s.s_id
group by s.s_id
having num in (select count(*)  
               from Course); Meet all courses
Copy the code

The reference method
select *   -- 3. Student information corresponding to s_id
from Student 
where s_id in(select s_id -- s_id of the maximum number of courses
              from Score 
              group by s_id 
              having count(*)=(select count(*) from Course)  -- 1. Total number of courses
             )
Copy the code