create table #student(Sno char(4) not null ,Sname varchar(8) not null ) --学生表
create table #class (Cno char(2) not null ,Cname varchar(20) not null) --课程表
create table #stu_class(Sno char(4) ,Cno char(2)) --学生选课表
---------初始数据
insert #student values ('001' ,'calla')
insert #student values ('002' ,'daisy')
insert #student values ('003' ,'susie')
insert #student values ('004' ,'julia')
insert #student values ('005' ,'dylan')
insert #student values ('006' ,'simba')
insert #student values ('007' ,'jimmy')
-------------
insert #class values ('01' ,'English')
insert #class values ('02' ,'Chinese')
insert #class values ('03' ,'Mathematics')
insert #class values ('04' ,'History')
-----------------------
insert #stu_class values('001','01')
insert #stu_class values('001','02')
insert #stu_class values('001','03')
insert #stu_class values('001','04')
insert #stu_class values('002','02')
insert #stu_class values('002','03')
insert #stu_class values('002','04')
insert #stu_class values('003','01')
insert #stu_class values('003','02')
insert #stu_class values('004','03')
insert #stu_class values('004','04')
insert #stu_class values('005','01')
insert #stu_class values('005','03')
insert #stu_class values('006','01')
insert #stu_class values('006','02')
insert #stu_class values('007','03')
insert #stu_class values('007','04')
select b.sname,c.cname
From #stu_class a ,#student b ,#class c
where a.sno = b.sno
and a.cno = c.cno
-----请查询出所选课目包含了所用Jimmy所选的课程的学生
--答案1
select distinct b.sname
from #stu_class a ,#student b
where a.cno in( select cno from #stu_class where sno = '007' )
and a.sno = b.sno
group by a.sno ,b.sname
having count(*) >= (select count(*)from #stu_class where sno = '007')
--答案2