2021-04-01

SQL Sever 2008语句查询实例2

1. 查询被所有同学选修的课程的课程名：
   select cno from C where not exists(
   selectfrom s where not exists(
   selectfrom SC
   where SNO=S.SNO and CNO=C.CNO))
   
2. 查询所有学生当前所在系（包括转系的学生），显示学号，姓名和所在系的名称
   自然连接：
   select s.sno ,s.sname ,dname from s
   JOIN d on substring(sno,5,2)=d.dno
   where sno not in (select SpecialStudent.sno from SpecialStudent where s.sno=SpecialStudent.sno)
   union
   select s.sno,s.sname,dname from s
   join SpecialStudent on s.sno=SpecialStudent.sno
   join d on SpecialStudent.nowInDept=d.Dno;
   
   外连接：
   SELECT s.SNO, s.SNAME, d.DNAME
   FROM S AS s LEFT JOIN D AS d ON SUBSTRING(s.SNO, 5, 2) = d.DNO
   WHERE s.SNO NOT IN (
   SELECT SNO FROM SpecialStudent
   )
   UNION
   SELECT s.SNO, s.SNAME, d.DNAME
   FROM SpecialStudent LEFT JOIN S AS s ON SpecialStudent.SNO = s.SNO
   LEFT JOIN D ON SpecialStudent.NOWINDEPT = D.DNO
   
   内连接：
   SELECT s.SNO, s.SNAME, d.DNAME
   FROM S AS s INNER JOIN D AS d ON SUBSTRING(s.SNO, 5, 2) = d.DNO
   WHERE s.SNO NOT IN (
   SELECT SNO FROM SpecialStudent
   )
   UNION
   SELECT s.SNO, s.SNAME, d.DNAME
   FROM SpecialStudent INNER JOIN S AS s ON SpecialStudent.SNO = s.SNO
   INNER JOIN D ON SpecialStudent.NOWINDEPT = D.DNO