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 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) );

Topic 12-- 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 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);

Topic 13-- 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 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

- 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 );

- 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:

Topic 14select * -- 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 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') ));

Topic 15-- 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 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.