mysql< Six >

--########## 01, comprehensive practice the table structure used by ##########--is derived from the database design of "Teacher instruction, student elective course and course achievement" created earlier--Studentinfo, Teacherinfo, Courseinfo, scoreinfo--1. The number of teachers who queried the surname Zhang Select count (Teacherid) as Zhang's number of teachers from the Teacherinfowhere teachername like ' Zhang% ';--2. Query the number of students enrolled in each course--writing 1, using the left link SELECT c. ' Coursename ' as course name, temp. Number of students enrolled from Courseinfo as cleft JOIN (SELECT CourseID, COU    NT (StudentID) as elective number of students from Scoreinfo GROUP by CourseID) as temp on c.courseid = temp.courseid;--notation 2, using subqueries Select C. ' Coursename ' as course name (SELECT COUNT (StudentID) from Scoreinfo as sc WHERE Sc.courseid = c. ' C Ourseid ') as the number of students enrolled from Courseinfo as c;--3, the number of students with a personal average score higher than 60, the student's name and the individual average score (if the number of people more than 2, show the second record and the third record) SELECT TEMP.S    Tudentid as student number, s.studentname as student name, temp. Personal average score from (SELECT StudentID, AVG (score) as personal average score from Scoreinfo GROUP by StudentID has AVG (score) >) as Templeft JOIN studentinfo as SON Temp.studentid = S.studentidlimit 1, 2;--4, number of males and number of females select Studentgender as Gender,Count (StudentID) as number of students from Studentinfogroup by studentgender;--5, check the number of pupils with the same name select * from Studentinfo;insert to Studen Tinfo VALUES (NULL, ' Shing ', ' female ', 38); SELECT Studentname as duplicate name of student, Count (StudentID) as duplicate number of students from Studentinfogroup by studentnamehaving count (StudentID) > 1    ;--6, the average score of each course is queried, and the results are arranged in ascending order according to the average grade of each course, and the result is the same, in the reverse order of the course number Select C. ' Coursename ' as course name, temp. Average score from Courseinfo as Cleft JOIN (  SELECT CourseID, AVG (score) as average score from Scoreinfo GROUP by CourseID) as Tempon C.courseid = Temp.courseidorder by Temp. Average score ASC, c. ' CourseID ' desc;--7, the name and score of the student whose course name is math and whose math score is less than 60, select C. ' Coursename ' as course name, S. ' Studentname ' as student name , SC. ' Score ' as fraction from Scoreinfo as Scinner JOIN Courseinfo as C on sc.courseid = C.courseid and c.coursename = ' math ' and s  C.score< -INNER JOIN Studentinfo as s on Sc.studentid= S.studentid;--8. Check the course information of all students (shown as: Student number, student name, course name, and single-line display) Select Temp. Student number, temp. Student name, Group_concat (temp. Course name) As course name from (Selec T S. ' StudentID ' as student number, S. ' Studentname ' as student name, C. ' Coursename ' as course name from Studentinfo as S left JOIN Scoreinfo A S SC on S.studentid= Sc.studentidLeft JOIN Courseinfo as C on Sc.courseid= C.courseidas Tempgroup by temp. Student number;--9. Check the name, course name and grade of the students who have scored more than 60 points in any course. ' Studentname ' as the student's name, C. ' Coursename ' as the course name, Sc. ' score ' as fractions from Scoreinfo as Scinner joins Courseinfo as C on Sc.courseid= C.courseidINNER JOIN Studentinfo as s on Sc.studentid= S.studentidWHERE Sc.score> 60;--10, query the student information of at least two courses--1, use independent subquery select S. ' StudentID ' as student number, S. ' Studentname ' as student's name, S. ' Studentgender ' as student gender, S. ' Studentage ' as student age from Studentinfo as Swhere S.studentid in (SELECT StudentID from Scoreinfo GROUP by student  ID having COUNT (courseid) >= 2);--notation 2, using inner connection select S. ' StudentID ' as student number, S. ' Studentname ' as student name, S. ' Studentgender ' As student gender, S. ' Studentage ' as student age from Studentinfo as Sinner JOIN (SELECT StudentID from Scoreinfo GROUP by student ID having COUNT (courseid) >= 2) as Tempon S.studentid = temp.studentid;--11, all students are enrolled in the course number and course name (based on no dirty data) SELECT Cou Rseid as course number, Coursename as course name from Courseinfowhere CourseID in (--group by course number in the Score information table, count the number of student numbers per group, and see which groups are the same as the number of students in the Student information table) The consensus is that all students are enrolled in the course SELECT CourseID from Scoreinfo GROUP by CourseID have count (studentid) = (select COUNT (Stud Entid) (from Studentinfo));--12, query personal English scores higher than the students ' information-thinking: In the score information table to get a comparison of row data, the operation is more troublesome-consider the "Row to column" operation, so that you can in a row of different The contents of the column are compared--row to column"Tip: Create two separate sets from the course number corresponding to the course name from the Score information table, and then connect the two sets according to the student number--so that you get a new set of different courses for the same student, that is, a new set of SEL with different course scores in the same row ECT S. ' StudentID ' as student number, S. ' Studentname ' as student's name, S. ' Studentgender ' as student gender, S. ' Studentage ' as student age from Studentinfo as S  WHERE S. ' StudentID ' in (---Personal English score collection from the Score information table Select StudentID, score from    Scoreinfo where CourseID = (SELECT CourseID from courseinfo WHERE coursename = ' English ')) as Temp1 INNER JOIN (  --Personal math score collection from the Score information table Select StudentID, score from scoreinfo WHERE CourseID = (select CourseID from Courseinfo WHERE coursename = ' math ')) as temp2 on temp1.studentid = Temp2.studentid and Temp1.score > Temp2.score);--13, query Number of all students, name, number of courses selected, total of select S. ' StudentID ' as student number, S. ' Studentname ' as student name, COUNT (SC. ' CourseID ') as the number of courses selected, SUM (SC. ' Score ') A  S total from Studentinfo as Sleft joins Scoreinfo as SC on s.studentid = sc.studentid--grouped by student number, syntax Okgroup by s.studentid;-- Group by student number and student name, grammar is OK,Because StudentID is the primary key, long can uniquely identify the record, plus studentname belong to the icing on the cake-group by S.studentid, S. ' Studentname ';--according to the student's name, grammar is not OK, Because of the name of the student, it will be divided into a group--group by s.studentname;--14, the query did not take the teacher's course student information-thinking: There are two types of students who did not take the teacher's course: The students who took the course but chose not the teacher's course and Students who do not have elective courses-which are more troublesome to think about-are thinking of thinking backwards-removing students from the student collection who have taken the teacher's course, and the rest is the student who has not taken the teacher's course, select S. ' StudentID ' as student number, s    . ' Studentname ' as student's name, S. ' Studentgender ' as student sex, S. ' Studentage ' as student age from Studentinfo as Swhere S.studentid not in (    --Students taking teacher's course SELECT Sc.studentid from Scoreinfo as SC INNER JOIN Courseinfo as C on sc.courseid = C.courseid INNER JOIN Teacherinfo as T on c.teacherid = T.teacherid and T.teachername = ' Miss Zhang ');--15, the inquiry has studied the Chinese also learned the student information of mathematics select S. ' Stud Entid ' as student number, S. ' Studentname ' as student's name, S. ' Studentgender ' as student gender, S. ' Studentage ' as student age from studentinfo as Sinner JOIN Scoreinfo as Sc1on S.studentid = Sc1.studentid and Sc1.courseid = (SELECT CourseID from courseinfo WHERE coursename = ' language ' ) INNER JOIN scoreinfo as Sc2on S.studentid =Sc2.studentid and Sc2.courseid = (SELECT CourseID from courseinfo WHERE coursename = ' mathematics ');--16. Inquire about students ' failure to pass each course in their own grades--May Sex 1, no achievement is also satisfied with the conditions of select S. ' StudentID ' as student number, S. ' Studentname ' as student's name, S. ' Studentgender ' as student gender, S. ' Studentage ' as student age from Studentinfo as Swhere S.studentid not in (SELECT StudentID from Scoreinfo WHERE score >= 60);--Probability 2, non-achievement not satisfied bar Piece select S. ' StudentID ' as student number, S. ' Studentname ' as student's name, S. ' Studentgender ' as student gender, S. ' Studentage ' as student age from Studentinfo     As Swhere S.studentid in (--the following is not correct: because this will be part of the course failed to pass some of the students also screened out-SELECT StudentID-from Scoreinfo-  WHERE Score< ---correct wording: Grouped by student number, the highest course score in the group is less than 60, which means that all courses fail SELECT StudentID from Scoreinfo GROUP by StudentID have MAX (Score) < 60);--17, the number of points for each subject is displayed as follows: Course number, course name, number of elective courses, [excellent 90~100], [Good 80~90], [General 70~80], [Pass 60~70], [inferior lattice 0~60] Select SC. ' CourseID ' as course number, C. ' Coursename ' as course name, COUNT (SC. ' StudentID ') as the number of courses selected, SUM (case when Sc.score 
    >= Sc.score<=Then 1 ELSE 0 END) As ' [excellent 90~100] ', SUM ( case when Sc.score>= Sc.score< -Then 1 ELSE 0 END) As ' [Good 80~90] ', SUM ( case when Sc.score>= Sc.score and< theThen 1 ELSE 0 END) As ' [General 70~80] ', SUM ( case when Sc.score>= Sc.score< -Then 1 ELSE 0 END) As ' [Pass 60~70] ', SUM ( case when Sc.score>= 0 and Sc.score< -Then 1 ELSE 0 END) As ' [0~60] ' from Scoreinfo as Scinner joins Courseinfo as C on SC. ' CourseID '= c. ' CourseID 'GROUP by SC. ' CourseID ';--18. Query for student information without taking all courses select S. ' StudentID ' as student number, S. ' Studentname ' as student name, S. ' Studentgender ' As student gender, S. ' Studentage ' as student age from Studentinfo as Swhere S.studentid in (---group by student number, the number of courses in each set is less than the number of courses in the course Information table is not an elective course Student Select StudentID from Scoreinfo GROUP by StudentID have count (CourseID) < (select count (CourseID) FRO M courseinfo));--19, inquiry and Liu Bei (student number 1) at least one elective course of student number and student name select S. ' StudentID ' as student number, S. ' Studentname ' as student name, S. ' Studentgender ' as student gender, S. ' Studentage ' as student age from Studentinfo as Swhere S.studentid in (--at least with Liu Bei took a course SELECT St         Udentid from Scoreinfo WHERE CourseID in (--Query out Liu Beixiang course SELECT CourseID from Scoreinfo as SC INNER JOIN Studentinfo as S on S. ' StudentID '= sc. ' StudentID 'and S. ' StudentID '= 1and S. ' Studentname '= ' Liu Bei ') ;--20, inquiry and Zhang Fei (student number 3) elective courses with the exact same student number and student name--Ideas:--1) first make a link to two score information table, the right table is Zhang Fei (student number 3) of the course and results-this time will be completely and Zhang Fei course the same classmates and part and Zhang Flying lessons The same classmate Select *from scoreinfo as Sc1inner JOIN scoreinfo as SC2 on SC1. ' CourseID '= sc2. ' CourseID 'and sc2. ' StudentID '= 3and sc1. ' StudentID ' <>sc2. ' StudentID '--2) on this basis, in the newly generated collection according to the student number group, if there is a student's course number and Zhang Fei's course quantity is consistent, then Zhang Fei (student number 3) Elective courses exactly the same student select S. StudentID ' as student number, S. ' Studentname ' as student's name, S. ' Studentgender ' as student gender, S. ' Studentage ' as student age from Studentinfo as Swhere s . StudentID in (SELECT sc1. ' StudentID ' from Scoreinfo as Sc1 INNER joins Scoreinfo as SC2 on SC1. ' CourseID ' = sc2. ' CourseID ' and SC2. ' StudentID ' = 3 and SC1. ' StudentID '<> SC2. ' StudentID ' GROUP by Sc1 ' StudentID ', sc2. ' StudentID ' has COUNT (sc1. ' CourseID ') = (SELECT COUNT (CourseID) FR OM scoreinfo WHERE StudentID = 3);--21, according to the average grade of individuals in descending order to display the students ' Chinese, maths, English, the results of the course (elective several of the average score of several doors, not elective courses show not selected)-displayed as: Student number, student name,             Average score, language score, Math score, English score select SC. ' StudentID ' as student number, S. ' Studentname ' as Student name, AVG (SC. ' score ') as average score, ifnull (( SELECT Sc1.score from Scoreinfo as SC1 WHERE Sc1.studentid = sc. ' StudentID ' A ND Sc1.courseid = (SELECT CourseID from courseinfo WHERE coursename = ' language '), ' not selected ') as Language score, ifnull ((SE Lect Sc2.score from Scoreinfo as SC2 WHERE Sc2.studentid = sc. ' StudentID ' and Sc2.coursei             D = (select CourseID from courseinfo WHERE coursename = ' math '), ' not selected ') as Math score, ifnull ((select Sc3.score From Scoreinfo as sc3 WHERE Sc3.studentid = sc. ' StudentID ' and Sc3.courseid = (SELECT Co Urseid from Courseinfo WHere Coursename = ' English '), ' not selected ') as English score from Scoreinfo as Scinner JOIN studentinfo as SON sc. ' StudentID ' = S. ' StudentID ' GROUP by SC. ' StudentID ' – ORDER by AVG (SC. ' score ') desc;--the above notation and the following notation, because the ORDER BY clause executes after the SELECT clause desc;--22 of the order by average    , query the highest and lowest points of each course, shown as: Course number, course name, highest score, minimum select Sc.courseid as course number, C. ' Coursename ' As course name, Max (SC. ' score ') as highest score, Min (sc. ' score ') as lowest point from Scoreinfo as Scinner JOIN courseinfo as CON Sc.courseid = C.courseidgroup by sc.courseid;-- 23. Check the student number and student name of the student who only took one course select SC. ' StudentID ' as student number, S. ' Studentname ' as student name from Scoreinfo as Scinner JOIN stude Ntinfo as SON Sc.studentid = S.studentidgroup by sc.studentidhaving COUNT (Sc.courseid) = 1;--24, the student number of the student who has studied all the courses taught by the teacher and the students Name Select SC. ' StudentID ' as student number, S. ' Studentname ' as student name from Scoreinfo as Scinner JOIN Courseinfo as C on Sc.coursei D = C.courseidinner Join Teacherinfo as T on c.teacherid = T.teacherid and T.teachername = ' Miss Zhang ' INNER join Studentinfo as s on sc.studentid = S.stuDentidgroup by Sc.studentidhaving COUNT (Sc.courseid) = (---teacher Zhang teaches the number of courses SELECT COUNT (courseinfo. ' CourseID ') from Co Urseinfo INNER JOIN teacherinfo as T on courseinfo.teacherid = T.teacherid and T.teachername = ' Miss Zhang ');--25. The Student information sheet was deleted. Several records, which now need to be queried for rows 4th through 6th, are used (consider using multiple implementations, hint: use limit and do not use limit)--delete from studentinfo where StudentID = 3 or StudentID = 7;--1, the direct use of the LIMIT keyword SELECT * from Studentinfo limit 3, 3;--2, consider removing the first 6 lines, in reverse order, then remove the first 3 lines, and then reverse select *from (SELECT * FR    OM (SELECT * from Studentinfo LIMIT 0, 6) as Temp1 ORDER by Temp1.studentid DESC As Temp2 limit 0, 3) as Temp3order by Temp3.studentid asc;--notation 3, do not use the LIMIT keyword Select temp. ' StudentID ' as student number, temp. ' St Udentname ' as student name, temp. ' Studentgender ' as student gender, temp. ' Studentage ' as student age from (SELECT *, (select COUNT (*)  From Studentinfo as S2 WHERE s2.studentid<= S1. ' StudentID ') As rownum from Studentinfo as S1) as Tempwhere temp.rownum between 4 and 6;

mysql< Six >