时间：2021-07-01 10:21:17 帮助过：17人阅读
3、查询平均成绩大于60分的同学的学号和平均成绩
1 SELECT 2 B.student_id, 3 student.sname, 4 B.cc 5 FROM 6 ( 7 SELECT 8 student_id, 9 num, 10 avg(num) AS cc 11 FROM 12 score 13 GROUP BY 14 student_id 15 HAVING 16 avg(num) > 60 17 ) AS B 18 LEFT JOIN student ON B.student_id = student.sid
4、查询所有同学的学号、姓名、选课数、总成绩
1 SELECT 2 student_id, 3 student.sname, 4 count(score.course_id)as cc, 5 sum(num)as cj 6 FROM 7 student 8 LEFT JOIN score ON score.student_id = student.sid 9 GROUP BY 10 score.student_id
5、查询姓“李”的老师的个数
1 SELECT * from teacher where tname like ‘李%‘
6、查询没学过“李平”老师课的同学的学号、姓名
1 SELECT student.sid,student.sname from student where sid not in 2 (SELECT 3 student_id 4 FROM 5 score 6 WHERE course_id IN 7 ( 8 SELECT 9 course.cid 10 FROM 11 course 12 LEFT JOIN teacher ON teacher.tid = course.teacher_id 13 WHERE 14 tname = ‘李平老师‘ 15 ) GROUP BY student_id)
7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
1 select A.student_id,B.sname FROM (SELECT score.student_id,student.sname,course_id 2 from score LEFT JOIN student on student.sid=score.student_id where score.course_id=‘1‘)as A 3 LEFT JOIN(SELECT score.student_id,student.sname,course_id 4 from score LEFT JOIN student on student.sid=score.student_id where score.course_id=‘2‘)as B 5 on A.student_id=B.student_id 6 where A.course_id=1 and B.course_id=2;
8、查询学过“李平”老师所教的所有课的同学的学号、姓名
1 SELECT 2 student.sid, 3 student.sname 4 FROM 5 student 6 WHERE 7 student.sid NOT IN ( 8 SELECT 9 student.sname 10 FROM 11 student 12 WHERE 13 student.sid IN ( 14 SELECT 15 course.cid 16 FROM 17 course 18 LEFT JOIN teacher ON teacher.tid = course.teacher_id 19 WHERE 20 teacher.tname = ‘李平老师‘ 21 ) 22 )
9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名
1 SELECT 2 student.sid, 3 student.sname 4 FROM 5 ( 6 SELECT 7 * 8 FROM 9 score 10 WHERE 11 score.course_id = ‘2‘ 12 ) AS A 13 LEFT JOIN ( 14 SELECT 15 * 16 FROM 17 score 18 WHERE 19 score.course_id = ‘1‘ 20 ) AS B ON A.student_id = B.student_id 21 LEFT JOIN student ON student.sid = B.student_id 22 WHERE 23 A.num < B.num
10、查询有课程成绩小于60分的同学的学号、姓名
1 SELECT 2 student.sid, 3 student.sname 4 FROM 5 score 6 LEFT JOIN course ON course.cid = score.course_id 7 LEFT JOIN student ON student.sid = score.student_id 8 WHERE 9 score.num < 60 10 GROUP BY 11 student_id
11、查询没有学全所有课的同学的学号、姓名
1 SELECT 2 student.sid, 3 student.sname 4 FROM 5 student 6 WHERE 7 student.sid NOT IN ( 8 SELECT 9 student.sid 10 FROM 11 score 12 LEFT JOIN course ON course.cid = score.course_id 13 LEFT JOIN student ON student.sid = score.student_id 14 GROUP BY 15 score.student_id 16 HAVING 17 count(course_id) = (SELECT COUNT(cid) FROM course) 18 )
12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名
1 SELECT 2 * 3 FROM 4 score 5 LEFT JOIN student on score.student_id = student.sid 6 LEFT JOIN course ON course.cid = score.course_id 7 WHERE student_id != 1 AND 8 score.course_id in ( 9 SELECT 10 course_id 11 FROM 12 score 13 WHERE 14 student_id = 1 15 ) 16 GROUP BY student_id
13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名
1 SELECT student_id from score where student_id!=1 and course_id IN 2 (select course_id from score where student_id =1 GROUP BY course_id) 3 GROUP BY student_id
14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名
1 SELECT 2 student_id, 3 sname 4 FROM 5 score 6 LEFT JOIN student ON score.student_id = student.sid 7 WHERE 8 student_id IN ( 9 SELECT 10 student_id 11 FROM 12 score 13 WHERE 14 student_id != 1 15 GROUP BY 16 student_id 17 HAVING 18 count(course_id) = ( 19 SELECT 20 count(1) 21 FROM 22 score 23 WHERE 24 student_id = 1 25 ) 26 ) 27 AND course_id IN ( 28 SELECT 29 course_id 30 FROM 31 score 32 WHERE 33 student_id = 1 34 ) 35 GROUP BY 36 student_id 37 HAVING 38 count(course_id) = ( 39 SELECT 40 count(1) 41 FROM 42 score 43 WHERE 44 student_id = 1 45 )
15、删除学习“李平”老师课的SC表记录
1 DELETE FROM score where score.course_id IN 2 (SELECT course_id from course LEFT JOIN teacher on teacher.tid=course.teacher_id 3 LEFT JOIN score on score.course_id=course.cid 4 LEFT JOIN student on score.student_id=student.sid 5 WHERE teacher.tname=‘李平老师‘ 6 GROUP BY course_id;)
16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩
1 insert into score (student_id,course_id,num)SELECT student_id,1,FLOOR(avg(num)) 2 (SELECT student_id from score where course_id !=2 3 SELECT FLOOR(avg(num))from score where course_id = 2)
17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分
1 select sc.student_id, 2 (select num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sy, 3 (select num from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl, 4 (select num from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as ty, 5 count(sc.course_id), 6 avg(sc.num) 7 from score as sc 8 group by student_id desc
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
1 select student_id,MAX(num),MIN(num) from score GROUP BY course_id
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
1 SELECT 2 course_id, 3 avg(num) AS nn, 4 sum( 5 CASE 6 WHEN num < 60 THEN 7 0 8 ELSE 9 1 10 END 11 ), 12 SUM(1), 13 sum( 14 CASE 15 WHEN num < 60 THEN 16 0 17 ELSE 18 1 19 END 20 ) / SUM(1) AS pj 21 FROM 22 score 23 GROUP BY 24 course_id 25 ORDER BY 26 avg(num) DESC
20、课程平均分从高到低显示（现实任课老师）
1 SELECT score.course_id,course.cname,avg(num),teacher.tname from score LEFT JOIN course on course.cid=score.course_id 2 LEFT JOIN teacher on teacher.tid=course.teacher_id 3 GROUP BY course_id 4 5 HAVING avg(num) 6 ORDER BY avg(num) DESC
Python/ MySQL练习题（一）
标签：color 完全 没有 student 成绩 pre cas 顺序 sid