有一数据库"S_T"中包含如下三张表:
学生表:student(sno,sname,ssex,sage,sdept)
课程表:course(cno,cname,cpno,ccredit)
选课表:sc(sno,cno,grade)
要求用select语句实现如下操作:
1) 查询平均成绩大于85分的学号、姓名、平均成绩
2) 查询各不同平均成绩所对应的学生人数
3) 查询同年龄的学生信息
4) 查询同系且同龄的学生信息
怎么做呀?求各位高手帮帮忙!
学生表:student(sno,sname,ssex,sage,sdept)
课程表:course(cno,cname,cpno,ccredit)
选课表:sc(sno,cno,grade)
要求用select语句实现如下操作:
1) 查询平均成绩大于85分的学号、姓名、平均成绩
2) 查询各不同平均成绩所对应的学生人数
3) 查询同年龄的学生信息
4) 查询同系且同龄的学生信息
怎么做呀?求各位高手帮帮忙!
from student t0 inner jon sc t1 on t0.sno=t1.sno
where avg(t1.grade)>85
group by t0.sno,t0.sname
2)select cno,avg(grade),count(sno) from sc group by cno
3)select * from student st where student.sage=st.sage
4)select * from student st where student.sdept=st.sdept and student.sage=st.sage
这是我想着写的,没有测试数据,请参照,若有错误,希望提出!
from student a,sc b
where a.sno=b.sno
group by a.sno,a.sname
having avg(b.grade)>852)select c.agrade,count(c.sno) from (
select a.sno as sno,a.sname,avg(b.grade) as agrade
from student a,sc b
where a.sno=b.sno
group by a.sno,a.sname) c
group by c.agrade