题目如下:
已知关系模式:S(sno,sname),C(cno,cname,cteacher),SC(sno,cno,scgrade),其中:S为学生表,sno为学号,sname为学生姓名;C为课程表,cno为课程号,cname为课程名,cteacher为任课老师,SC为学生选课成绩表,scgrade为成绩
现要求写出如下SQL语句:
1.找出选修过‘李明’老师讲授的所有课程的学生的姓名。
2.列出所学课程平均分成绩高于80分而且没有不及格课程的学生姓名及其平均成绩。
3.列出即学过‘1’号课程又学过2号课程的所有学生的学号。请各位帮忙,在线等
已知关系模式:S(sno,sname),C(cno,cname,cteacher),SC(sno,cno,scgrade),其中:S为学生表,sno为学号,sname为学生姓名;C为课程表,cno为课程号,cname为课程名,cteacher为任课老师,SC为学生选课成绩表,scgrade为成绩
现要求写出如下SQL语句:
1.找出选修过‘李明’老师讲授的所有课程的学生的姓名。
2.列出所学课程平均分成绩高于80分而且没有不及格课程的学生姓名及其平均成绩。
3.列出即学过‘1’号课程又学过2号课程的所有学生的学号。请各位帮忙,在线等
from sc
inner join s on sc.sno=s.sno
where sc.cteacher='李明'
s.sname
,avg(sc.scgrade)
from sc
inner join s on sc.sno=s.sno
where sc.scgrade>=60
group by s.sno
,s.sname
having avg(sc.scgrade)>80
select s.sno
from c
inner join sc on c.cno=sc.cno
inner join s on s.sno=sc.sno
where c.cno=1 or c.cno=2
select sname from sc inner join s on s.sno=sc.sno
inner join c on c.sno=sc.cno where c.cteacher='李明’
inner join sc on sc.sno = s.sno
inner join c on sc.cno = c.cno
where c.cteacher = '李明'
一下,等有空的时候再来看看
( select sno from sc where cno in( select cno from C where cteacher='李明') )3. select sno from sc where cno='1' and cno='2'
s elect distinct s.*
f rom S join SC on s.sno = sc.sno join c on sc.cno = c.cno
w here c.cteacher = '李明'
2、
s elect distinct s.* avg_score
f rom S join SC on s.sno = sc.sno join (s elect sno,avg(scgrade) a avg_score f rom sc group by sno,cno) a on a.sno = s.sno
w here sc.scgrade >= 60 and a.avg_score > 803、
s elect s.sno
f rom S join SC sc1 on s.sno = sc1.sno join SC sc2 on s.sno = sc2.sno
w here sc1.cno = '1' and sc2.cno = '2'
1.找出选修过‘李明’老师讲授的所有课程的学生的姓名。
select sname
from s,c,sc
where s.sno=sc.sno and sc.cno=c,cno and cteacher='李明'
2.列出所学课程平均分成绩高于80分而且没有不及格课程的学生姓名及其平均成绩。
select sname,avg(scgrade)
from s,sc
where s.sno=sc.sno
group by s.sno
having min(scgrade)>=60 and avg(scgrade)>80
3.列出即学过‘1’号课程又学过2号课程的所有学生的学号。
注意: 下面语句的前提:(sno,cno) 是sc表的关键字
select sno from sc
where cno in ('1','2')
group by sno
having count(*)=2
from sc
inner join s on sc.sno=s.sno
inner join c on sc.sno=c.sno and c.cteacher='李明'