mysql 学生成绩练习题
student
course
teacher
score
1 查询Score表中至少有5名学生选修的并以3开头的课程的平均分数
select cno,avg(degree) from score group by cno having count(*)>=5 and cno regexp "^3"
思路:按课程分组,计算各课程选修人数,用正则表达式找到以3开头的课程(此处也可以用like+通配符%)
2 查询分数大于70,小于90的Sno列
select sno from score where degree between 70 and 90
3 查询所有学生的Sname、Cno和Degree列
select sname,cno,degree from student st,score sc where st.sno = sc.sno order by sname
4 查询所有学生的Sname、Cname和Degree列
select sname,cname,degree from score sc,student st,course c where sc.sno = st.sno and sc.cno = c.cno
5 查询“95033”班学生各课程的平均分
select cname,format(avg(degree),2) avg_degree from score inner join course on score.cno = course.cno inner join (select sno from student where class = 95033)as c on c.sno=score.sno group by cname
6 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录
select sno,degree from score where cno = '3-105' and degree > (select degree from score where sno = 109 and cno = '3-105')
7 查询和学号为108、101的同学同年出生的所有学生的Sno、Sname和Sbirthday列
select sno,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student where sno in(108,101))
8 查询“张旭“教师任课的学生成绩
#方法一 子句嵌套
select sno,cno,degree from score where cno = (select cno from course where tno = (select tno from teacher where tname = "张旭")) #方法二 多表联结
select s.sno,s.cno,s.degree from score s,course c,teacher t where t.tname = "张旭" and t.tno = c.tno and c.cno = s.cno
9 查询选修某课程的同学人数多于5人的教师姓名
#方法一
select tname from teacher where tno=(select tno from course where cno =(select cno from score group by cno having count(*)>5)) #方法二
select tname from teacher t,course c where t.tno = c.tno and c.cno = (select cno from score group by cno having count(*)>5)
10 查询“计算 机系”与“电子工程系“不同职称的教师的Tname和Prof
select Tname,Prof from Teacher where Depart ='计算机系' and Prof not in( select Prof from Teacher where Depart ='电子工程系') union select Tname,Prof from Teacher where Depart ='电子工程系' and Prof not in( select Prof from Teacher where Depart ='计算机系');
11 查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序
select Cno,Sno,Degree from Score where Cno = '3-105' and Degree > any(select Degree from Score where Cno = '3-245') order by Degree desc;
any some 表示集合内or关系 all是and关系
12 查询成绩比该课程平均成绩低的同学的成绩表
select * from score a where degree < (select avg(degree) from score b where b.cno=a.cno);
数据库引擎逐条从主查询取记录与子查询对比过滤
13 查询至少有2名男生的班号
select class from student group by class having count(ssex="男")>=2