Python/mysql Exercises (II.)

Python/mysql Exercises (II.)

21、查询各科成绩前三名的记录:(不考虑成绩并列情况)

1Select Score.sid,score.course_id,score.num,t.first_num,t.second_num fromScore left Join2     (3 Select4 SID,5(Select num fromScore as s2 where s2.course_id = s1.course_id order BY num DESC limit 0,1) as First_num,6(Select num fromScore as s2 where s2.course_id = s1.course_id order BY num DESC limit 3,1) as Second_num7      from8 score as S19 ) as TTenOn score.sid =T.sid Onewhere Score.num <= t.first_num andScore.num >= T.second_num

22. Check the number of students who have been enrolled in each course

1  from score left JOIN course on course.cid=score.course_id2GROUP by course_id3 having COUNT (1) >4

23. Find out the number and name of all students who have only one course of study

1  from score left join student on student.sid=score.student_id2 left join course on course.cid=< c4>score.course_id3GROUP by student_id4 have count (score.course_id) =' 1 '

24. Query the number of boys and girls

1  from Student 2 GROUP by Gender 3 having count (SID)

25. Check the list of students with the surname "Zhang"

1  from ' Zhang% '

26. Check the names of students with the same name and count the number

1  from Student 2 GROUP by Sname 3 having COUNT (sname)

27, the average score of each course is queried, the results are arranged in ascending order of average grade, and the average result is the same, descending by the course number.

1   from score left JOIN course on course.cid=score.course_id2  GROUP by course_id3< /c6>  

28. The number, name and average scores of all students who have average scores greater than 85

1   from score left JOIN student on student.sid=score.student_id2  GROUP by student_id  3having  avg (num) > 85

29. Check the name and score of the student whose course title is "physical" and whose score is less than 60

1 SELECT student.sname,score.num from   score left JOIN student on student.sid=Score.student_ ID2 left JOIN course on course.cid=score.course_id3 where course.cname='  Creature ' and Score.num <60

30. The number and name of the student whose course number is 003 and the course score is more than 80

1  from score left join course on course.cid=score.course_id2 left join student on Student.sid=
   
    score.student_id
    3 where course.cid=
    '
    3
    '
     and num > 80
   

31. Number of students selected for the course

1  from 2  from Score GROUP by student_id) as A

32. The name of the students with the highest performance and the results of the students who are enrolled in the courses offered by the "Li Ping" teacher

1  from score 2 left join course in course.cid=score.course_id3 left join student on student.sid=  score.student_id4 left JOIN teacher on teacher.tid=course.teacher_id5 where Teacher.tname=' Mr. Li Ping '6GROUP by student_id7  ORDER by Num DESC8 LIMIT 1

33. Check each course and the corresponding number of elective

1 SELECT cname,count (1) from score left JOIN course on course.cid=score.course_id2 GROUP by course_id

34. The student's number, course number, and student score of the students who have the same results in different courses

1  from score as A1, score as A2 2  and a1.num=a2.num3 GROUP by student_id

35. Check the top two of each course score

1Select Score.sid,score.course_id,score.num,t.first_num,t.second_num fromScore left Join2     (3 Select4 SID,5(Select num fromScore as s2 where s2.course_id = s1.course_id order BY num DESC limit 0,1) as First_num,6(Select num fromScore as s2 where s2.course_id = s1.course_id order BY num DESC limit) as Second_num7      from8 score as S19 ) as TTenOn score.sid =T.sid Onewhere Score.num <= t.first_num andScore.num >= T.second_num

36. Retrieve the student number of at least two elective courses

1  from  from score GROUP by course_id) as A2 from score left joins student on Student.sid=score.student_i D3GROUP by student_id4 have count (1) > 2

37. Check the course number and course name of all the students who have enrolled in the course

1  from  from Student)

38, inquiry did not learn the "Li Ping" teacher taught any of the students name of the course

1 SELECT2 Student.sname3  from4 Student5 WHERE6 Sid Not in (7 SELECT8 course_id9  fromTen score OneLeft JOIN course on course.cid =score.course_id ALeft JOIN student on student.sid =score.student_id -Left JOIN teacher on teacher.tid =course.teacher_id - WHERE theTeacher.tname ='Mr. Li Ping' -)

39. Check the number of students with two or more failed courses and their average scores

1  from score where num <60 GROUP by student_id have count (1) >2

40, search "004" course score is less than 60, in descending order by the number of students

1  from  and num <60 ORDER by num DESC

41, delete the "002" Students of the "001" The results of the course

1  from  and Course_id=1

Python/mysql Exercises (II.)