MySQL article---mysql exercises

2. Inquire about the number of students in the ' biology ' course that is higher than the ' physics ' course: Get all the people who have a biology course (student number, score)-temporary table get all the people who have a physics course (student number, score)-temporary table connect two temporary tables according to the "Student number": Biology---of physical achievements SELECT Score.sid,score.student_id,course.cname,score.num from Scoreleft JOIN course on Score.course_id=course.cid WHERE course.cname= ' creature '; select a.student_id from (select Score.sid,score.student_id,course.cname,score.num from Score Left JOIN course on score.course_id=course.cid where course.cname= "creature") as Ainner JOIN (select score.sid,score.student_id , Course.cname,score.num from score left JOIN course on Score.course_id=course.cid where course.cname= "physical") as Bon A.studen t_id = B.student_idwhere a.num > b.num3. Query the number of students with average scores greater than 60 points and the average score; ideas: Use AVG to get an average based on the student group, and filter the AVG by having a s Elect Student_id,avg (NUM) from score GROUP by STUDENT_ID have avg (num) > 60 to perform a table operation: SELECT B.student_id,student.sname, B.mean from (SELECT student_id,avg (num) as mean to score GROUP by STUDENT_ID have avg (num) > as Bleft JOIN Stud ENT on B.student_id=student.sid 4. Check all students ' School number, name, number of courses selected, assemblySELECT Score.student_id,student.sname,count (student_id), sum (num) from score left joins student on score.student_id= Student.sidgroup by SCORE.STUDENT_ID5. Query the number of teachers with the surname ' Li ' select count (1) from teacher WHERE tname like ' li% '; 6. Inquiry did not learn ' Miss Li Ping ' The student's number, name, and idea: first find out the ID of all the lessons taught by teacher Li Ping to get all student ID students who have chosen the class. Filter select Student.sid,student.sname from student WHERE SID is not in ( Select student_id from Score WHERE course_id in (SELECT course.cid from course left joins teacher on COURSE.TEACHER_ID=TEAC Her.tid WHERE teacher.tname = ' li ping ') GROUP by student_id) 7, the inquiry learned "001" and also learned the number "002" course of the students of the student's number, name , the idea: first found that both choose 001 and choose 002 Course of the students to group according to the student, if the number of students equals 2 means that both doors have been selected select Student_id,sname from (select student_id,course_id from Score WHERE course_id=1 OR course_id=2) as Bleft JOIN student on b.student_id = Student.sid GROUP by STUDENT_ID have CO UNT (student_id) > 18. Inquires the students who have studied the ' Li Ping ' class, their names; ditto, except that 001 and 002 become in the SELECT student.sid,student.sname from student WHERE SID In (select student_id from score WHERE course_id on (select Course.cid from Course Left JOIN teacher on Course.teacher_id=teacher.tid WHERE teacher.tname = ' teacher Li ') GROUP by student_id 10. Students who have a course score of less than 60 points number, name; Select Sid,sname from student where Sid in (SELECT DISTINCT student_id from score WHERE num < 60) 11. Inquire about the students who have not studied all the classes Number, name, idea: in the score table according to the students to group, to obtain each student to choose the number of courses if the quantity = = Total Course quantity, indicating that all courses have been selected select Student_id,sname from score left JOIN student on Score.student_id=student.sidgroup by student_id has count (course_id) = (SELECT count (1) from course) 12. To inquire at least one course with the students who have studied the same number and name as the students who have learned the same as "001"; Train of thought: Get all the courses selected by 001 students to get the course in which all the students and all the courses are based on student screening, get all student information and then connect with the student table, get the name select Student_ Id,sname,count (course_id) from score left joins student on Score.student_id=student.sidwhere student_id! = 1 and course_id In (SELECT course_id from score WHERE student_id=1) GROUP by STUDENT_ID13. Query learn number and name of students who have studied at least 001 students ' class First Find and 001 learn all and then Number = 001 all disciplines = = "Other people may choose more Select Student_id,sname,count (course_id) from Scoreleft JOIN student on score. Student_id=student.sidwhere student_id! = 1 and course_id in (SELECT course_ID from score where student_id = 1) GROUP by student_id have count (course_id) = (SELECT count (course_id) from score WHERE student_id=1) 14. Inquiries and the ' 002 ' students to study the course exactly the same as other students study number and name; ) The same number 002 learned about select student_id from score where student_id in (select student_id from score where student_id!=1 GROUP B  Y student_id has count (1) = (select count (1) from score where student_id = 1)) and course_id in (select course_id from Score where student_id = 1) GROUP by student_id has count (1) = (select count (1) from score where student_id = 1) 15. Delete 1 6. Insert some records into the SC table that require the following conditions: 1. The student number of the number ' 002 ' Course is not 2. Insert the ' 002 ' course's average score idea; Because insert supports INSERT INTO TB1 (xx,xx) Select X1,x2 from TB2; all, get all the people who didn't take 002 lessons, get 002 average score 17.18. Query the highest and lowest scores for each section: Course ID, highest score, lowest score, SELECT Course_id,max (num) as Max_num, MIN (num) as Min_num from score GROUP by course_id;19. The percentages from low to high and passing rate are from high to low in the order of the subjects ' average grades; method one: SELECT course_id,avg (num), SUM (case When num<60 then 0 else 1 end), sum (1), sum (case if num<60 then 0 ELSE 1 end)/sum (1) *100as percent from SCoregroup by Course_idorder by AVG (num) asc,percent desc; method Two: Select COURSE_ID, AVG (num) as avgnum,sum (case when Score.num > 1 Else 0 END)/count (1) *100 as percent from score group by course_id ORDER by Avgnum asc,percent desc;20. Course Average Display from high to low (instructor) SELECT avg (if (IsNull (score.num), 0,score.num)), teacher.tname from Courseleft JOIN score on course.cid= Score.course_idleft JOIN teacher on Course.teacher_id=teacher.tidgroup by Score.course_ Id21. Check the record of the top three records of each section: (regardless of the results) SELECT Score.sid,score.course_id,score.num,t.first_num,t.second_num from Scoreleft JOIN (select SID, (select num from score as S2 WHERE s2.course_id=s1.course_id ORDER by num DESC LIMIT 0,1) as First_num, (S Elect num from score as S2 WHERE s2.course_id=s1.course_id ORDER by num DESC LIMIT 3,1) as Second_numfrom score as S1) as TON score.sid = T.sidwhere score.num <= t.first_num and Score.num >= t.second_num22. Query the number of students enrolled in each course; SELECT course_id, Count (1) from score GROUP by course_id23 the number and name of all students who have only one course enrolled; ) SELECT StudEnt.sid,student.sname,count (1) from Scoreleft JOIN student on Score.student_id=student.sidgroup by COURSE_ID have COUNT (1) = 124. Query male, female number of girls select * FROM (select COUNT (1) as men from student WHERE gender= ' male ') as A, (select COUNT (1) as Woma N from student where gender= ' female ') as b;25. Check the list of students with the surname ' Zhang ' select sname from student WHERE sname like ' Zhang% ' 26. Check the list of students with the same name and count the number SE Lect Sname,count (1) as count from student GROUP by Sname27. The average score for each course is queried, the results are sorted in ascending order by average, and the average grade is the same, select Course_id,avg is arranged in descending order of the course ( if (IsNull (num), 0,num)) as Avg. from Scoregroup to course_id ORDER by avg asc,course_id desc28. Query the number, name and average score of all students whose average score is greater than 85 (! ！！！ >85 not removed) SELECT student_id,sname,avg (if (IsNull (num), 0,num)) from score left JOIN student on score.student_id= Student.sid GROUP by student_id 29. Check the name and score of the student whose class is ' creature ' and score below 60 select Student.sname,score.num from Scoreleft JOIN course On Score.course_id=course.cidleft joins student on Score.student_id=student.sidwhere Score.num < course.cname= ' Biology ' 30. Search for the student's number and name of the course number 003 and the course score of 80 + * FROM score WHERE score.student_id = 3 and Score.num > 8031. Number of students selected in the course select COUNT (c) from (select COUNT (student_id) as C From score Group by student_id) as a;32. The highest student name and the result of the students enrolled in the course "Zhang Lei '" Select Sname,num from Scoreleft JOIN student on SC Ore.student_id=student.sidwhere score.course_id in (SELECT course.cid from course left JOIN teacher oncourse.teacher_id=    Teacher.tid WHERE tname= ' Zhang Lei ') ORDER by Num DESC LIMIT 133. Query each course and the corresponding number of elective persons; Select Course.cname,count (1) from score Left JOIN Course the SCORE.COURSE_ID = Course.cid Group by course_id;34. Check the student's number, course number, and student score for different courses but with the same results (!!!  ) SELECT DISTINCT S1.course_id,s2.course_id,s1.num,s2.numfrom score as s1,score as S2 WHERE s1.num=s2.num and s1.course_id ! = S2.COURSE_ID36. Retrieves at least two elective courses of student number select student_id from score GROUP by student_id have count (student_id) > 1;37. Query all Students are enrolled in the course number and course name (!!! ) SELECT Course_id,count (1) from Scoregroup by course_id has count (1) = (SELECT count (1) from student); 38. Inquiry did not learn ' Li Ping ' teacher lectures The name of the student in any one of the courses select Student_iD,student.sname from Scoreleft JOIN student on Score.student_id=student.sidwhere score.course_id not in (SELECT CID from C Ourse left JOIN teacher on Course.teacher_id=teacher.tid WHERE tname= ' teacher Li ') GROUP by student_id Plus course name Select Student_id,stu Dent.sname,course.cname from Scoreleft join student on Score.student_id=student.sidleft join course on course_id= Course.cidwhere score.course_id not in (SELECT CID from course left joins teacher on Course.teacher_id=teacher.tid WHERE TN Ame= ' Li Ping ') GROUP by student_id39. Check the number of students with two or more failed courses and their average score (!!!)  ) SELECT Student_id,count (1) from score WHERE num < A GROUP by student_id have count (1) > 2 40. Retrieve ' 004 ' course score less than 60, by score Descending class number select student_id from score WHERE num<60 and course_id=4 order by Num DESC;

MySQL article---mysql exercises