Shulou(Shulou.com)06/01 Report--

< 60 ->

GROUP BY-> student_id-> HAVING-> count (course_id) > = 2->) AS T1-> INNER JOIN class ON student.sid = t1.student_id-> AND student.class_id = class.cid +-+-+ | sname | caption | +-+-+ | understand | Class 2 in three years | +-+-+ 1 row in set (0.00 sec) # 11. Query the names of students who have taken all courses mysql > SELECT-> student.sname-> FROM-> student-> WHERE-> sid IN (- > SELECT-> student_id-> FROM-> score-> GROUP BY-> student_id-> HAVING-> COUNT (course_id) = (SELECT count (cid) FROM course)->) +-+ | sname | +-+ | Zhang San | | Zhang Yi | | Zhang er | | Zhang Si | | Hammer | | Li San | | Li Yi | | Li er | | Li Si | | Ruhua | +-+ 10 rows in set (0.01 sec) # 12. Query all grades of courses taught by teacher Li Ping: mysql > SELECT-> *-> FROM-> score-> WHERE-> course_id IN (- > SELECT-> cid-> FROM-> course-> INNER JOIN teacher ON course.teacher_id = teacher.tid-> WHERE-> teacher.tname = 'Mr. Li Ping'->) +-+ | sid | student_id | course_id | num | +-+ | 2 | 1 | 2 | 9 | 11 | 3 | 2 | 66 | | 15 | 4 | 2 | 11 | 19 | 5 | 2 | 11 | 23 | 6 | 2 | 100 | 27 | 7 | 2 | 100 | 31 | 8 | 2 | 100 | 35 | 9 | 2 | 88 | 39 | 10 | 2 | 77 | 43 | 11 | | | 2 | 77 | | 47 | 12 | 2 | 77 | | 5 | 1 | 4 | 66 | 9 | 2 | 4 | 99 | 13 | 3 | 4 | 99 | 17 | 4 | 100 | 21 | 5 | 4 | 100 | 25 | 6 | 4 | | | 100 | | 29 | 7 | 4 | 88 | 33 | 8 | 4 | 88 | 37 | 9 | 4 | 22 | 41 | 10 | 4 | 87 | | 45 | 11 | 4 | 87 | 49 | 12 | 4 | 87 | +-| -+-+ 23 rows in set (0.00 sec) # 13, Query the course number and course name mysql > SELECT-> cid that all students have taken. -> cname-> FROM-> course-> WHERE-> cid IN (- > SELECT-> course_id-> FROM-> score-> GROUP BY-> course_id-> HAVING-> COUNT (student) _ id) = (- > SELECT-> COUNT (sid)-> FROM-> student->)->) Empty set (0.00 sec) # 14. Query the number of electives for each course mysql > SELECT-> course_id,-> COUNT (student_id)-> FROM-> score-> GROUP BY-> course_id +-+-+ | course_id | COUNT (student_id) | +-+-+ | 1 | 12 | 2 | 11 | 3 | 12 | | 4 | 12 | +-+-+ 4 rows in set (0.00 sec) # 15, Query the names and student numbers of students who have taken only one course mysql > SELECT-> sid -> sname-> FROM-> student-> WHERE-> sid IN (- > SELECT-> student_id-> FROM-> score-> GROUP BY-> student_id-> HAVING-> COUNT (course) _ id) = 1->) +-+-+ | sid | sname | +-+-+ | 13 | Liu San | +-+-+ 1 row in set (0.00 sec) # 16. Query the scores of all students and sort them in order from high to low (grades are removed) mysql > SELECT DISTINCT num FROM score ORDER BY num DESC +-+ | num | +-+ | 100 | 99 | 91 | 90 | 88 | 87 | 79 | 77 | 68 | 67 | 66 | 43 | 22 | 11 | 10 | 9 | 8 | +-+ 17 rows in set (0.00 sec) # 17. Query the names and grades of students whose average grade point is greater than 85 mysql > SELECT-> sname -> t1.avg_num-> FROM-> student-> INNER JOIN (- > SELECT-> student_id) -> avg (num) avg_num-> FROM-> score-> GROUP BY-> student_id-> HAVING-> AVG (num) > 85->) T1 ON student.sid = t1.student_id +-+-+ | sname | avg_num | +-+-+ | Liu San | 87.0000 | +-+-+ 1 row in set (0.00 sec) # 18. Query the names and corresponding biological scores of students who fail in biology mysql > SELECT-> sname. Low_num-> FROM-> student-> INNER JOIN (SELECT student_id, num low_num FROM score WHERE course_id = 1 GROUP BY student_id HAVING num

< 60 ) t1 ON student.sid = t1.student_id;+--------+---------+| sname | low_num |+--------+---------+| 理解 | 10 || 钢蛋 | 8 || 张四 | 9 || 铁锤 | 9 || 李三 | 9 |+--------+---------+5 rows in set (0.00 sec)#19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名mysql>

SELECT-> sname-> FROM-> student-> WHERE-> sid = (- > SELECT-> student_id-> FROM-> score-> WHERE-> course_id IN (- > SELECT-> Course.cid-> FROM-> course-> INNER JOIN teacher ON course.teacher_id = teacher.tid-> WHERE-> teacher.tname = 'Mr. Li Ping'->)-> GROUP BY-> student_id-> ORDER BY-> AVG (num) DESC-> LIMIT 1->) +-+ | sname | +-+ | Zhang Si | +-+ 1 row in set (0.00 sec) # 20. Query the names of the top two students with the best scores in each course: mysql > SELECT-> *-> FROM-> score-> ORDER BY-> course_id,-> num DESC +-+ | sid | student_id | course_id | num | +-+ | 34 | 9 | 1 | 91 | 46 | 12 | 1 | 90 | | 42 | | 11 | 1 | 90 | | 38 | 10 | 1 | 90 | | 14 | 4 | 1 | 79 | 18 | 5 | 1 | 79 | 10 | 3 | 1 | 77 | 1 | 1 | 10 | 30 | 8 | 1 | 9 | 26 | 7 | | | 1 | 9 | | 22 | 6 | 1 | 9 | 6 | 2 | 1 | 8 | 31 | 8 | 2 | 100 | 23 | 6 | 2 | 100 | 27 | 2 | 100 | 35 | 9 | 2 | 88 | 47 | 12 | 2 | 77 | | 43 | 11 | 2 | 77 | 39 | 10 | 2 | 77 | 11 | 3 | 2 | 66 | 19 | 5 | 2 | 11 | 15 | 4 | 11 | 2 | 1 | 2 | 9 | 52 | 13 | 3 | 87 | | 12 | | | 3 | 3 | 87 | | 8 | 2 | 3 | 68 | | 20 | 5 | 3 | 67 | 36 | 9 | 3 | 67 | 16 | 4 | 3 | 67 | 24 | 6 | 67 | 32 | 8 | 3 | 67 | 28 | 7 | 3 | 67 | 48 | 12 | 3 | 43 | 44 | 11 | 3 | 43 | 40 | 10 | 3 | 43 | 21 | 5 | 4 | 100 | 17 | 4 | 100 | 25 | 6 | 4 | 100 | 9 | 2 | 4 | 99 | | 13 | 3 | 4 | 99 | | 29 | 7 | 4 | 88 | 33 | 8 | 4 | 88 | 41 | 10 | 4 | 87 | 49 | 12 | 4 | 87 | 45 | 11 | 4 | 87 | 5 | 1 | 4 | 66 | 37 | | 9 | 4 | 22 | +-+ 47 rows in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.