乐科测试 www.leketest.com 版权所有, 联系作者:qq2293913554
--drop table xs;
10
有
3
个表
S
,
C
,
SC
:
S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)
SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)
问题:
1 找出没选过“黎明”老师的所有学生姓名。(分别使用嵌套和非嵌套 2 种方式)
select sname from s where sno not in
(select sno from sc where cno in (select cno from c where cteacher='黎明'))
或 select S.SNAME from S,C,SC
where C.CNO=SC.CNO and SC.SNO=S.SNO and C.CTEACHER !='黎明'
2 列出 2 门以上(含 2 门)不及格学生姓名及平均成绩。
select S.SNAME,avg(SC.SCGRADE) from S,SC
where S.SNO=SC.SNO and SC.SCGRADE<60 group by S.SNO,S.SNAME having count(1)>=2;
或者
select s.sname, t.pj from s inner join
(select sno, avg(scgrade) pj from sc
where scgrade < 60 group by sno having count(*) >= 2) t on s.sno = t.sno;
3 即学过 1 号课程又学过 2 号课所有学生的姓名。
select S.SNAME from S
where S.SNO in
(select a.SNO from SC a,SC b where a.SNO=b.SNO and a.CNO=1 and b.CNO=2);
或 select sname from s where
sno in (select sno from sc where cno = 1)
and
sno in (select sno from sc where cno = 2)