table s(sno,sname)学号 学生姓名
table c(cno,ckecheng,cteacher)课程号,课程名,任课老师
table sc(sno,cno.scgrade)学号,课程号,成绩
问题
1,查询所有任课老师不是李明的学生姓名
2,查询所有有两科以上(包括两科)不及格的学生姓名和平均成绩.
table c(cno,ckecheng,cteacher)课程号,课程名,任课老师
table sc(sno,cno.scgrade)学号,课程号,成绩
问题
1,查询所有任课老师不是李明的学生姓名
2,查询所有有两科以上(包括两科)不及格的学生姓名和平均成绩.
select distinct sname from s where sno in(
select sno from sc where cno not in(select cno from c where cteacher='李明'));select s.sname,b.avegrade from s inner join (
select count(sno),sno from sc group by sno where scgrade<60 having count(sno)>=2)a on s.sno=a.sno inner join (select avg(scgrade) avegrade,sno from sc group by sno)b on s.sno=b.sno;
where not exists(select 1 from sc join c
on sc.cno=c.cno
where sc.sno=sno and c.cteacher='李明')2.select a.sname,avg(scgrade) as avggrade
from s a join sc n
on a.sno=b.sno
where (select count(*) from sc where sno=a.sno where scgrade<60)>2
group by s.sname