Official account: Special House
Author: Peter
Editor: Peter

Hello, I'm Peter~

Continue with questions 11-15 of MySQL classic 50. The specific questions are as follows:

• Query the information of students who have not completed all courses
• Query the information of students who have at least one course and the same student with student number 01
• Query the information of students who are exactly the same as the courses studied by 01
• Check the names of students who have not taken any courses taught by Mr. Zhang San
• Check the student number, name and average score of students who fail two or more courses

Topic 11

Topic demand

Query the information of students who have not completed all courses

Analysis process

The topic mainly involves: Course table and Student table

SQL implementation

The idea of realizing this method is to group and count the number of courses according to the student number, and then exclude the students corresponding to the student number

-- Own method
select *   -- Results obtained after excluding student number
from Student 
where s_id not in (
  select s_id 
  from(select 
         s_id
         ,count(s_id) as number  -- 3.The student number where the maximum number of courses is located needs to be excluded
       from Score 
       group by s_id) s  -- Alias
  where number=(select max(number)   -- 2.Ensure the maximum number of courses  
                from(select 
                     s_id, 
                     count(s_id) as number  -- 1.Statistics of student number and number of courses (i.e. several courses)
                   from Score 
                   group by s_id)t));  -- alias

At first, my method did not use the Course table in the maximum number of courses, resulting in the result of using more than one temporary table. Now I use the statistical value of the Course table, that is, the result of 3 courses, as the total number of courses:

select s.* 
from Student s
where s_id not in (
  select s_id 
  from Score s1
  group by s_id 
  having count(*) = (select count(*) from Course)
);

-- Method 2: having

select s.* 
from Student s  -- Student list
left join Score s1  -- Transcript
on s1.s_id = s.s_id
group by s.s_id  -- Student number grouping
having count(s1.c_id) < (  -- Number of courses for students after grouping<3
  select count(*) from Course  -- Total number of courses=3
)

Topic 12

Topic demand

Query the information of students who have at least one course and the same student with student number 01

Analysis process

The topic of the course is at least the same as that of the class 01

Course: Score - > C_ id

Student: Student - > s_ id

SQL implementation

Let's first look at the results: because classmate 01 took all the courses, other students met the requirements. Except classmate 08, they didn't get any grades and didn't meet the requirements.

The specific implementation process is as follows:

select *   -- 3,Get student information
from Student 
where s_id in (
  select distinct s_id   -- 2,Find out all student numbers (students) that meet the requirements of the course in the 01 student course, remove the duplicate student numbers, and exclude 01 himself at the same time
  from Score 
  where c_id in (
    select c_id 
    from Score 
    where s_id=01   -- 1,Find out all the courses of student No. 01
                ) 
  and s_id != 01);

-- Method 2
select s1.*
from Student s1
join Score s2
on s1.s_id = s2.s_id  -- Relationship between student table and grade table
and c_id in (select c_id from Score where s_id=01)  -- The course is limited to the course of 01 students only
group by s1.s_id;  -- Group according to student number

Topic 13

Topic demand

Query the information of students who are exactly the same as the courses studied by 01

Analysis process

We can speculate on this question: because the total number of courses is 3, and the number of courses of classmate 01 is just 3, so we just need to find out the students who have completed three courses in the Score table.

SQL implementation

1. Own method

select * 
from Student 
where s_id in (select s_id   -- 3,The student number obtained in step 2 meets the requirements
               from(select distinct(s_id), count(c_id) number 
                    from Score 
                    group by s_id)t1 -- 1,Student number and course grouping results t1
               where number=3  -- 2,Speculation: select the student number whose number of courses is 3 (3 in 01)
               and s_id !=01  -- 01 Self exclusion
              );             

In step 2 above, we do not consider directly specifying 3 (where number=3), but use the number of courses taken by student 01 (although it is also 3):

select * 
from Student 
where s_id in(
  select s_id   -- 3,The student number obtained in step 2 meets the requirements
  from(select distinct(s_id), count(c_id) number 
       from Score 
       group by s_id)t1 -- 1,Student number and course grouping results t1
       where number=(select count(c_id) number 
                     from Score 
                     group by s_id having s_id=01)  -- 2,Place of change: use the number of courses 3 of student number 01 instead
  and s_id !=01  -- 01 Self exclusion
);             

1. Use group_concat function

group_concat is used as follows:

group_concat([DISTINCT] field [Order BY ASC/DESC sort field] [Separator 'Separator'])

We will each s in the Score table_ C of ID_ ID is grouped and merged. The actual effect is as follows:

select 
	s_id
	,group_concat(c_id order by c_id) -- Grouping, merging and sorting at the same time
from Score 
group by s_id;

The reason for sorting is to prevent this situation: 01 students take courses in the order of 01, 02, 03; If some students take courses in the order of 02, 03 and 01, although the order is different, they take the same courses in essence

After sorting, it will become 01, 02 and 03 to ensure the same results

After that, we only need to judge the same result as that of classmate 01 after the merger, and take out the student number:

select *   -- 3,Query information
from Student
where s_id in(
  select s_id 
  from Score 
  group by s_id 
  having group_concat(c_id order by c_id)=(   -- 2,Find the student number that is the same as the result of grouping and merging of students No. 01 s_id；Sorting is also required
    select group_concat(c_id order by c_id)   -- 1,Find out the results of grouping and merging of students No. 01 and sort them at the same time; Sorting is important
    from Score 
    group by s_id
    having s_id=01)
  and s_id != 01  -- Exclude yourself
);

Topic 14

Topic demand

Check the names of students who have not taken any courses taught by Mr. Zhang San

Analysis process

Teacher: Teacher - > t_ name(t_id)

Course: Course - > t_ id——>c_ id

Name: Student

SQL implementation

The specific process is as follows:

select s_name   -- 4,The student number is reversed to find the student's name
from Student 
where s_id not in(
  select distinct(s_id) -- 3,Course number: find the corresponding student number
  from Score 
  where c_id=(
    select c_id -- 2,Teacher number: find the corresponding course number
    from Course 
    where t_id=(
      select t_id   -- 1,Name find teacher number
      from Teacher 
      where t_name='Zhang San')
  ));

 -- Student number of students who have taken Mr. Zhang's course
 select distinct(s_id) 
 from Score 
 where c_id=(
   select c_id 
   from Course 
   where t_id=(
     select t_id 
     from Teacher 
     where t_name='Zhang San')); -- Students who have taken teacher Zhang San's class

Topic 15

Topic demand

Check the student number, name and average score of students who fail two or more courses

Analysis process

We need to count the number of courses that each Student failed: directly count C through the data in the Score table_ Quantity with ID less than or equal to 60; Then join with the Student table

SQL implementation

First, let's see which students meet the requirements of passing two or more courses

-- 2 Failing at or above

select 
	s_id
  ,round(avg(s_score))  avg_score
from Score 
where s_score < 60 -- Less than 60 points, fail
group by s_id
having count(s_score) >= 2;   -- More than two failed

Note 04 and 06 are the final results we want

-- Own method
select 
	s.s_id
	,s.s_name 
	,t.avg_score
from Student s
join (select 
      	s_id
      	,round(avg(s_score))  avg_score
      from Score 
      where s_score < 60 
      group by s_id
      having count(s_score) >= 2)t
on s.s_id=t.s_id

-- Reference method 1

select 
	a.s_id,
	a.s_name,
	ROUND(AVG(b.s_score)) 
from Student a 
left join Score b 
on a.s_id = b.s_id
where a.s_id in(
  select s_id 
  from Score 
  where s_score<60 
  group by s_id 
  having count(1)>=2)
group by a.s_id,a.s_name

-- Reference method 2
select 
	s.s_id
	,s_name
	,round(avg(s_score), 2) avg_score
from Student s
join Score sc
on s.s_id=sc.s_id
and sc.s_score < 60  -- fail,
group by s.s_id   -- Student number grouping
having count(sc.c_id )>= 2;   -- 2 Course	

Improvement points

The above two methods do not consider all 08 students, and there are no scores in three subjects. This problem needs to be improved.