关于exists的理解（二）

注：exists : 强调的是是否返回结果集，不要求知道返回什么。

现有学生表Student，成绩表SC，课程表Course。如下：

-- 1.查询所有选修了“K1”课程的学生名。

-- 普通查询in 

 SELECT SNAME FROM student where SNO in (
  select sno from sc where sc.CNO='K1'
);

-- 带EXISTS的SQL查询 

SELECT SNAME FROM student   where  EXISTS (
  select * from sc  where  sc.SNO=student.SNO and sc.CNO = 'K1' 
);

-- 2.查询没有选C1课程的学生的学号、姓名

 SELECT SNAME FROM student where SNO not in (
  select sno from sc where sc.CNO='K5'
);

SELECT SNAME FROM student   where NOT EXISTS (
  select * from sc  where  sc.SNO=student.SNO and sc.CNO = 'K5' 
);

-- 3.查询选修了所有课程的学生的姓名 

select Sname from student where Sno IN(
select Sno from SC
group by Sno
having count(*)=(select count(*) from course))

SELECT SNAME from student WHERE NOT EXISTS(
 	  SELECT * FROM course where NOT EXISTS(
		 SELECT * from sc WHERE  sc.SNO=student.SNO and sc.CNO=course.CNO 
	)
);

-- 4.查询至少选修了S2所选的全部课程的学生名

select sname FROM student where NOT EXISTS(
  select * from sc  x where x.SNO='2'  AND NOT EXISTS(
    SELECT *  FROM sc  y  WHERE  student.SNO = y.SNO and x.CNO=y.cno
 )
)

-- 5.查询至少选修了K1和K5的课程的学生姓名

 select sname FROM student,sc  x where x.cno='K1' AND student.SNO=x.sno AND  EXISTS(
    SELECT *  FROM sc  y  WHERE  x.SNO = y.SNO and y.CNO='K8'
);

select x.SNO,sname
from SC x, SC y,student
where x.Sno=y.Sno and x.cno='K1' and y.cno='K8' and student.SNO=x.SNO;

-- 6.求平均成绩超过80分的学号及平均成绩

SELECT sno, AVG(score) 
 FROM sc 
 GROUP BY SNO
 HAVING AVG(score)>=80;

参考：

SQL中EXISTS的使用

检索所有课程都选修的的学生的学号与姓名