**********************************************************************************************
有三个表:S、C、SC
S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)
SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)
问题1:找出没有选择黎明老师的所有学生姓名?
问题2:列出2门(含有2门)以上不及格学生姓名及平均成绩?
问题3:即学过1号课程又学过2号课程的所有学生?
**********************************************************************************************
解答问题1:找出没有选择黎明老师的所有学生姓名?
方法一(正确):
select s.sno,s.sname from s
where s.sno not in (select sno from sc where cno in (select cno from c where cteacher = 'LiMing'));
解答问题2:列出2门(含有2门)以上不及格学生姓名及平均成绩?
select s.sno,sname,avg(scgrade)
from s join sc on (s.sno=sc.sno)
where sc.sno in (select sno from sc where scgrade < 60 group by sno having count(*) >=2)
group by s.sno,sname;
解答问题3:即学过1号课程又学过2号课程的所有学生?
方法一:
select sno,sname from s
where sno in
(
select sno from sc where cno=1
intersect
select sno from sc where cno=2
);
方法二:
select sno,sname from s
where sno in (select sno from sc where cno=1)
and sno in (select sno from sc where cno=2);
阅读(224) | 评论(0) | 转发(0) |