SQL Server 2015 部分查询练习.docVIP

查询至少选修了两门课的学生学号 select distinct a.sno from sc as a,sc as b where a.sno=b.sno and a.cno!=b.cno use mydata select Sno from sc group by Sno having count(cno)=2 既选修了C01又选修C02课程的学生学号 select a.sno from sc a,sc b where a.sno=b.sno and a.cno=c01 and b.cno=c02 select sno from student where sno in (select sc.sno from sc where sc. cno=’c01’ and sc.sno=s.sno) and sc.cno =’c02’ select SNO from SC as SC1 where SC1.CNO = c01 and exists (select SNO from SC as SC2 where SC2. CNO = c02 and SC2.SNO = SC1.SNO) select sno from student where sno in (selectsnofrom sc where cno=1)and sno in (select sno from sc where cno=2) 列出选修了c01号课程的学生的学号及姓名。 select Sname from student where exists ( select *from sc where sno=student.sno and cno =C01) select s.sname,s.sno from student,sc where s.sno=sc.sno and sc.cno=’c01’ 查询没有选修c01号课程的学生姓名 select Sname from student where not exists ( select * from sc where sno=student.sno and cno =C01) select s.sname,s.sno from student,sc where s.sno=sc.sno and sc.cno=’c01’ 列出选修了001号和002号课程的学生的学号 select SNO from SC as SC1 where SC1.CNO = c01 and exists (select SNO from SC as SC2 where SC2. CNO = c02 and SC2.SNO = SC1.SNO) 思考：查询选修了全部课程的学生姓名 select 学号，姓名，年龄 from 学生表,学生选课表 where 学生表.学号=学生选课表.学号 group by 学生选课表.学号 having count(学生选课表.课程号)=count(select 课程号 from 课程表)11. 查询选修了全部课程的学生姓名:SQL Server中: select sname from student where not exists(select * from course where not exists( select * from sc where sno=student.sno and cno=course.cno))查找学号为 00003 没有选修的科目?3. 查询选修了课程’1’和课程’2’的学生的学号:select sno from student where sno in (selectsnofrom sc where cno=1)and sno in (select sno from sc where cno=2)比较: 查询选修了课程’1’或课程’2’的学生的sno:select sno from sc where cno=1 or cno=2比较连接查询: select A.sno from sc A, sc B where A.sno=