s(s#,sname,age,sex)
c(c#,cname,teacher)
sc(s#,c#,grade)
如:选择选修了‘程军’所教的全部课程的学生可以如下:
select sname from s,sc
where s.s#=sc.s#
and c# in (select c# from c
where teacher='程军')
group by sname,s.s#
having count(*)=(select count(*) from c where teacher='程军')
或
select sname from s where not exists
(select * from c where teacher='程军' and not exists
(select * from sc where sc.s#=s.s# and sc.c#=c.c#))
可是选择选修了学号为2的同学所选修的全部课程的学生和着到题很类似,可是我却求部出来??
还有:选择选修课程与学号为2的同学所选修的课程都相同(不多不少)的学生
选择选修课程与‘程军’所教的全部课程都相同(不多不少)可以如下
最好能有exists 和not exists 来描述。
c(c#,cname,teacher)
sc(s#,c#,grade)
如:选择选修了‘程军’所教的全部课程的学生可以如下:
select sname from s,sc
where s.s#=sc.s#
and c# in (select c# from c
where teacher='程军')
group by sname,s.s#
having count(*)=(select count(*) from c where teacher='程军')
或
select sname from s where not exists
(select * from c where teacher='程军' and not exists
(select * from sc where sc.s#=s.s# and sc.c#=c.c#))
可是选择选修了学号为2的同学所选修的全部课程的学生和着到题很类似,可是我却求部出来??
还有:选择选修课程与学号为2的同学所选修的课程都相同(不多不少)的学生
选择选修课程与‘程军’所教的全部课程都相同(不多不少)可以如下
最好能有exists 和not exists 来描述。
select cname from sc a
join s b on a.s#=b.s#
join c c on a.c#=b.c#
where s#=2 --如果是姓名,就用 where sname='姓名'
select sname from sc a
join s b on a.s#=b.s#
join c c on a.c#=b.c#
group by sname
having count(cname)=(select count(*) from sc a
join s b on a.s#=b.s#
join c c on a.c#=b.c#
where s#=2 --如果是姓名,就用 where sname='姓名'
)--选择选修课程与‘程军’所教的全部课程都相同
select sname from sc a
join s b on a.s#=b.s#
join c c on a.c#=b.c#
group by sname
having count(cname)=(select count(*) from sc a
join s b on a.s#=b.s#
join c c on a.c#=b.c#
where teacher='程军'
)
zjcxc(邹建) 兄:能用exists 或not exists 来描述上述两题吗?(此法我也会)
join s b on a.s#=b.s#
join c c on a.c#=b.c#
where s#=2 --如果是姓名,就用 where sname='姓名'此法有错,选出的是2号所选修的全部课程与题意不符