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

 

转载于:https://www.cnblogs.com/islvgb/p/9483645.html