数据库实验 –嵌套查询–查询选修了“c1”课程但没有选修“c2”课程的学生学号–三种方法— in 的方法—exists方法—–集合操作 ： INTERSECT

select s_num as 学号
from  selectCourse A
where A.c_num = 'C1' and  s_num  not in (select  s_num from selectCourse
                                      where  c_num ='C2' )

select s_num as 学号
from  selectCourse A
where A.c_num = 'C1' and  not exists (select * from selectCourse B
                                      where A.s_num=B.s_num  and  c_num ='C2' )

select   s_num as 学号 from selectCourse
								       where  s_num  not  in
								        (
										select  s_num  from  selectCourse
										where  c_num ='C2'   
										) 
NTERSECT  
select   s_num as 学号 from selectCourse
								      where  s_num   in
										(
										 (select  s_num  from  selectCourse
										where  c_num ='C1')  
										) 	

--创建选课表
create table selectCourse
(
 s_num  int,
 c_num varchar(20),
 score numeric(3,1)   ,
 primary key (s_num,c_num),
 foreign key ( s_num) references student(s_num),
 foreign key (c_num) references course(c_num),
)
drop table selectCourse
--插入数据

insert into  selectCourse(s_num, c_num, score) 
                                    values(1815001,'C1',93),
									         (1815002,'C1',90),
											 (1815003,'C1',99),
											 (1815004,'C1',96),								
			                                 (1815001,'C2',80),
											 (1815001,'C3',90),
											 (1815001,'C4',78),
											 (1815001,'C5',88),
											 (1815001,'C6',98),
											 (1815005,'C3',88),							   
											 (1815004,'C3',98)
insert into  selectCourse(s_num, c_num ) 
                               			  values(1815005,'C4')