三个表
S<sno sanme
学号 姓名
c<cno cname cteacher
课号 课名称 老师
SC<sno cno scgrade
学号 课号 成绩
问题:
1,选出没选eric老师的学生姓名
2,学过1号课又有2号课的学生姓名。
3,2门以上不及格的学生姓名和成绩
select S.same from S Where S.sno in ( select SC.sno from C,SC where C.cteacher<>'eric' AND SC.cno = C.con )select S.same from S Where S.sno in ( select SC.sno from SC where SC.cno ='1号' AND SC.cno ='2号' group sno )select S.same from S Where S.sno in ( select SC.sno from SC Where SC.scgrade<60 group SC.cno having count(SC.cno)>=2)
S<sno sanme
学号 姓名
c<cno cname cteacher
课号 课名称 老师
SC<sno cno scgrade
学号 课号 成绩
问题:
1,选出没选eric老师的学生姓名
2,学过1号课又有2号课的学生姓名。
3,2门以上不及格的学生姓名和成绩
select S.same from S Where S.sno in ( select SC.sno from C,SC where C.cteacher<>'eric' AND SC.cno = C.con )select S.same from S Where S.sno in ( select SC.sno from SC where SC.cno ='1号' AND SC.cno ='2号' group sno )select S.same from S Where S.sno in ( select SC.sno from SC Where SC.scgrade<60 group SC.cno having count(SC.cno)>=2)
Where S.sno not in ( select SC.sno from C,SC where C.cteacher='eric' AND SC.cno = C.con )
2. select s.sname from s
where s.sno in
(select distinct a.sno from sc a
where a.cno = '1号'
and a.sno in (select b.sno from sc b where b.cno = '2号')
)
3. select s.sname, sc.scgrade from s, sc
where s.sno = sc.sno
ans s.sno in (
select sno from sc where scgrade < 60
group by sno
having count(1) >= 2
)好像楼主没有做对
要求选出没选eric老师的学生姓名, 楼主的写法不正确,举个例子,学生A选择了bree老师的课程和eric老师的课程,按照楼主的sql语句,结果中将会出现学生A的姓名,但是要求中是不能出现的2. select S.same from S Where S.sno in ( select SC.sno from SC Where SC.scgrade<60 group SC.cno having count(SC.cno)>=2)
要求2门以上不及格的学生姓名和成绩,1)没有选择出成绩;2)group by SC.cno而不是group SC.cno
第二题肯定是错了你的看不太明白阿
那我第三题改成这样就对了把?select S.same,S.scgrade
from S
Where S.sno in
(select SC.sno from SC Where SC.scgrade<60 group by SC.cno having count(SC.cno)>=2)
where s.sno in
(select distinct a.sno from sc a -- 去掉重复的学号
where a.cno = '1号' -- 满足选择1号课程
and a.sno in (select b.sno from sc b where b.cno = '2号') -- 且选择2号课程
)
要求学过1号课又有2号课的学生姓名,SC表中记录学生的学号,课程和成绩,也就是说一个学生将会出现多条记录,所以选择的时候使用distinct去掉重复的学号;同时需要满足1号课程和2号课程的学生,首先必须选择1号课程,其次学号必须在选择2号课程的学生中2. S.scgrade S是学生表,表中没有成绩字段
那题目要是2门以上不及格的学生姓名
select S.same from S Where S.sno in ( select SC.sno from SC Where SC.scgrade<60 group SC.cno having count(SC.cno)>=2)
我这样写是不是就对了另外3,2门以上不及格的学生姓名和成绩
这样写总对了把?
select S.same,SC.scgrade
from S,SC
Where S.sno=Sc.sno
AND S.sno in (select SC.sno from SC Where SC.scgrade<60 group SC.cno having count(SC.cno)>=2)
同时感谢a